• Home  / 
  • Excel
  •  /  How to protect cells in excel and hide the formula in a workbook

How to protect cells in excel and hide the formula in a workbook

Investment banker protects the excel file by password, but anyone having access to the file can easily access the formulas used in the cell by having a cursor on the cell. As part of our continued excel training series,  you will learn how to protect cells in excel.

cell security

Hence If you want to prevent a formula from displaying in the formula bar then this blog is all about it.

The purpose of hiding the formulas can be to hide the cell reference or hidden formulas used in the preparation of file.

Image 1 - Formula is visible in formula bar

Image 1 – Formula is visible in formula bar

  1. Select the range of cells whose formulas you want to hide. You can also select
  • nonadjacent ranges (by selecting the cell and press control to select another cell) or
  • The entire sheet.

Image 2 - Select all the cell of the sheet

Image 2 – Select all the cell of the sheet

  1. On the Home tab, in the Cells group, click Format.
Image 3 - Go to font setting option

Image 3 – Go to font setting option

  1. In the Format Cells dialog box, on the Protection tab, select the Hidden check box.
  2. Click OK.
    Image 4 - Format Cell Dialog Box

    Image 4 – Format Cell Dialog Box

  1. On the Review tab, in the Changes group, click Protect Sheet.

Read more article on Excel Data Security 

  1. Make sure the Protect worksheet and contents of “selected locked cells” check box is selected, and then click OK. This process will protect cells in excel worksheet. The cells which are selected are now locked and protected.
Image 5 - Protection of the worksheet

Image 5 – Protection of the worksheet

Learn how to unprotect excel sheet without password

CONVERSE: How to unhide the contents of cells to display in formula bar

After you do this, unprotected cells can be edited. You may want to make a backup copy of the workbook before you go on.

  1. First of all in the Review tab, in the Changes group, click Unprotect Sheet. If you created a password, enter the password when prompted.
Image 6 - unprotect the worksheet

Image 6 – unprotect the worksheet

  1. Select the range of cells with formulas you want to unhide (and cells used in the formulas if you hid those cells as well).
  2. On the Home tab, in the Cells group, click Format.
Image 7 - Go to font setting option

Image 7 – Go to font setting option

  1. On the Protection tab, clear the Hidden check box. Click OK.
Image 8 - Select hidden check box

Image 8 – Select hidden check box

  1. On the Review tab, in the Changes group, click Protect Sheet.
  1. Make sure the Protect worksheet and contents of “selected locked cells” check box is selected, and then click OK.

Image 9 - Protection of the worksheet

 Learn more about our Free Online Excel Courses

Comment below regarding Userform

About the author

Sagar Doshi