How to Protect Formulas in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

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.

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.

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.

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.

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

Related Articles

<< Go Back toÂ Excel FormulasÂ |Â Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF