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.

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.

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.

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

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.

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