How to Remove Formulas in Excel – 8 Easy Methods

A sample dataset is shown below.

Download Practice Workbook

Download and practise.
Removing Formulas.xlsm
[/wpsm_box]

How to Remove Formulas in Excel – 8 Easy Methods

This dataset includes Product ID, Selling Price, Cost, and their corresponding Revenue in columns B, C, D, and E.

Whenever you select a cell in the Revenue column, you can see a subtraction formula in the Formula Bar.

Method 1 – Using the Home Tab on Ribbon to Remove Formulas

Steps: 

• Select and copy the cells containing the formulas you want to delete.

• Go to Home > Paste > Paste Values.

• The formula will be removed and values will remain.

Method 2 – Utilizing the Paste Special Option to Remove Formulas and Keep Values

Steps:

• Select the cells and copy them like in Method 1.
• Right-click the selected cells and select Paste Special.

• In the Paste Special window, select Values. Click OK.

The formula will be removed.

Method 3 – Using Keyboard Shortcuts to Delete Formulas in Excel

Steps:

• Select and copy the cells using Ctrl+C.
• Use:

ALT+E+S+V+ENTER

or

CTRL+ALT+V, V, ENTER

• You will see the values only.

Method 4 – Removing Formulas with the Right Key of the Mouse

Steps:

• Select the E5:E14 range that contains the formula.

• A four-headed arrow cursor will be displayed.

• Hold the right key of the mouse and drag the selection to the right. Then, move it back to the left. Select Copy Here as Values Only.

The formula was removed.

Method 5 – Incorporating the Quick Access Toolbar to Remove Formulas in Excel

 Steps:

• Click Customize Quick Access Toolbar.
• Select More Commands.

• Choose Paste Special and click Add
• Click OK.

• Paste Special is added to the Toolbar.

• Proceed as in Method 2.

Method 6 – Using the Go To Dialog Box to Find Cells with Formulas 

 Steps:

• Press CTRL+G in the active sheet. In Go To, select Special.

• In Go To Special, choose Formulas and click OK.

• Cells containing formulas will be highlighted.

• Use a formula-removing method.

Method 7 – Applying a VBA Code to remove Formulas

Steps:

• Go to the Developer tab and click Visual Basic.

• Click Insert >> Module.

• Enter the following code.

Sub Rmv_Formulas()
Dim rng_1 As Range
Dim cell_1 As Range
Set rng_1 = Selection
For Each cell_1 In rng_1
cell_1.Value = cell_1.Value
Next cell_1
End Sub

• Save the file as a Macro-Enabled workbook.
• In the VBA worksheet, select the range E5:E14 which contains formulas.
• Go to the Developer tab and click Macros.

• In the Macro dialog box, select the macro and click Run.

Formulas were removed from the selected range.

Method 8 – Delete Formulas from Multiple Sheets in Excel

 Steps:

• Press SHIFT to group sheets. Sheets Multiple_1, Multiple_2, and Multiple_3 were grouped.

• Use any of the previous methods to remove formulas.

• After removing the formulas, ungroup sheets by clicking a sheet that is not in the group.

Practice Section

Practise in the sheet below.

Remove Formulas in Excel: Knowledge Hub

• Remove Automatic Formula in Excel
• Remove Formulas from Entire Workbook
• Remove Formula in Excel and Keep Values
• Remove Formulas in Excel Keeping Values and Formatting VBA
• Remove Formula when Filtered
• Remove Hidden Formulas in Excel

<< Go Back to Excel Formulas | Learn Excel