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.
- 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.
- In the above window, click on the “Protection” tab.
- 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.
Click on “Ok,” and all the cells are unlocked now. - 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.
- This will take you to the “Got To Special” window like the below one.
- From the above window, choose “Formula” as the option.
- Click on “Ok,” and all the cells which have formulas will be selected.
Look, it has selected only the black font colored cells. - 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. - 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.
- 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. - 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 the 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 the 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
This has been a guide to Excel Protect Formulas. Here we learn how to protect formulas in excel and also hide the formulas and downloadable excel templates. You may learn more about excel from the following articles –
- Column Total in Excel
- Troubleshoot in Excel
- How to Unprotect Excel Workbook?
- Protect Sheet in VBA
- External Links in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
45.6K subscribers