Convert formulas to values

[Frank Horatio]

Using Excel for Mac 2011.

Trying to convert the result of the formulas into the actual values. So instead of cell b4 containing "=sum(b1:b3)" and displaying some value, the contents of the cell would just be the value.

This is something that ASAP utilities can do in Windows.

Anyone with any tips?

 

[VoG]

Try running this macro

Sub ToVals()
On Error Resume Next
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    .Value = .Value
End With
End Sub

 

[Jonmo1]

Welcome to the board...

Though I can't be 100% sure, since I don't use MAC...
I'd say I'm 99.99999999999% sure that you can do the same thing in MAC that we do in Windows.

Copy the cell(s) with the formula
Then Right Click(or whatever the Mac Equivelant is) - Paste Special - Values

Hope that helps.

 

[Frank Horatio]

VoG ... perfect. Thank you.

jonmo1 - thanks for answering. I was trying to do the conversion "in-place"

 

[VoG]

You are welcome.

jonmo1's suggestion will also work - you just paste special over the formulas.

 

[Frank Horatio]

VoG,

This converts ALL formulas in the worksheet to values. Is there a way to only convert selected cells/columns/rows?

Thanks.

 

[VoG]

Try this: select the range then run the macro

Sub ToVals()
On Error Resume Next
With Selection.SpecialCells(xlCellTypeFormulas)
    .Value = .Value
End With
End Sub

 

[Frank Horatio]

Brilliant!