Consolidate Excel Formulas into Values

My client is having trouble with sheets containing formulae, so I am converting all the calculated values into hardcoded data. Apparently there is no native function to do this job. According to Microsoft's support, the "solution" (oh my God) is to select the cells you want, copy, select the first cell, execute "paste special", click values only:

https://support.microsoft.com/en-us/office/replace-a-formula-with-its-result-38001951-c0e8-4fbd-9048-37ef81e2891e

Now, this sounds like a poor mending, a patch, not a proper function. I would like to know if it is possible to achieve the same result with a quick AppleScrip action (even using UI) or maybe macros. I can deal better with Automator so I would rather not dive into VBA world, but I can change my mind if it is easier.

What I need is: select the cells, press a shortcut, replace the formula with its calculated result.

Solution 1:

Here is a simpler, faster method to replace formulae with values using the paste special command. It is set to work with the used range but the focus can be narrowed easily enough (see comments).

tell application "Microsoft Excel"
    
    set ur to used range of active sheet -- copy from where
    -- to reduce affected area…
    -- set ur to intersect range1 ur range2 column "A:B"
    
    copy range ur -- to clipboard
    
    activate object range "A1" of active sheet -- paste where
    paste special range "A1" what paste values
    
end tell

I'm leaving my original answer here as it is valid and can be useful in other circumstances, such as if you wished to further process each cell.

This will grab the value of every cell of a sheet's used range, and then cycle through each cell of that range and replace its contents with its value. I've added an optional line to limit the affected range.

tell application "Microsoft Excel" tell workbook 1 tell active sheet

        set ur to used range -- where      
        set ur to intersect range1 ur range2 column "A:B" -- limit where
        
        set cv to value of cells of ur -- what          
        repeat with x from 1 to count of cells of ur
            set value of cell x of ur to item x of cv
        end repeat
        
    end tell
end tell

end tell

Finally, here is a mouse-driven solution that I found on macexcel.com at Change formulas to values under Manual way 2.

Select the range you wish to affect and hover the cursor over the right or bottom edge of the selected range. The cursor will change to a hand. Right-click and begin to drag the selection, then the cursor will change to a fist. Make sure you haven't actually moved the selection and release the button. An alternate contextual menu results. Click Copy Here as Values Only and the selection's values will be pasted over its current contents. And of course, you can create a keyboard shortcut for Paste Values which allows for a quick and easy solution.