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

Dataset Overview

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.

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.

• Right-click and choose Paste Special.

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

Excel will convert the formulas to values.

Method 2 – Paste Values Directly

Steps:

• Copy the range E5:E11.

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

• Excel will convert the formulas to values.

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

• Click OK in the Paste Special window.

3.2 Pressing F9 Key

Steps:

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

• Press F9 to display the values.

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

Method 4 – Mouse Hover and Copy

Steps:

• Select the range C5:C11.

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

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

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.

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

• Click OK and then Close & Load.

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

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

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

• Convert Formula to Value in Multiple Cells in Excel  
• How to Convert Formula Result to Text String in Excel
• Putting Result of a Formula in Another Cell in Excel 

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