# 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. • 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. ### 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. ### 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( _
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( _
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.

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

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

## What is ExcelDemy?

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

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  