Afia Kona
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.
Download Workbook
2 Methods to Protect Excel Cells with Formulas
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.
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.
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.
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.
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.
Read More: How to Protect Excel Cells from Deletion (3 Handy Methods)
Similar Readings
- How to Unlock Cells in Excel When Scrolling (4 Easy Ways)
- Excel VBA to Lock Cells without Protecting Sheet (4 Ideal Examples)
- How to Protect Cells without Protecting Sheet in Excel (3 Ways)
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.
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.
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.
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.
➤ 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.
Read More: Lock a Cell after Data Entry Using Excel VBA with Message Box Notification Before Locking
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.
