This article illustrates how to convert formula to value in multiple cells in excel. We usually use cell references in the formulas in Excel. Changing values in those reference cells will also change the output of the formulas. You need to convert the formulas to values to avoid that. Fortunately, there are several ways to do that in Excel. Have a quick look through the article to learn 5 such ways around doing that.
Convert Formula to Value in Multiple Cells in Excel: 5 Ways
Assume you have the following dataset. Here, the cells in the total column contain formulas. Follow the methods below to convert them to values.
1. Use Keyboard Shortcut to Convert Formula to Value in Multiple Cells
You can use some keyboard shortcuts to convert formulas in multiple cells to values in Excel.
📌 Steps:
- First, select and copy (CTRL+C) the range of cells containing formulas.
- Then press SHIFT+F10+V to convert them to values. You can also use ALT+H+V+V and ALT+E+S+V+Enter keyboard shortcuts to do that.
2. Utilize Excel Paste Special Feature
Alternatively, you can use the Paste Special feature in Excel to get the same result.
📌 Steps:
- First, select and copy (CTRL+C) the range. Then select Paste >> Values(V) from the Home tab.
- After that, you will get the following result.
Read More: Convert Formula to Value Without Paste Special in Excel
3. Drag & Drop Values with Mouse
Follow the steps below to get the same result using a mouse drag trick.
📌 Steps:
- First, select the range and put the cursor on the outline of the range. Then, you will see a four-sided arrow.
- Next, right-click and drag the selection outside the range and bring it back to the original position.
- Now release your mouse and you will see the option to Copy Here as Values Only. Click on it.
- After that, you will see the following result.
4. Use Power Query Tool
You can also get the same results using Power Query in Excel. Follow the steps below to do that.
📌 Steps:
- First, select the range that contains formulas. Then select Data >> From Table/Range as shown below.
- Now Excel will create a table using that range. Click OK to comply.
- After that, you will see the following table in the Power Query Editor. Now, select Close & Load >> Close & Load To… as shown below.
- Then choose the location and click OK.
- After that, the converted values will be inserted into a new table in the specified location. You just need to refresh the query when the source data changes.
Read More: How to Return Value of Cell Not Formula in Excel
5. Use Excel VBA to Convert Formula to Value in Multiple Cells
You cannot convert formulas from multiple ranges to values using the Paste Special feature. Therefore the shortcuts won’t work in that case too. Follow the steps below to use VBA to solve this problem.
📌 Steps:
- First, press ALT+F11 to open the VBA window. Then select Insert >> Module to create a new module. You can also use the ALT+I+M shortcut to do that.
- Now, copy the following code using the copy button.
Sub ConvertFormulasToValues()
Dim FRng, Cell As Range
On Error Resume Next
Set FRng = Application.InputBox( _
Prompt:="Please Select", Type:=8)
For Each Cell In FRng
If Cell.HasFormula Then
Cell.Formula = Cell.Value
End If
Next Cell
End Sub- Then, paste the copied code onto the module as shown below.
- Keep the cursor on the code and press F5 to run it. Then, you will be asked to select the range. Click OK after that.
- Finally, you will get the following result as in the earlier methods.
🔎 VBA Code Explanation:
Sub ConvertFormulasToValues()
We need to write the code inside this subject procedure.
Dim FRng, Cell As Range
Declaring necessary variables.
On Error Resume Next
Ignores errors.
Set FRng = Application.InputBox( _
Prompt:=”Please Select”, Type:=8)
Takes user input.
For Each Cell In FRng
If Cell.HasFormula Then
Loops through each cell in the selection to check if they contain formulas.
Cell.Formula = Cell.Value
Converts the cell formula to the cell value.
Read More: Excel VBA: Convert Formula to Value Automatically
Things to Remember
- The Paste Special feature does not work with multiple selections.
- Back up your data before using the VBA code.
- Save the document as a macro-enabled workbook to avoid losing the code.
Download Practice Workbook
You can download the practice workbook from the download button below.
Conclusion
Now you know 5 different ways how to convert formula to value in multiple cells in Excel. Which method do you prefer? Do you have any further queries or suggestions? Please let us know in the comment section below.
