Hosne Ara
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.
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.
- As a result, we can see that formulas are visible now.
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.
- Then, right-click on the copied cells and click on the Paste Values from Paste Options (see screenshot).
- 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.
- Consequently, excel will show all the formulas we have used to calculate total sales. Now copy all the cells containing the formula.
- Then, go to Home > Paste > Paste Values.
- At last, we can see that all hidden formulas are gone, only values present.
⏩ 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).
- Next, press Alt + E + S to bring the Paste Special dialog.
- Now press V and hit OK/Enter.
- 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.
- As a consequence, the Go To Special dialog appears. Click on Formulas and press OK.
- 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.
- Next, go to any of the worksheets of the selected group (say Sheet1) and select and copy cells that have the formulas.
- 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.
