Macro to Convert Formulas to Values

These VBA macros replace formulas with their results, in the selection, on the whole sheet or across every sheet, so you can send a file without formulas or freeze figures before the data changes.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Convert the selection

Sub FormulasToValuesSelection()
    Dim a As Range
    For Each a In Selection.Areas
        a.Value = a.Value
    Next a
End Sub

Looping through Areas makes it work even if you selected several separate ranges with Ctrl.

Convert the whole sheet

Sub FormulasToValuesSheet()
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
End Sub

Convert every sheet

Sub FormulasToValuesAllSheets()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
End Sub

Run this on a copy of the workbook, such as one made with Save a copy with a timestamp, not the original.

Without a macro

  1. Select the cells and press Ctrl + C.
  2. Press Ctrl + Alt + V, choose Values and click OK. Or press Ctrl + Shift + V in Microsoft 365.

Things to know

  • Formatting is kept; only the formulas are replaced.
  • This can’t be undone. Once saved, the formulas are gone for good.
  • Values from links to other workbooks are frozen too, which is useful before sharing a file.

Related macros