How to Replace Formulas with Their Values in Excel


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.

replace formulas with their values1

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

replace formulas with their values2

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

replace formulas with their values3

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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

#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

replace formulas with their values4

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

replace formulas with their values5

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

replace formulas with their values6

#7 Let’s see the result:

replace formulas with their values7

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.

Leave a Reply