How to Protect Formulas in Excel (With Easy Steps)

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.

Step-by-Step Guideline to Protect Formulas in Excel

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.

Unlock All Cells from Excel Worksheet

  • 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.


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.

Find Cells That Contain Formulas

  • Then from the Editing group, go to Find & Select > Go To Special.

Find Cells That Contain Formulas

  • As result, the Go To Special dialog appears, put a checkmark on Formulas, and press OK.

Find Cells That Contain Formulas

  • 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.

Find Cells That Contain Formulas


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.

Protect Formulas in Excel

  • 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.

Protect Formulas in Excel

  • 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.


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.

Hide Formulas to Protect in Excel Worksheets

  • Next, to find which cells have the formulas, go to Home > Find & Select > Go To Special.

Hide Formulas to Protect in Excel Worksheets

  • Then, from the Select section check Formulas and press OK.

Hide Formulas to Protect in Excel Worksheets

  • 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.

Hide Formulas to Protect in Excel Worksheets

  • 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.


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.

Remove Protection and Unhide Formulas in Excel

  • 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’.

Add ‘Lock Cell’ Icon to Excel 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.

Add ‘Lock Cell’ Icon to Excel Quick Access Toolbar


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.

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo