Convert Formula to Value in Multiple Cells in Excel (5 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Convert Formula to Value in Multiple Cells with Keyboard Shortcut

  • 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.

Paste Special to Convert Formula to Value in Multiple Cells in Excel

  • 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.

Drag the Mouse to Convert Formula to Value in Multiple Cells in Excel

  • 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.

Convert Formula to Value in Multiple Cells with Power Query

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.

Convert Formula to Value in Multiple Cells with Excel VBA

  • 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.


Related Articles


<< Go Back to Convert Formula to Value in Excel | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo