Durjoy Paul
Let’s consider a calculation that has multiple columns referenced with formulas. If one value tampers the whole calculation will be lost. To avoid such unnecessary tampering we need to make the formulas hidden. This article will show you how to hide formula in an Excel sheet.
Download Practice Workbook
Download this practice workbook.
2 Ways to Hide Formula in Excel Sheet
1. Hide Formula in Excel by Formatting Cells
Here, we have a dataset of ten different stores with their income, expenses, and profits. You have the formula in column E and that formula we want to hide from our Excel sheet.
Steps
- At first, you need to select the cells that have formulas you want to hide.
- At first, click the ‘Home’ tab.
- Then go to the Number group and click on the small titled arrow at the lower right of the group.
- After clicking the small titled arrow from the number group, the ‘Format cell’ dialog box opens up. Click the ‘Protection’ tab.
- By choosing the ‘Protection’ tab, click both Locked and Hidden options.
- In the ribbon, you will find a ‘Review’ tab and click on it.
- After clicking the ‘Review’ tab, there is a ‘Protect’ group, click on the ‘Protect Sheet’ option.
- In the ‘Protect Sheet’ dialog box, you can either enter a password or leave it blank and click on ‘OK’.
- If you enter a password previously, you need to re-write this password to proceed and click on ‘OK’.
- After applying those steps you can protect your worksheet in such a way that if you click on a cell that has the formula, no formula would be shown in the formula bar.
Read More: How to Hide Formula in Excel without Protecting Sheet (2 Methods)
Similar Readings
- How to Hide Formulas from Other Users in Excel (2 Ways)
- How to Hide Formulas in Excel until Data Is Entered (2 Methods)
2. Hide Formula in Excel with VBA Codes
Steps
- At first, you need to Select ‘Alt+F11’ in order to run the VBA editor. If there is no Developer tab, you can adjust it by customizing the ribbon.
- After opening the VBA editor, click on ‘Insert’.
- From the ‘Insert’ tab, select ‘Module’.
- In the opened Module window, type the following VBA code:
Sub LockAndHideAllCellsWithFormulas()
With ActiveSheet
.Unprotect
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
.Cells.SpecialCells(xlCellTypeFormulas).FormulaHidden = True
.Protect AllowDeletingRows:=True
End With
End Sub
- Minimize the Module window and editor.
- Then click on the ‘View’ tab and go to the ‘Macros’
- Select the given ‘Macro Name’ and click ‘Run’.
- After using VBA codes you can protect your worksheet with the cell formula hidden.
Read More: How to Hide Formula in Excel Using VBA (4 Methods)
Conclusion
Both methods are practically proven to hide formula in Excel. For any kind of confusion, you can comment below and visit Exceldemy for further knowledge about Excel.
