How to Remove Formulas in Excel – 8 Easy Methods

A sample dataset is shown below.

Overview GIF

Download Practice Workbook

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


How to Remove Formulas in Excel – 8 Easy Methods

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

Dataset of revenue list

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

each cell in column E got formula


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

Steps: 

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

selecting Column which contains formula and copying them

  • Go to Home > Paste > Paste Values.

Pasting as Values

The formula will be removed and values will remain.

formulas removed, only value remains


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.

selecting paste special option

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

working on paste special dialog box

The formula will be removed.

Formula is removed from cell E5


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.

selecting column with formulas

A four-headed arrow cursor will be displayed.

four head arrow

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

selecting Copy Here as Values Only on context menu

The formula was removed.

Final output of removing formulas in Excel


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

 Steps:

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

customize quick access toolbar icon

  • Choose Paste Special and click Add
  • Click OK.

adding command in the quick access toolbar

Paste Special is added to the Toolbar.

new command added in the QAT


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.

using keyboard shortcut to bring Go To Dialog box

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

working on Go To Special dialog box

Cells containing formulas will be highlighted.

cells containing formulas are higlighted

  • Use a formula-removing method.

Method 7 – Applying a VBA Code to remove Formulas

Steps:

  • Go to the Developer tab and click Visual Basic.

navigating to Developer tab

  • Click Insert >> Module.

inserting a new 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

VBA Code to remove formulas in Excel

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

clicking on macros option

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

selecting macro on the Macro dialog box

Formulas were removed from the selected range.

formulas removed using VBA code


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.

selecting multiple sheets at a time

  • Use any of the previous methods to remove formulas.

formulas removed from multiple sheets

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

Practice Section

Practice in the sheet below.

practice section


Remove Formulas in Excel: Knowledge Hub


<< Go Back to Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo