Hide and lock formulas in Excel

This post tells about the usage of the hide and lock formula feature available in Microsoft Excel and how you can unprotect and unhide the same.

How to Lock formulas in Excel ?

When we go through a worksheet full of formulas there are chances of editing the formula accidentally, this may result in a huge error.

So here Excel allows the user to protect the cells with formula alone, instead of protecting the entire worksheet.

To lock formulas in Excel firstly you need to unlock all cells in the worksheet, to do so follow the below steps:

  • Select all cells in the worksheet by pressing Ctrl+A.
  • Right Click and select Format cells or press Ctrl+1.
Lock formulas in Excel
  • Select the Protection tab from the format cells dialog box.
  • Uncheck the locked option.
  • Click OK.
Lock formulas in Excel

Now you have to identify and select the cells with the formula:

  • Select all cells in the worksheet by pressing Ctrl+A.
  • Go to the Home tab and then click on Find & Select from the editing group.
  • A drop-down appears, select Go to special, and then select formulas.
Lock formulas in Excel
Lock formulas in Excel
  • Click OK.

Now the cells with formulas will be automatically selected.

Lock formulas in Excel

Once done,  you have to lock the cells with the formula.

Below are the steps to follow:

  • With the cells with formula selected, Right Click and select Format cells or press Ctrl+1.
Lock formulas in Excel
  • Select the Protection tab from the format cells dialog box.
  • Check the locked option.
Lock formulas in Excel
  • Click OK.

The lock cells with formula would be complete only when you protect the worksheet.

Follow the given steps:

  • Select the Review tab and click on the Protect sheet.
  • A dialog box appears, make sure to check the ‘Protect worksheet and contents of the locked cells’.
  • Specify the password and reenter the password.
Lock formulas in Excel
  • Click OK.

Finally, the cells with the formula are locked and the users cannot change anything with the formula.

Lock formulas in Excel

How to Hide Formulas in Microsoft Excel ?

Locking the formula cells will not allow the users to edit but the formula will be visible in the formula bar.

Hide Formulas in Microsoft Excel

Excel provides an option to hide the formulas also. The steps to hide formulas in locked cells:

  • Select all the cells in the worksheet using Control + A.
  • Go to Home and click on Find & Select from the Editing group.
  • A drop-down appears, select Go to special, and then select formulas
Hide Formulas in Microsoft Excel
  • Click OK.

Now the cells that have formulas will be selected.

  • Right-click and select Format cells or Press Control + 1. This will open the format cells dialog box.
  • In the Format Cells dialog box, select the Protection tab.
  • Check the Hidden option.
Hide Formulas in Microsoft Excel
  • Click OK.

Now, if the user selects a cell with a formula and is locked, he/she will not be able to see the formula in the formula bar.

Hide Formulas in Microsoft Excel

How to unprotect and unhide formulas in Excel?

  • Go to the Review tab and select Unprotect sheet.
  • Enter the password and click OK.
How to unprotect and unhide formulas

You may want to uncheck the Hidden checkbox after unprotecting the worksheet to let the users see the formulas for future use. Press Ctrl + 1 to open the Format Cells dialog box, go to the Protection tab and uncheck the Hidden box.

How to unprotect and unhide formulas

Leave A Reply

Your email address will not be published. Required fields are marked *

You May Also Like

In this article, you will learn about the SKEW.P function, the formula syntax and usage of the function in Microsoft...
In this article, you will learn about the SKEW function, the formula syntax and usage of the function in Microsoft...
In this article, you will learn about the RANK.EQ function, the formula syntax and usage of the function in Microsoft...
  • Excel
  • November 23, 2021