How to Remove Hidden Formulas in Excel (5 Quick Methods)

In this article, I am gonna discuss how you can remove hidden formulas in Microsoft Excel. While working in excel, often we hide or lock formulas to prevent unwanted changes in data. We also hide formulas in protected sheets of excel. So, let’s see how we can delete hidden formulas from excel worksheets.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


5 Methods to Remove Hidden Formulas in Excel

Suppose we have a dataset containing several items’ sales data. Here totals sales are calculated using the SUM function. However, we cannot see any formula in the Formula Bar as they are hidden.

5 Methods to Remove Hidden Formulas in Excel

Now, I will delete these hidden formulas using the below methods.

1. Show Excel Hidden Formulas and Delete Them with Paste Values Option

If I want to remove hidden formulas, first I have to unprotect the worksheet that contains the formulas. To unprotect excel sheets & delete the hidden formulas, follow the below steps.

Steps:

  • First, go to the worksheet where formulas are hidden. Then go to Review > Protect > Unprotect Sheet.

Show Excel Hidden Formulas and Delete Them with Paste Values Option

  • As a result, we can see that formulas are visible now.

Show Excel Hidden Formulas and Delete Them with Paste Values Option

As formulas are visible now, I will delete them using the Paste Values option. Applying Paste Values will remove only formulas and keep the data.

  • Select copy the cells that contain the formulas using Ctrl + C. Or you can copy the selected cells by going Home > Copy.

Show Excel Hidden Formulas and Delete Them with Paste Values Option

  • Then, right-click on the copied cells and click on the Paste Values from Paste Options (see screenshot).

Show Excel Hidden Formulas and Delete Them with Paste Values Option

  • Finally, we can see that all the formulas are deleted where values remain.

Read More: How to Remove Automatic Formula in Excel (5 Methods)


2. Remove Hidden Formulas Using Home Tab in Excel

In this method, I will discuss how you can show formulas in excel and delete them from the Home tab. Here are the steps we will follow to perform the task.

Steps:

  • Initially, I will show formulas using the Show Formulas option. To do that go to the worksheet where we have the formulas and go to Formulas > Show Formulas.

Remove Hidden Formulas Using Home Tab in Excel

  • Consequently, excel will show all the formulas we have used to calculate total sales. Now copy all the cells containing the formula.

Remove Hidden Formulas Using Home Tab in Excel

  • Then, go to Home > Paste > Paste Values.

Remove Hidden Formulas Using Home Tab in Excel

  • At last, we can see that all hidden formulas are gone, only values present.

Remove Hidden Formulas Using Home Tab in Excel

Note:

  • You can show formulas using keyboard shortcuts too (Ctrl + `).
  • We can use the FORMULATEXT function to show formulas in excel.


3. Apply Keyboard Shortcuts in Excel to Delete Hidden Formulas

This time, I will use keyboard shortcuts to remove hidden formulas.

Steps:

  • First, select the cells that contain formulas and copy them (using Ctrl + C).

Apply Keyboard Shortcuts in Excel to Delete Hidden Formulas

  • Next, press Alt + E + S to bring the Paste Special dialog.

Apply Keyboard Shortcuts in Excel to Delete Hidden Formulas

  • Now press V and hit OK/Enter.

Apply Keyboard Shortcuts in Excel to Delete Hidden Formulas

  • As a consequence, you will see that all the formulas are gone while values remain.

Note:

You can use alternative combinations of keyboard shortcuts to remove hidden formulas. To do that follow the below steps.

Steps:

  • Copy the formula containing cells using Ctrl + C.
  • Next, press ‘Ctrl + Alt + V, V, Enter‘ or ‘Alt + H + V + V‘.
  • Upon entering the above keys, we can see there are no hidden formulas.

4. Use Go To Special Feature to Find & Remove Cells with Hidden Formulas

Now, we will find formula containing cells using the Go To Special option and later delete them.

Steps:

  • First, go to the worksheet where you have the data with hidden formulas. Then press Ctrl + G to bring the Go To dialog box.
  • Next, press Special.

Use Go To Special Feature to Find & Remove Cells with Hidden Formulas

  • As a consequence, the Go To Special dialog appears. Click on Formulas and press OK.

Use Go To Special Feature to Find & Remove Cells with Hidden Formulas

  • Pressing OK will highlight all the cells that contain formulas.

  • Now you can copy highlighted cells and paste values using any of the methods mentioned above.

Note:

You can get the Go To Special dialog directly by following the path: Home > Editing > Find & Select > Go To Special.


5. Delete Hidden Formulas from Multiple Worksheets in Excel

You can remove hidden formulas from multiple sheets in excel. Let’s assume we have done sales data in Sheet1, Sheet2, and Sheet3. Now I will remove formulas from these three sheets at once. Follow the below steps to perform the operation.

Steps:

  • Firstly, select all three sheets using the Ctrl/Shift key.

Delete Hidden Formulas from Multiple Worksheets in Excel

  • Next, go to any of the worksheets of the selected group (say Sheet1) and select and copy cells that have the formulas.

Delete Hidden Formulas from Multiple Worksheets in Excel

  • Now go to Home > Paste > Paste Values to delete formulas.

  • As a result, you will see all the formulas from the selected cells in Sheet1 are gone.
  • Now if you go to any of the worksheets grouped initially, you will see hidden formulas are removed. For instance, go to Sheet 2 and see the below output.

  • From the above result, we can see only values are left, no formulas. Similarly, for Sheet3, you will gel the same result.

Note:

To unselect the grouped worksheets, simply select any of the sheets that are not in the group.

Read More: How to Remove Formulas from Entire Excel Workbook (2 Easy Ways)


Things to Remember

  • You can add the Paste Special command in the Quick Access Toolbar to delete hidden formulas.
  • Remember, you cannot show hidden formulas using the Show Formulas option in a protected sheet.

Conclusion

In the above article, I have tried to discuss several methods to remove hidden formulas in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo