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

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

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

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

Advanced Excel Exercises with Solutions PDF