0

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.

11
  • 1
    It seems quite reasonable when you have the choice to paste.special formulae or values or formatting etc as you wish - I’m happy with that flexibility...
    – Solar Mike
    Commented Jul 14, 2021 at 19:21
  • 1
    From keyboard its ⌘ + C (to copy) and then ⌃ + ⌘ + V (to open paste special window) + V (to select Value) + ⏎. So it is 7 keystrokes. If you want replace only in one cell then F2 (go into cell editing mode) and then F9 (replace formula with value). Commented Jul 14, 2021 at 20:14
  • @SolarMike its not flexibility when you don't have a proper function to do the work. "Consolidate data" is flexibility. Commented Jul 14, 2021 at 20:32
  • @AivarPaalberg thanks for the input. It seems easy enough, but every extra step is a problem when you have several files per day, with formulas scattered through different areas of the sheet. I will try to write a script to do all the strokes in one pass. Commented Jul 14, 2021 at 20:37
  • When I install excel, one of the things I do the first day is add a handful of items to the toolbars, notably 'paste values' (and 'select current region'). You can also assign a keyboard shortcut to paste values which would reduce your keystroke count.
    – Mockman
    Commented Jul 16, 2021 at 1:31

2 Answers 2

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.

3
  • Interesting. It is elegant and it does work! However it took a long time to process just a few lines. The copy paste approach is almost instant. This could be one of the few cases where using some UI shortcuts can be more effective. Commented Jul 15, 2021 at 19:07
  • Roughly speaking, how many rows/columns are in your used range? Also, in your question, you mention selecting cells… if you did that, approximately how many would there be?
    – Mockman
    Commented Jul 16, 2021 at 1:28
  • thank you for all your trouble, I wrote a simple script that did the trick. I hope your solution will help other people. If you still wish to test your code I have a sample file here with all the formulas grabbing data from the second sheet: bit.ly/3kzAi6l Commented Jul 19, 2021 at 23:17
0

It was way simpler than I thought. A simple GUI script solved the problem. However I still think Microsoft should implement a "consolidate all formulas" function. I wrote some feedback for them. Anyway, here is my simple, one shortcut solution:

on run {}
   tell application "Microsoft Excel" to activate
   tell application "System Events"
      key code 0 using command down --select all
      delay 0.5
      key code 8 using command down --copy
      delay 0.5
      key code 9 using {control down, command down} --paste special
      delay 0.5
      key code 9 --V (select values only)
      delay 0.5
      key code 36 --return
   end tell
end run

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .