Hosne Ara
This article will discuss how you can protect formulas in Microsoft Excel. Sometimes, when you share spreadsheets with other people, you might want no one to edit any formulas used in the calculation because manipulation of formulas may result in erroneous results. Again, at times we may need to protect formulas only. For example, you have shared an excel file with your superior and he is allowed to make some changes to the spreadsheet. However, your superior should not change any formula used in the spreadsheet. In such cases, we can protect cells with formulas only.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
Step-by-Step Guideline to Protect Formulas in Excel
Suppose we have the below dataset containing sales data. Here I have used formulas to calculate revenues.
Now, I will protect the above cells with formulas by following some easy steps.
Step 1: Unlock All Cells from Excel Worksheet
To protect formulas only, first, we have to unlock all the cells in an excel worksheet. By default, all the cells in excel remain locked. Follow the below steps to unlock all the cells.
- Initially, select all the cells in the worksheet by pressing Ctrl + A.
- Next press Ctrl + 1 to bring the Format Cells dialog. Or you can right-click on the selection to get the dialog.
- When the Format Cells dialog shows up, select the Protection tab.
- Uncheck the Locked box option, and press OK.
Read More: How to Protect Formulas Without Protecting Worksheet in Excel
Step 2: Find Cells That Contain Formulas
Now we will find which cells contain formulas. To do that follow the below steps:
- First, select the entire worksheet by pressing Ctrl + A.
- Next, from Ribbon, go to Home > Editing group.
- Then from the Editing group, go to Find & Select > Go To Special.
- As result, the Go To Special dialog appears, put a checkmark on Formulas, and press OK.
- Consequently, all the cells that contain formulas are highlighted.
Step 3: Lock Formula Cells Only
This time we will lock formula containing cells and follow the below steps to do the task.
- While keeping the cells with formulas, press Ctrl + 1 to bring the Formula Cells dialog.
- Then put a checkmark on the Locked option under the Protection tab, and press OK. This will lock the formula cells again.
Step 4: Protect Formulas in Excel
In this step, we will protect the formulas. Here are the steps to perform the task.
- First, go to the Review tab and then click on the Protect Sheet command.
- As a consequence, the Protect Sheet dialog shows up.
- Now, make sure you have put a checkmark on the ‘Select locked cells’ option. If you want, you can enter a password to unprotect the sheet, but it is optional.
- Press OK after that.
- Finally, all the formulas are now protected. If you double-click any of the cells that have formulas, excel will show the below warning which means you cannot edit any formulas now.
Read More: How to Protect Formula in Excel but Allow Input (2 Methods)
How to Hide Formulas in Excel for Protection
Sometimes, you might want to hide the formulas which you have used in your calculation. It is very simple to hide formulas in excel. Here are the steps to perform the task.
Steps:
- First of all, select all the cells in the worksheet using Ctrl + A.
- Next, to find which cells have the formulas, go to Home > Find & Select > Go To Special.
- Then, from the Select section check Formulas and press OK.
- When all the cells with formulas are found out, select those cells and use Ctrl + 1 to bring up the Format Cells dialog.
- Afterward, go to the Protection tab, and put check marks on both Locked and Hidden options. Press OK after that.
- Now protect the sheet by going Review > Protect Sheet (explained in Step 4).
- At last, after protecting the sheet, we will see that formulas got hidden and locked as well.
Read More: Excel VBA: Protect and Hide Formulas
How to Unprotect Sheet and Show Formulas in Excel
You can unprotect formulas by following the below steps.
Steps:
- In the beginning, use the path: Review > Unprotect Sheet.
- Or you can go to the Home tab, form the Cells group follow Format > Unprotect Sheet.
Add ‘Lock Cell’ Icon to Excel Quick Access Toolbar
You can add the Lock Cell icon to the Quick Access Toolbar and see the status of the cells. To get the Lock Cells icon, follow the below steps.
Steps:
- Go to the Home tab, then follow Format > Lock Cell.
- Right-click on the Lock Cell and select ‘Add to Quick Access Toolbar’.
- Consequently, the Lock Cell icon is added to Quick Access Toolbar. Now if you select any cells and it is locked, the lock cell icon will be shaded.
Conclusion
In the above article, I have tried to discuss several methods to protect formulas in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.
