Excel Protect Formulas

Protect Formulas in Excel

Formulas are an integral part of Excel file and without formulas, we cannot create reports or organize the data, so formulas are pivotal in excel. Once the formulas are applied we can edit them at any point in time, that’s common but there comes a possible error. Since we can edit the formula we end up deleting or wrong editing of formula so it will cause the wrong report summary and it may cost you millions of dollars. If you can spot the error quickly you are lucky but if not you will end up in a mess, but the good news is that we have an option of protecting our formulas so we will end up in a mess. In this article, we will show you how to protect formulas in excel.

How to Protect Formulas in Excel?

Protection is the key thing when it comes to excelling and sharing the same excel workbook with others. So the protection of formulas is as part of the protection of worksheets in excel, we need to follow simple steps to protect our formulas.

For example, look at the below data in excel.

In the above table, all the black-colored cells are formula cells, so we need to protect them. Assume we need to allow the users to work with other cells except for the cells which have formulas, follow the below steps, and protect them.

Step 1: By default, all the cells are locked in excel in excel, so if we protect the worksheet directly all the cells will be protected and users cannot work with any of the cells, so first we need to unlock all the cells of the worksheet.

Select the entire worksheet and press Ctrl + 1 to open the Format Cells window.

Step 2: In the above window click on the “Protection” tab.

Step 3: As you can see under “Protection” the checkbox of “Locked” is ticked, so that means all the cells are clocked now, so uncheck this box.

Step 4: Click on “Ok” and all the cells are unlocked now.

Step 5: Once all the cells are unlocked we need to lock only formula cells because we need to protect only formula cells, so how do you know which cell has the formula in it??

Select the entire worksheet and press the F5 key to open the “Go-To” window and press on the “Special” tab.

Step 6: This will take you to the “Go To Special” window like the below one.

From the above window choose “Formula” as the option.

Step 7: Click on “Ok” and all the cells which have formulas will be selected.

Look it has selected only the black font colored cells.

Step 8: Now again press Ctrl + 1 to open the Format Cell window and this time makes only these cells as “Locked”.

Click on “Ok” and only selected cells will be locked and protection applies only for these cells.

Step 9: Now we need to protect the worksheet in order to protect formulas in excel. So under the REVIEW tab click on the “Protect Sheet” option.

Step 10: In the “Protect Sheet” window we need to enter the password to protect the locked cells, so enter the formulas as you would like to give. (Make sure you remember the formula)

In the above window we can choose other actions that can be performed with locked cells, so by default first two options are selected, if you want to give any other actions to the user you can check those boxes. As of now, we are not allowing users to do any action with locked cells except for the selection of cells.

Step 11: Click on “Ok” and reenter the password in the next window.

Click on “Ok” and your formulas are protected.

If you try to do any kind of action in formula cells it will show below message for you.

Ok, like this we can protect formulas in excel.

How to Hide Formulas in Excel?

Formulas are protected and it is fine but we can go one step further as well i.e. we can hide formulas from viewing in the formula bar.

• As of now, we can see the formula in formula bar even after protection.

So in order to hide them, first unprotect the worksheet that we have protected, then choose only formula cell and open the “Format Cell” dialogue box.

• Under the “Protection” tab check the box of “Hidden”.

Click on “Ok” to close the above window.

• Now again protect the worksheet and all the formula cells will not show any formulas in the formula bar.

Things to Remember

• By default all the cells are locked, so to protect only formula cells unlock other cells.
• To select only formula cells use the “Go To Special” option (F5 is the shortcut key) and under “Go To Special” click on “Formulas”.

Recommended Articles