How to Lock Formulas in Excel (a Step-by-Step Guide)

Watch Video – How to Lock Formulas in Excel

Excel formulas are easy to create and edit in Excel.

You can easily edit a formula through the formula bar or directly in the cell.

While this makes it convenient to create formulas in Excel, it comes with a few disadvantages as well.

Consider this.

You are going through a worksheet full of formulas, and you accidently hit the delete key, or backspace key, or some other number/alphabet key.

Now you’ll be lucky if you’re able to spot the error and correct it. But if you are not, it may lead to some erroneous results.

And let me tell you this, errors in Excel have cost millions to companies (read this or this).

The chances of such errors increase multifold when you share a file with colleagues or managers or clients.

One of the ways to prevent this from happening is to lock the worksheet and all the cells. However, doing this would prevent the user from making any changes to the worksheet. For example, if you’re sending a workbook to your manager for review, you may want to allow him to add his comments or change some cells.

A better workaround to this is to lock only those cells that have formulas in it.

How to Lock Formulas in Excel

Before I show you how to lock formulas in Excel, here is something you must know:

By default, all the cells are locked in Excel. Only when you protect the locked cells can you truly restrict the user from making changes. This also means that if a cell is not locked and you protect it, the user would be able to make changes.

Here are the steps to lock formulas in Excel (explained in detail later on):

  1. Select all the cells and unlock these.
  2. Select all the cells that have formulas (using Go To Special).
  3. Lock these selected cells.
  4. Protect the worksheet.

Now that I have outlined the steps above, let’s dive in and see how to do this (and more importantly, why we must do this):

Step 1: Select All the Cells and Unlock it

While you may find it confusing, bear with me and keep reading.

As I mentioned, only the cells that are locked as well as protected can truly be restricted. If all the cells are locked, and I protect the entire worksheet, it would mean a user can’t change anything.

But we only want to lock (restrict access) to the cells that have formulas in it.

To do this, we first need to unlock all the cells and then select and lock only those cells that have formulas in it.

Here are the steps to unlock all the cells:

  • Select all the cells in the worksheet (use the keyboard shortcut Control + A).
  • Use the keyboard shortcut Control + 1 (hold the Control key and then press 1). This will open the format cells dialog box.
  • In the format cells dialog box, select the Protection tab.How to Lock Formulas in Excel - Protection tab
  • Uncheck the ‘Locked’ option.How to Lock Formulas in Excel - Unchek Locked
  • Click ok.

Step 2: Select All the Cells that Have Formulas

Now that all the cells have been unlocked, we need to make sure that the cells that have formulas are locked.

To do this, we need to first select all the cells with formulas.

Here are the steps to select all the cells that have formulas:

  • Select all the cells in the worksheet (use Control + A).
  • Go to Home and within the Editing group, click on Find & Select.
  • From the drop down, select Go to Special.How to Lock Formulas in Excel - Go to special
  • In the Go To Special dialog box, select Formulas.
  • Click OK.

This would select all the cells that have formulas in it.

Step 3: Lock the Cells with Formulas

Now that we have selected the cells with formulas, we need to go back and lock these cells (enable the lock property that we disabled in step 1).

Once we do this, protecting the worksheet would lock these cells that have formulas, but not the other cells.

Here are the steps to Lock Cells with Formulas:

  • With the cells with formulas selected, press Control + 1 (hold the Control key and then press 1).
  • In the format cells dialog box, select the Protection tab.How to Lock Formulas in Excel - Protection tab
  • Check the ‘Locked’ option.How to Lock Formulas in Excel - locked
  • Click ok.

Step 4 – Protect the Worksheet

Now that the ‘Locked’ property is enabled for cells with formulas (and not for other cells), protecting the entire worksheet would only restrict access to the cells with formulas.

Here are the steps to protect the worksheet:

  • Go to the Review tab.How to Lock Formulas in Excel - review
  • Click on Protect Sheet.How to Lock Formulas in Excel - Protect Sheet
  • In the Protect Sheet dialog box, make sure the option ‘Protect worksheet and contents of the locked cells’ is checked.How to Lock Formulas in Excel - protect worksheet and content
  • [Optional] Specify the password.
  • Click OK.

Once you are done with the above four steps, all the cells that have formulas would be locked, and the user wouldn’t be able to change anything in it.

If the user tries to change the cells, he/she will get a prompt as shown below:

How to Lock Formulas in Excel - locked prompt

How to Hide Formulas in Excel

When you lock formulas in Excel, the user can’t make any changes to the cells with formulas.

However, if that cell is selected, the formula in the cell would be visible in the formula bar.

While this isn’t an issue in most of the cases, but if you don’t want the formula to be visible, you need to hide it.

Here are the steps to hide formulas in locked cells:

  • Select all the cells in the worksheet (use Control + A).
  • Go to Home and within the Editing group, click on Find & Select.
  • From the drop down, select Go to Special.
  • In the Go To Special dialog box, select Formulas.
  • Click OK. This will select all the cells that have formulas in it.
  • Press Control + 1 (hold the control key and then press 1). This will open the format cells dialog box.
  • In the Format Cells dialog box, go to Protection tab.
  • Check the Hidden option.How to Lock Formulas in Excel - hidden
  • Click OK.

Now, when the user selects a cell that has a formula and is locked, he/she will not be able to see the formula in the formula bar.

Note: As mentioned earlier, a cell that has not been locked can not be protected. The same applies when you hide formulas in Excel. Unless the cell is locked, only checking the Hidden checkbox wouldn’t do anything. To truly hide formulas in Excel, the cells should have the Locked and Hidden check boxes selected, and then it should be protected.

You May Also Like the Following Excel Tutorials:

  • Kathryn says:

    Can this be applied to an entire FILE, not just the worksheet?

  • Carlos Ruano says:

    Thank you! I just found your site, and it has already helped me immensely.

    Your instructions are wonderfully clear and detailed just enough without going overboard. Awesome!

    • Sumit Bansal says:

      Glad you found it useful!

  • Branislav Kollár says:

    Hi. Nice article. There is no thing more evil to do than to hardcode a value to a verification column.

    There is a typo in the section How to Lock Formulas in Excel in the second step 2. Select all the cells that have formulas (using paste special). It should be Go To Special, not paste special. Typo can cause confusion.

  • Muhammad Fathy says:

    Many thanks for you, Awesome Tips

  • >