Al Arafat Siddique
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)
- Then, click the Right button and Copy the cells
- Now, click the Right button again and choose Values from the Paste Options
- 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)
- Right-click the mouse and Copy the cells
- After that, to Paste the values go to the Home tab and select Values from the Paste Values options
- 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.
- 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.
- Select Formulas from Go To Special window and click OK
- The above command will get the cells with formulas selected.
- 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
- Secondly, click the Right Button and select Paste Special option
- In the third step, select the Values option and then hit OK.
- 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
- Holding the Right Key of the mouse, drag the cursor to a little right and bring it back to its original position.
- From the given options choose “Copy Here as Values Only”
- 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.
- 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).
- After that, select and copy the formula contained cells of Sheet 1(one can choose any of the grouped sheets)
- Now, click the right button again and choose Values from the Paste Options.
- 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.
- In the Excel Options window, select the Paste option having a right arrow at the end, then click Add button, and finally hit OK.
- The last operation has added a Paste icon in the toolbar which is inactive. Now, the Copy formula-contained cells using Right Click.
- 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.
- 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“
- Go to the Developer tab and choose Visual Basic. The Visual Basic Editor will open up.
- In the Visual Basic Editor click Insert and then choose Module. It’ll create a new module.
- 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.
