How to Protect Excel Cells with Formulas (2 Simple Methods)

Get FREE Advanced Excel Exercises with Solutions!

If you want to protect Excel cells with formulas, this article is for you. Here, we will walk you through 2 quick and easy methods to do the task effortlessly.


How to Protect Excel Cells with Formulas: 2 Methods

The following Student Score List table shows the Name, Math, Science, and Total Score columns. We can see the formula of cell E5 in the formula bar. We will use 2 methods to protect the cells with formulas. Here, we used Excel 365. You can use any available Excel version.

how to protect excel cells with formulas


Method-1: Using Review Tab to Protect Excel Cells with Formulas

In this method, we will go to the Review tab to protect Excel cells with formulas.

➤ First of all, we will select all the cells of our Student Score List table.

After that, we will click on the Alignment Setting tool marked with a red color box to open the Format Cells window.

We can also type CTRL+1 to open the Format Cells window.

how to protect excel cells with formulas

A Format Cells window will appear.

➤ After that, we will click on Protection > unmark Locked box > click OK.

Now, we have to lock the cells that have formulas, and to do so we will follow the following steps.

➤ At first, we will select the entire dataset of the Student Score List table > we will go to the Home tab > select Editing > select Find & Select.

➤ After that, from the Find & Select click on Go to Special.

A Go to Special window will appear.

➤ After that, we will click on Formulas > click OK.

how to protect excel cells with formulas

Now, we can see the cells that contain formulas have been selected.

➤ Afterward, we will again click on Alignment Setting tools or press CTRL+1.

A Format Cells window will appear.

➤ We will select Protection > mark the Locked box > click OK.

➤ Afterward, we will go to the Review tab > select Protect > select Protect Sheet.

A Protect Sheet window will appear.

➤ Then, give a password in the Password to Unprotect Sheet box.

Make sure the Selected locked cells box is marked.

➤ Click OK.

how to protect excel cells with formulas

A Confirm Password window will appear.

➤ We have to reenter the password in the Reenter password to proceed box > click OK.

Finally, when we click in cell E5 to edit the formula, we can see a warning appears. Therefore, the formulas have been protected with a password.

how to protect excel cells with formulas

Read More: How to Protect Excel Cells from Deletion


Method-2: Using Info Option from File Tab

In this method, we will use the Info option to protect Excel cells with formulas.

➤ First of all, we will select all the cells of our Student Score List table.

➤ After that, we will click on the Alignment Setting tool marked with a red color box to open the Format Cells window, or we can also type CTRL+1 to open the Format Cells window.

how to protect excel cells with formulas

A Format Cells window will appear.

➤ After that, we will click on Protection > unmark Locked box > click OK.

Now, we have to lock the cells that have formulas, and to do so we will follow the following steps.

➤ At first, we will select the entire dataset of the Student Score List table > we will go to the Home tab > select Editing > select Find & Select.

➤ Afterward, from the Find & Select click on Go to Special.

how to protect excel cells with formulas

A Go to Special window will appear.

➤ After that, we will click on Formulas > click OK.

Now, we can see the cells that contain formulas have been selected.

➤ Afterward, we will again click on Alignment Setting tools or press CTRL+1.

how to protect excel cells with formulas

A Format Cells window will appear.

➤ Then, we will select Protection > mark the Locked box > click OK.

Now, we will protect the formulas with a password.

➤ To do so, at first, we will go to the File tab.

how to protect excel cells with formulas

➤ Select Info > select Protect Workbook > select Protect Current Sheet.

A Protect Sheet window will appear.

➤ Afterward, we will give a password in the Password to Unprotect Sheet box.

Make sure the Selected locked cells box is marked.

➤ Click OK.

A Confirm Password window will appear.

➤ We have to reenter the password in the Reenter password to proceed box > click OK.

Finally, when we click in cell E5 to edit the formula, we can see a warning appears. Therefore, the formulas have been protected with a password.

how to protect excel cells with formulas


Download Practice Workbook


Conclusion

Here, we tried to show you 2 methods to protect Excel cells with formulas. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, feel free to know us in the comment section.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afia Kona
Afia Kona

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo