This post will guide you how to replace formulas with their calculated values in Excel. How do I replace all formulas with their values using VBA Macro in Excel.

Assuming that you have a list of data that contain formulas in range B1:B4, and you want to replace all formulas with their calculated values, how to do it. You can use the Paste Special command or an Excel VBA Macro to achieve the result.

1. Replace Formulas with Their Values

If you want to replace all formulas in the selected range of cells with their values in Excel, you can do the following steps:#1 select the range of cells that contain formulas. And press Ctrl + C keys on your keyboard.

#2 right click on the selected cells, and select the Paste Values menu under the Paste Options from the popup menu list.

#3 you would notice that all the formulas have been replaced with their calculated results in the selected range.

2. Replace Formulas with Their Values Using VBA

You can also use an Excel VBA Macro to achieve the same result of replacing all formulas with their calculated values. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code  into the code window. Then clicking “Save” button.

Sub ReplaceFormulasWithValues()
    Dim sourceRange As Range
    On Error Resume Next
    Set sourceRange = Application.InputBox("Select the source range with formulas:", Type:=8)
    On Error GoTo 0
    
    If Not (sourceRange Is Nothing) Then
        sourceRange.Value = sourceRange.Value
    End If
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 Please select one range that contain formuals. Click OK button.

#7 Let’s see the result:

3. Video: Replace Formulas with Their Values

This Excel Video tutorial, where we’ll explore two effective methods to transform your formulas into static values. In this session, we’ll delve into the user-friendly ‘Paste Options’ feature for quick replacements and harness the power of VBA macros for a more automated and tailored approach.

https://youtu.be/3jz64G6AKAw