How to Remove Formulas in Excel (8 Easy Ways)

Usually, you can remove formulas from an Excel cell just by pressing the delete button. Unfortunately, this way of deletion removes the values from the cell. Again, you may want to send your spreadsheet to other people and due to confidentiality, you do not want to show the formula in the cells. So, in such situations, you will prefer to erase the formula only. Luckily, there are several ways to remove the formulas in Excel. In this article, we will discuss 8 easy and quick methods on how to remove formulas in Excel. So, go through the entire article to understand the topic properly.
The GIF attached below is an overview image of the article and depicts its main idea.

Download Practice Workbook

You can download and practice the method we have discussed in the article.

8 Suitable Approaches to Remove Formulas in Excel

For ease of understanding, we are going to use an Item-wise Revenue List of a certain small startup. This dataset includes the Product ID, Selling Price, Cost, and their corresponding Revenue in columns B, C, D, and E respectively.

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

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.
Now, we’ll use this dataset to remove formulas in Excel using several approaches. Let’s explore them one by one.
Not to mention, here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.

1. Using Home Tab Ribbon to Remove Formulas

You can use Excel Ribbon to remove formulas. For example, the Home tab can be used. Here are the steps we followed:

📌 Steps: 

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

• Go to Home > Paste > Paste Values.

• As a result, the formula will be erased, only values remain.

2. Utilizing Paste Special Option to Remove Formulas but Keep Values

Another way to remove formulas is to use right-click and Paste Special.
The following steps are involved in this method:

📌 Steps:

• At first, select the cells and copy them like in Method 1.
• Right-click on the selected cells, and select Paste Special from the context menu.

• Upon selection, the Paste Special window will show up. Then, select Values. After that, click OK.

As a result, the formula will be erased from the cells.

3. Applying Keyboard Shortcuts to Delete Formulas in Excel

If you prefer using keyboard shortcuts, you have two combinations of keys to remove formulas from cells. So, here are the examples.
The steps we have followed in this method are:

📌 Steps:

• Firstly, select and copy the cells using Ctrl+C.
• Then you can use the following combination.

ALT+E+S+V+ENTER

or

CTRL+ALT+V, V, ENTER

• Upon using the keys you will get the values without formulas in Excel.

4. Removing Formulas Implementing Right Key of Mouse

This is an interesting technique to remove formulas in Excel. Besides, it is very easy.
We have used the below steps for this method:

📌 Steps:

• Select the cells ( E5:E14 range) containing the formula.

• A four-headed arrow cursor will show up when you select the cells.

• Hold the right key of the mouse and drag the selection to the right side a little bit. Then, move back the selection to the left. Now, let go of the right key selection, and a context menu will show up. Finally, select Copy Here as Values Only.

Consequently, the formula got erased from the sheet.

5. Incorporating Quick Access Toolbar to Erase Formulas in Excel

There are more interesting ways to remove formulas in Excel, such as using the Quick Access Toolbar. Moreover, this method is very quick.
You can use the following steps to try this method:

📌 Steps:

• Firstly, click on the Customize Quick Access Toolbar icon on the top.
• Then, select the More Commands option.

• Add Paste Special from the list of commands, click on the Add button, and tap OK.

• Now, Paste Special is added to the Toolbar.

• Now, follow the steps of Method 2 to complete the remaining part.

6. Using Go To Dialog Box to Find Cells with Formulas 

There are times when you have several cells, but you do not know which cell contains formulas. In such a scenario, you have to first find out the cells with formulas, and then apply the method to remove formulas.
Here, we have used the following steps:

📌 Steps:

• Go to the active sheet and type CTRL+G. As a result, the Go To window will show up, select Special.

• Then, the Go To Special window will pop up, choose Formulas, and press OK.

• Upon clicking OK, cells containing formulas will be highlighted.

• In the end, you have to apply formula-removing methods to these highlighted cells one by one.

7. Applying VBA Code to Eradicate Formulas

Although using formulas is a quick way of editing data, it can be difficult to interpret. Furthermore, if you often need to remove formulas, then you may consider the VBA code below.

📌 Steps:

• Initially, navigate to the Developer tab and click on Visual Basic in the Code group.

• After that, click on Insert >> Module.

• At this moment, paste the following code into the module.

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

• Next, save the file as a Macro-Enabled workbook.
• Later, return to the VBA worksheet. Select the range (E5:E14) containing formulas. Again, go to the Developer tab and click on Macros.

Suddenly, the Macro dialog box will pop up.

• Select the only macro available here and click on the Run button.

Magically, the formulas got removed from the selected range of cells.

8. Delete Formulas from Multiple Sheets in Excel

Sometimes, you might need to erase formulas from multiple sheets in Excel. Luckily, there are ways to select sheets in groups and apply formula-removing methods. Besides, this method is really time-saving.
We have followed the below steps for this method:

📌 Steps:

• Firstly, select sheets in a group by pressing the SHIFT key. We have grouped sheets Multiple_1, Multiple_2, and Multiple_3.

• Now, go to any of the grouped sheets, and repeat the steps of any previous methods. Easily, formulas will be removed.

• After removing the formulas, ungroup the selected sheets by clicking any of the sheets that are not in the group.

Practice Section

For doing practice by yourself we have provided a Practice section like the one below in each sheet on the right side. Please do it by yourself.

Conclusion

We have discussed most of the easy and effective methods available to remove formulas in Excel. Please feel free to ask any questions you have regarding the above-mentioned methods. Also, you may follow our website, ExcelDemy, to explore more.

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