How to Convert Formula to Value Automatically in Excel (6 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Excel is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes, we need to convert formula to value in Excel. In this article, I am going to show you 6 effective methods to convert formula to value automatically in Excel.

This is the dataset that I am going to use. I have some students along with their marks in Physics, Math, and Total marks (calculated using the SUM function). I will convert the Total marks to values.

excel convert formula to value automatically


1. Applying Paste Special Feature to Convert Formula to Value Automatically in Excel

Now I will show the use of the Paste Special feature to convert formulas to values.

Steps:

  • Select E5:E11. Press CTRL+C to copy the range.

You can also copy them using the context menu.

excel convert formula to value automatically

  • After selecting, bring the context menu by right-clicking your mouse and then select Paste Special.

  • Paste Special window will appear. Check Values >> click OK.

excel convert formula to value automatically

Excel will convert the formulas to values.


2. Using Paste as Value Option to Convert Formula to Value

You can also use Paste Values from the context menu directly to convert formulas to values.

Steps:

  • Select E5:E11 and copy them.

excel convert formula to value automatically

  • Then select values from the Paste Options of the context menu.

  • Excel will do the rest.

excel convert formula to value automatically


3. Converting Formula to Value Automatically with Keyboard Shortcuts

You can use multiple keyboard shortcuts to convert formula to value automatically in Excel. In this section, I will describe them one by one.


3.1 Using ALT+E+S+V Keys Simultaneously

This method will basically show the keyboard shortcuts to convert formulas using the Paste Special feature.

Steps:

  • Press CTRL+C to copy the range E5:E11.

  • Then press ALT+E+S+V one by one. Do not press them together. You will see that the Paste Special window has popped up. Click OK.

excel convert formula to value automatically

The formulas will turn into values.


3.2 Pressing F9 Key

Let’s look at another keyboard shortcut that will serve our purpose.

Steps:

  • Select E5. Go to the formula bar to edit the cell. Then select the formula.

excel convert formula to value automatically

  • Now press F9. Excel will show the values.

Note: Though this method is easy to execute, it takes a lot of time to convert the formulas one by one if the dataset is huge.

Read More: How to Return Value of Cell Not Formula in Excel


4. Holding and Hovering the Mouse to Convert Formula to Value

You can also use your mouse to convert formula to value automatically in Excel.

Steps:

  • Select the range C5:C11.

excel convert formula to value automatically

  • Then hover your mouse to bring the 4-arrow pointer (see image).

  • Then keep right-clicking the mouse and move the cursor to the destination where you want to paste the values. Select Copy Here as Values Only.

excel convert formula to value automatically

Excel will copy the values only.


5. Using Excel Power Query to Convert Formula to Value Automatically

Now I will show how to use Power Query to convert formulas to values.

Steps:

  • Select the entire dataset. Then go to the Data tab >> select From Table/Range.

excel convert formula to value automatically

  • Create Table box will pop up. Select the range for your table >> check My table has headers >> click OK.

  • Power Query window will appear. Click Close & Load.

excel convert formula to value automatically

  • Excel will return the numbers as values in a separate worksheet.


6. Applying VBA Code for Converting Formulas to Value Automatically

Now I will convert formulas to value automatically with VBA.

Steps:

  • Press ALT + F11 to open the VBA window.
  • Then go to Insert >> select Module.

excel convert formula to value automatically

  • A new module will pop up. Write down the following code.
Sub Convert_Formulas_to_Values()
Dim rn As Range
Dim Cell As Range
Set rn = Selection
For Each Cell In rn
If Cell.HasFormula Then
Cell.Formula = Cell.Value
End If
Next Cell
End Sub

  • Now press F5 to run the code. Excel will convert the formulas to values.

excel convert formula to value automatically

Read More: How to Stop Formula to Convert into Value Automatically in Excel


Things to Remember

  • You can also open a VBA window from the Developer tab.

  • Press ALT, then E, then S, and finally V. Do not press them together.
  • You can press SHIFT+F10 to bring the context menu.

Download Practice Workbook

Download this workbook to practice while going through this article.


Conclusion

In this article, I have demonstrated 6 effective methods to convert formulas to values in Excel. I hope it helps everyone.


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.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo