Convert Formulas into Values Using Macros In Excel

Whenever we have a lot of cells with formulas, then wanted to change them to permanent values, we need to use the Paste Special functionality of Excel. What if I told you, you can do this in a single click for all formula cells in your selection? That’s right, Excel Macros can convert formulas into values in one step! Make sure your Excel has the Developer Tab enabled following this tutorial. I explain how you can do this below step by step!

Key Takeaways

• Automate Formula Conversion – Macros allow you to quickly convert formulas into static values, eliminating the need for manual copy-pasting and improving efficiency.
• Use VBA Code for Instant Conversion – A simple VBA script can replace all formulas in a selected range with their current values, preserving data without affecting formatting.
• Prevent Accidental Formula Changes – By converting formulas into values, you protect your calculations from unintended modifications, ensuring data integrity.
• Improve Performance in Large Workbooks – Removing formulas reduces recalculations, which can significantly improve the speed and responsiveness of complex Excel workbooks.
• Easily Assign Macros to Buttons – You can link the macro to a button or keyboard shortcut, allowing for one-click formula-to-value conversion whenever needed.

What does it do?

Converts formulas into values in your selection

Copy Source Code:

Sub ConvertFormulasIntoValues()

Dim rng As Range
Dim formulaCell As Range
Set rng = Selection

'Check each cell in the range if it has a formula
For Each formulaCell In rng
If formulaCell.HasFormula Then
formulaCell.Formula = formulaCell.Value
End If
Next formulaCell

End Sub

Final Result: 

 

How to Convert Formulas into Values Using Macros In Excel

Here is our list of formula cells:

To have a better look, you can see it uses the RANDBETWEEN formula. Let us change these to values only!

STEP 1: Go to Developer > Code > Visual Basic

 

STEP 2: Paste in your code and Select Save. Close the window afterwards.

 

STEP 3: Let us test it out!

Open the sheet containing the data. Go to Developer > Code > Macros

 

Make sure your formula cells and macro are both selected. Click Run.

 

With just one click, all of the formula cells are now converted to values! The values also changed because of the nature of the RANDBETWEEN formula, as it changes along with any change done to the workbook 🙂

 

 

Frequently Asked Questions

Why should I convert formulas into values in Excel?
Converting formulas into values helps prevent accidental changes, improves performance by reducing recalculations, and ensures that your data remains unchanged even if referenced cells are modified or deleted.

Can I assign the macro to a button for easy access?
Yes, you can create a button using the “Developer” tab, assign the macro to it, and click it whenever you need to convert formulas into values instantly. You can also assign it to a keyboard shortcut for quick execution.

Will converting formulas into values remove formatting?
No, using a macro to replace formulas with values only affects the cell contents. Formatting, including font styles, colors, and borders, remains intact. However, you should double-check any conditional formatting rules, as they might depend on formulas.

Can I undo the formula conversion after running the macro?
No, once formulas are replaced with values, the action cannot be undone unless you immediately press Ctrl + Z. To prevent data loss, consider saving a backup copy of your workbook before running the macro.