Have you built a model/report/analysis in Excel that’s found to be useful by others too? Used some magical formulas and functions? Then you must have thought of protecting the cells containing those magical formulas. Here are the steps to protect and hide the cells containing formulas in Excel:
Step:1. Remove protection from entire sheet that has the formulas (note that by default, entire sheet is protected in Excel) by following the below steps:
a. Go to the sheet that has the formulas that you want to protect and hide. Select all the cells in the sheet.
b. Bring up the “Format cells” dialogue by hitting Ctrl + 1 on your keyboard. Click on “Protection” tab. Uncheck the “Locked” and “Hidden” options. Hit “OK” to close the “Format Cells” dialogue box.
Step:2. Select the cells containing formulas and apply protection only to those cells. To do this, follow below steps:
a. Invoke the “Go To” dialogue by hitting “Ctrl + G” on your keyboard. Click on “Special…” button to invoke “Go To Special” dialogue box. Select “Formulas” option and let all the sub-options under “Formulas” option be checked. Hit “OK” to close the dialogue box. Now you will see that all the cells containing formulas in the sheet are now selected. For the ease of understanding, have highlighted the formulas containing cells in this example in yellow color.
b. Now, while keeping the range containing the formulas selected, invoke the “Format cells” dialogue by hitting Ctrl + 1 again on your keyboard. Click on “Protection” tab. Then select the “Protection” and “Hidden” options to ensure both are checked. Hit “OK” button to close the “Format Cells” dialogue box.
Step:3. Password protect the sheet containing the formulas, by following the below steps:
a. Bring up the “Protect Sheet” dialogue by right clicking on the sheet name and hitting on “Protect Sheet”. Enter the password, hit “OK”. Then reenter the same password and then hit “OK” again.
Now all the cells containing formulas in this sheet are hidden (i.e., formulas in the cells cannot be viewed in formula bar) and protected (i.e., content of the cells containing formulas cannot be edited).
The idea is to first remove the default protection that is enabled for the entire sheet and then select only the desired cell range and then enable protection. Finally apply password protection.
Hope that’s a learning for today. Watch the space for more.
Leave a Reply