How to Lock and Hide Formulas in Excel

Locking a formula means no one can edit or delete your formula except you, and hiding a formula in Excel means no one can see your formula. In this tutorial, you’ll learn to do both at once.

Formulas and functions are among the most powerful features in Excel. We use them every now and then. The efficiency of your calculation crucially depends on them. When we're talking about one of the most powerful features, there has to be some kind of security problem. Yes, we have two in this case.

1. We don’t want others to see our formula.

2. We must stop others from editing or deleting our formulas, both accidentally and deliberately.

You can solve these issues separately if you want. For example, if you don’t want others to see your formula, you can hide it. If you want to stop others from editing or deleting your formula, you can protect them.

But sometimes we need to lock and hide formulas in Excel but allow input on other cells. In this article you’ll find a detailed step-by-step procedure on how you can protect and hide formulas in Excel.

First, we need to select the entire worksheet and you can do that by pressing CTRL+A. There are several ways of selecting a cell or a range of cells. Please check out this video tutorial if you want to learn more about it: Select Cells in Excel 

After you’ve selected the worksheet, click on the "Home" tab, "Format" and then "Format Cells."

Now a popup will open and you need to click on the "Protection" tab from the Popup.

Now you need to uncheck both the "Locked" and "Hidden" checkmarks from here and click OK. Excel usually locks all of the cells by default. This will allow us to keep all the cells that don’t have a formula in them open.

Now we need to select all the cells that have the formula in them. Under the home tab, select "Find & Select". Now click on "Go to Special". Now from the popup, select formulas. Click OK, and you are done! All the cells that have a formula in them are now selected. Now these are the cells that we need to protect and hide the formula. So, to protect all the cells with the formula that we previously unlocked.  

After you’ve selected the cells with formula, click on the "Home" tab, "Format" and then "Format Cells". Now a popup will open and you need to click on the "Protection" tab from the Popup. Now you need to checkmark both "Locked" and "Hidden" from here and click ok.

Lastly, you need to go to the review tab and then click on the "Protect Sheet" option. Now enter the password twice and click ok. This is how you can lock and hide formulas in Excel without protecting the entire sheet. Thanks a million for reading this far. If you want to hide a formula in excel or lock a formula in excel separately, you can do that also. Check the related article below.

If you wish to undo what we have done till now, go to the “Review” tab and click on “Unprotect Sheet”. Enter the password twice. Now select the entire worksheet and check mark locked and uncheck hidden.

If possible, please support us by becoming a Patron. It means a lot to us. Thanks a million for your time. Leave a comment if you have any questions.