How to lock cells with formulas in Microsoft Excel
In this tutorial, we will learn how to lock cells with formulas in Microsoft Excel.
To safeguard the formulas you have diligently crafted, Excel provides users with the ability to lock cells. This can be achieved by accessing the “Protection” tab within the “Format Cells” dialog box and utilizing the “Protect Sheet” feature.
Let’s say we have applied a formula to calculate profit for some products. We want to lock the cells with formulas. Following are the steps to be followed.
Locking cells with formulas involves safeguarding certain cells within a spreadsheet or worksheet to prevent inadvertent editing or modification while ensuring that the formulas embedded within those cells remain operational and dynamically update.
Step 1 – Select the Complete Sheet
Select the complete sheet by pressing the CTRL+A keys or by clicking on the triangle in the upper left corner of the sheet.
Step 2 – Press the CTRL+1 Keys
Press the CTRL+1 keys on the keyboard to open the Format Cells dialog box.
Step 3 – Locate the Protection Tab and Unselect the “Locked” Option
Locate the Protection tab in the dialog box.
Unselect the “Locked” option.
Hit the OK button.
Step 4 – Now Select the Cells with Formulas
Now select the cells with formulas.
For non-adjacent cells, we can select the by holding the CTRL key.
Another method is to press the CTRL+G keys and click on the “Special” option.
Select “Formulas” and click on OK.
Step 5 – Lock the Selected Cells i.e. The Cells With Formulas
Now lock the cells with formulas, selected in the previous step.
For this, press CTRL+1 keys and in the Protection tab check the box with the option “Locked”.
Step 6 – Locate the Protect Sheet Option
Locate the “Review” tab.
Perform a click on the “Protect Sheet” option.
Step 7 – Choose the “Select the Locked Cells” and “Select the Unlocked Cells” Options and Set a Password
Choose the “Select the Locked Cells” and “Select the Unlocked Cells” options.
Set a password.
Hit the OK button, now the cells with formulas will be locked for any editing.