How to Lock Formulas in Excel

Usually, formulas are added in cells, and suppose a cell containing a formula is deleted, what would you do?

Yes, it would be highly annoying because all your work and efforts are removed instantly. People often ask questions about how they can protect their spreadsheets from any damage whether it is intentional or accidental. Well, the simple answer to this is by locking down the cells that contain formulas you can secure the data. Once the cells are locked, nothing can delete the data no matter if it is done intentionally or by mistake.

You would have noticed that Excel always provides solutions to queries. If you click on a cell that contains a formula, you will see the formula appears in the formula bar. When you need to figure out the formula, you can do this by opening the Formulas tab. Next, you need to open the Formula Auditing group and then choose the Evaluate Formulas option.

You can also hide the formulas from appearing in the formula bar or anywhere in the sheet. Because sometimes you don’t want others to see what procedure you have used to calculate the final outcome.

Each cell in the sheet is locked automatically. When you protect the locked cells only then you can control others from making changes to the sheet.

Let’s find out how to lock formulas in Excel:

First of all, you have to select all the cells and unlock them.

It must be confusing to you. Let me explain this to you. As I already mentioned that when locked cells are protected only then you can stop others from making changes. However, here in this case we need to lock only those cells that contain formulas. For this, you have to unlock all of them and then choose once again to lock cells that contain formulas in them.

To unlock cells:

Choose all the cells in the worksheet. Or else you can use the keyboard shortcut CTRL + A.
Press CTRL + 1 from the keyboard to open the format cells dialog box.
Choose the Protection option from that dialog box.

Now, uncheck the “Locked” option.

Press OK.
Now, you need to choose the cells that contain formulas.
Once all the cells are unlocked, ensure that cells containing formulas are locked. For this, you have to choose all the cells containing formulas.

You can follow the steps for this:

Choose all the cells in the worksheet.
Open the Home tab and click on Find & Select option from the Editing group.

Choose the Go to Special option from the drop-down menu.

Select Formulas from the Go To Special box.

Press OK.
Now, all of the cells that contain formulas are selected.
Well, now is the time to lock all the cells that contain formulas in them. once the cells are selected, we need to lock them.
To lock all the cells with formulas, below are some easy steps to follow:
Press CTRL + 1 for all the cells with formulas.
Choose the Protection tab in the format cells dialog box.

Now, check the “Locked” option.

Press OK.

Now, let’s protect the Worksheet by following some more steps:

Open the Review tab.

Choose Protect Sheet option.

From the Protect Sheet dialog box, check the Protect worksheet and the contents of the locked cells.

You can put a password as well.
Press OK.

You will see that all the steps have now locked formulas in the cells. Any user will not be able to make changes to the sheet. If anyone tries to make changes to the sheet, a message will appear on the sheet instantly.

How to Hide Formulas in Excel

Sometimes, you need to hide formulas from others so that no one else can figure out how you have calculated the values. Once the formula is hidden, it will not appear in the formula bar when the cell is clicked. Below are some steps to follow for hiding Excel formulas:

Choose a cell range that contains formulas you need to hide.
Press the CTRL key to select non-adjacent cells. If you need to select the whole sheet, you can press CTRL + A shortcut.
In case you need to select all cells containing formulas, open the Go To Special tab and choose the Formulas option.
Choose the Format Cells option.
Press CTRL + 1 from the keyboard.
Right-click on the selected cells and open the Format Cells option given in the context menu.
Open the Home tab and choose Cells group. Select the Format option and then the Format Cells option.
Choose the Hidden checkbox in the Format Cells dialog box.
The Locked option is selected automatically and it stops the cell data from being edited.

Press OK.

Now, you can protect the sheet easily.

This is how to lock formulas in Excel, you also need to bear in mind if the sheet is not protected, locking cells and hiding formulas would be useless.