How to Lock Formulas in Excel Without Protecting the Workbook

Step 1: Open the spreadsheet

Suppose we have an Excel as follows which contains some calculations done with the help of formulas.

Step 2: Unlock all Cells

1. Select the whole excel sheet by pressing Ctrl + A.

Step 3: Right- Click and Select Format Cells

Right click while the cells are selected. Click on “Format Cells” option or Press Ctrl + 1.

Step 4: Go to the Protection Tab

The below box will now open. Choose the “Protection” tab.

Step 5: Unmark the Locked Checkbox and Click Ok

Observe the “Locked” option is marked. Unmark this checkbox

Step 6: Select the cells with formulas

1. Next go to the “Home” section. Select “Find & Select” option.

2. Completing the above step will open a dialog box as shown below. Click on “Go to Special”.

3. This will open a separate dialog box as shown below.

4. Choose the “Formulas” option. Click “Ok”.

5. After the above step. All the cells containing formulas will be selected as shown below.

Step 7: Lock the cells

1. Now press Ctrl + 1. In the “Protection” tab check the “Locked” and “Hidden” option.

2. The options will look as below. Click “Ok”. All the cells containing formulas are now locked and hidden.

Step 8: Protect the cells

1. While the all the locked formula cells are still selected. Go to “Review” section.

2. Choose the “Protect Sheet” option.

3. Now in the “Protect Sheet” dialog box, enter a password to secure the sheet. After you have entered the password click “Ok”.

4. Confirm the password by re-entering it. Click on “Ok”.

The formulas are now safe and protected. You are free to make changes to any of the cells as you like. However, if you attempt to edit the cells that have the formulas, a message box will appear a shown below.

Fed up with unintentionally messing up your Excel formulas and causing chaos in your meticulously designed spreadsheets? Don’t worry! Mastering the skill of locking formulas is your solution. In this brief tutorial, we’ll delve into the techniques to protect your calculations from unintended alterations, ensuring that your Excel wizardry remains undisturbed. Let’s jump into the straightforward practice of locking formulas and ensuring the security of your data sorcery.

In this article, we will discuss how to lock and protect the formulas within the Excel sheet along with an example.