How to Clear Formula in Excel (7+ Methods)

Microsoft Excel is one of the most popular tools used for rigorous calculations. Numerous built-in functions make it easy to perform complex calculations with a big data set. But, once the calculations are done, sometimes it is necessary to remove all the formulas before publishing them. To clear the formula in Excel might be required to hide confidential information inside the formula you don’t want to disclose or you need the calculated values not to link up to any other cells or any other reason. Here in this article, we’ll see how easily we can remove formulas from Excel.


Download the Practice Workbook


7+ Ways to Clear Formula in Excel 

Here, we’ll work on a dataset of a list of fruit items in a super shop in Australia. The dataset has a price column that shows the product of Unit Price and Quantity of the fruit items. We used the SUMPRODUCT function to get the product of these two columns. Now, we are going to apply 7+ methods to remove the formula while keeping the values unchanged.


1. Clear Formula from Excel Sheet Using Right Click

This method shows how to use the right button of the mouse to copy the cells with formulas and then paste only the values. As a result, we’ll get the cells with formulas removed while keeping the values as it was before.

  •  Select the cells having formulas that you want to remove (here F5:F9)

Clear Formula from Excel using Right Click-Cells with Formulas

  •  Then, click the Right button and Copy the cells

Clear Formula from Excel using Right Click-Copy Cells with Formulas

  •  Now, click the Right button again and choose Values from the Paste Options

Clear Formula from Excel Using Right Click-Paste as Values

  • Following the above steps will clear the formula from the cells keeping the values unchanged.


2. Remove Formula Using Home Tab in Excel

The Home tab in Excel provides Paste options where we can choose the ‘Values’ option to clear formulas. This Paste option gets activated after the cells are being copied. The required steps are:

  • Select the cells having formulas you need to clear (In this example: F5:F9)

Clear Formula from Excel Using Home Tab-Cells With Formulas

  • Right-click the mouse and Copy the cells

Clear Formula from Excel Using Home Tab-Copy Cellls with Formulas

  • After that, to Paste the values go to the Home tab and select Values from the Paste Values options

Clear Formula from Excel Using Home Tab-Paste as Values

  • Finally, see the final output below:


3. Clear Formula Using Excel Keyboard Shortcuts

After selecting the formula-contained cells, using two keyboard commands we’ll get the Paste as Values option. Let’s follow the commands:

  • Select the formula-contained cells. And then press Ctrl + C to copy the cells.

Clear Formula from Excel Using Keyboard Shortcut-Cells with Formulas

  • Then, press Alt + E + S + V + Enter to get Paste as Values 


4. Find and Clear Formula Using Keyboard Shortcuts

At first, we are going to find and get selected the cells with formulas using keyboard shortcuts and then replace the cells with values only. Let’s follow the steps:

  • By pressing Ctrl + G open up the Go To window and, now, select the Special button.

Find and Clear Formula from Excel Using Keyboard Shortcut-Cells with Formulas

  • Select Formulas from Go To Special window and click OK

Find and Clear Formula from Excel Using Keyboard Shortcut-Find cells with formulas

  • The above command will get the cells with formulas selected.

Find and Clear Formula from Excel Using Keyboard Shortcut- cells are selected with formulas

  • Now, press Alt + E + S + V + Enter to get Paste as Values. The final output is here:


5. Clear Formula Using Paste Special in Excel

Here, we’ll use Paste Special in MS Excel to remove formulas.

  • Firstly, we’ll Select and Copy the cells having formulas

Erase Formula Using Paste Special in Excel-Copy Cells with Formulas

  • Secondly, click the Right Button and select Paste Special option

Erase Formula Using Paste Special in Excel-Paste as Values

  • In the third step, select the Values option and then hit OK.

Erase Formula Using Paste Special in Excel-Paste as Values

  • The final result shows cells without formulas.


6. Erase Formula in Excel Using Clever Mouse Trick

In this method, we are going to use a special mouse trick to show the Paste as Values option to get our job done. Let’s follow the instructions:

  • Select the cells with formulas and then move the cursor to the right edge of the column

Clear Formula Using Clever Mouse Trick

  • Holding the Right Key of the mouse, drag the cursor to a little right and bring it back to its original position.

Clear Formula Using Clever Mouse Trick

  • From the given options choose “Copy Here as Values Only

Clear Formula Using Clever Mouse Trick-Paste as Values

  • As a result, the cells become free of formulas.


7. Get Rid of Formula from Multiple Excel Sheets (Just One Click)

Sometimes it may be necessary to erase formulas from multiple sheets to save time and work. Here we’ll demonstrate how to make it done using the group sheets method in Excel.

  • Here we see 3 ungrouped sheets (Sheet 1, Sheet 2, and Sheet 3) each having formula-contained cells. Below Sheet 1 is shown with cells with formulas selected.

. Get Rid of Formula From Multiple Excel Sheets(Just One Click)-Ungrouped Sheets

  • To make the sheets grouped, we hold the Shift key and select the first and last sheet (In this example: Sheet 1 and Sheet 3).

. Get Rid of Formula From Multiple Excel Sheets(Just One Click)-Grouped Sheets

  • After that, select and copy the formula contained cells of Sheet 1(one can choose any of the grouped sheets)

. Get Rid of Formula From Multiple Excel Sheets(Just One Click)-Copy Cells with Formulas

  • Now, click the right button again and choose Values from the Paste Options.

. Get Rid of Formula From Multiple Excel Sheets(Just One Click)-Paste as Values

  • All the grouped sheets will get the same operation we did for Sheet 1.


8. Clear Formula Using Quick Access Toolbar in Excel

Using quick access toolbar in Excel is another option to use the Paste as Values option to make cells formula-free while keeping values unchanged.

  • Click the Quick Access Toolbar button and choose the More Commands option to customize the toolbar.

Clear Formula Using Quick Access Toolbar

  • In the Excel Options window, select the Paste option having a right arrow at the end, then click Add button, and finally hit OK.

Clear Formula Using Quick Access Toolbar-Adding Paste option in Access Toolbar

  • The last operation has added a Paste icon in the toolbar which is inactive. Now, the Copy formula-contained cells using Right Click.

Clear Formula Using Quick Access Toolbar-Cell with Formulas are Copied

  • At this stage, click the Paste button in the toolbar which is active, and then choose the Values option to clear formulas from the cells.

Clear Formula Using Quick Access Toolbar-Paste as Values

  • Here, is our desired output.


9. Delete Formula Using VBA and Keep the Values Only

VBA is also a great tool, we can use it to delete the formulas from the cells writing simple code, and keep the values only. Let’s follow the guide to accomplish this:

  • In this image, we can see cells with formulas. Name the sheet as “VBA

 Delete the Formula Using VBA and Keep the Values Only

 Delete the Formula Using VBA and Keep the Values Only

  • In the Visual Basic Editor click Insert and then choose Module. It’ll create a new module.

 Delete the Formula Using VBA and Keep the Values Only

  • Write the following code and press F5
Sub values ()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("VBA")
ws.UsedRange.Value = ws.UsedRange.Value
End Sub
  • The formulas in the VBA sheet are now removed.


Conclusion

Finally, we have mastered several methods to clear a formula from Excel. Which one do you prefer to use? You can comment or ask any question related to this topic. Explore Exceldemy to learn more about Microsoft Excel.


Further Readings

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo