How to Hide Formula in Excel Sheet (2 Methods)

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.

Hide Formula in Excel by Formatting Cells

  • 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.

Conceal Formula in Excel by Formatting Cells

  • 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.

Conceal Formula in Excel by utilizing Formatting Cells

Read More: How to Hide Formula in Excel without Protecting Sheet (2 Methods)


Similar Readings


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

Hide Formula in Excel with VBA Codes

  • 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’.

Conceal Formula in Excel with VBA Codes

  • 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.


Related Articles

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo