How to Automatically Convert Formulas to Values in Excel (6 Methods)

Dataset Description

Let’s consider a dataset with student marks in Physics, Math, and Total (calculated using the SUM function). Our goal is to convert the Total marks from formulas to values.

excel convert formula to value automatically


Method 1 – Using Paste Special:

Steps:

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

Alternatively, copy the range using the Context Menu.

excel convert formula to value automatically

  • Right-click and choose Paste Special.

  • In the Paste Special window, select Values and click OK.

excel convert formula to value automatically

Excel will convert the formulas to values.


Method 2 – Paste Values Directly:

Steps:

  • Copy the range E5:E11.

excel convert formula to value automatically

  • Right-click and choose Paste Values from the Context Menu.

  • Excel will convert the formulas to values.

excel convert formula to value automatically


Method 3 – Keyboard Shortcuts:

3.1 Using ALT+E+S+V Keys

Steps:

  • Copy the range E5:E11 (CTRL+C).

  • Press ALT+E, then S, and finally V (one after the other).

excel convert formula to value automatically

  • Click OK in the Paste Special window.


3.2 Pressing F9 Key

Steps:

  • Select cell E5.
  • Edit the formula in the formula bar.

excel convert formula to value automatically

  • Press F9 to display the values.

Note: While this method is straightforward, it can be time-consuming for large datasets.

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


Method 4 – Mouse Hover and Copy:

Steps:

  • Select the range C5:C11.

excel convert formula to value automatically

  • Hover your mouse to reveal the 4-arrow pointer (see image).

  • Right-click and choose Copy Here as Values Only.

excel convert formula to value automatically

Excel will copy the values only.


Method 5 – Excel Power Query:

Steps:

  • Select the entire dataset.
  • Go to the Data tab and choose From Table/Range.

excel convert formula to value automatically

  • In the Create Table box, select the range and check My table has headers.

  • Click OK and then Close & Load.

excel convert formula to value automatically

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


Method 6 – VBA Code:

Steps:

  • Press ALT + F11 to open the VBA window.
  • Insert a new Module.

excel convert formula to value automatically

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

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

  • Use ALT, E, S, and V (individually) to access the Context Menu. Do not press them together.
  • Press SHIFT+F10 to bring up the Context Menu.

Download Practice Workbook

You can download the practice workbook from here:


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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