How to Lock Cells in Excel (Step-by-Step Tutorial + Video)

Watch Video – How to Lock Cells in Excel

Sometimes you may want to lock cells in Excel so that other people can’t make changes to it. It could be to avoid tampering of critical data or prevent people from making changes in the formulas.

How to Lock Cells in Excel

Before we learn how to lock cells in Excel, you need to understand how it works on a conceptual level.

All cells in Excel are locked by default.

But…

It doesn’t work until you also protect these cells.

Only when you have a combination of cells which are locked and protected can you truly prevent people from making changes.

In this tutorial, you’ll learn:

  • How to lock all the cells in a worksheet in Excel.
  • How to lock some specific cells in Excel.
  • How to hide formula from the locked cell.

So let’s get started.

Lock all the Cells in a Worksheet in Excel

This essentially means that you want to lock the entire worksheet.

Now, since we already know that all the cells are locked by default, all we need to do is to protect the entire worksheet.

Here are the steps to lock all the cells in a worksheet.

  • Click the Review tab.
  • In the Changes group, click on Protect Sheet.How to Lock Cells in Excel - Protect Sheet
  • In the Protect Sheet dialog box:
    • Make sure that you’ve checked the box ‘Protect worksheet and contents of locked cells’ (it’s checked by default).Protect Sheet dialog box - selected locked cells
    • Enter a Password (if you want to password protect the sheet).Lock Cells in Excel - Protect Sheet dialog box enter password
    • Specify what a user is allowed to do. By default, the first two boxes are checked that allows a user to select locked and unlocked cells. You can allow other options as well such as formatting or inserting rows/columns.How to Lock Cells in Excel - Protect Sheet dialog box3
    • Click OK.How to Lock Cells in Excel - Protect Sheet dialog box4

If you have used a password, it will ask you to reconfirm the password.

Once locked, you’ll notice that most of the options in the ribbon are unavailable, and if someone tries to change anything in the worksheet, it shows a prompt (as shown below):

Error when you try to edit locked cells in Excel

To unlock the worksheet, go to Review –> Changes –> Protect Sheet. If you had used a password to lock the worksheet, it will ask you to enter that password to unlock.

Lock Some Specific Cells in Excel

Sometimes, you may want to lock some specific cells that contain crucial data points or formulas.

In this case, you need to simply protect the cells that you want to lock and leave the rest as is.

Now, since all the cells are locked by default, if you protect the sheet, all the cells would get locked. Hence you need to first make sure only the cells that you want to protect are locked, and then protect the worksheet.

Here is a simple example where I want to lock B2 and B3, and these contain values that are not to be changed.

How to Lock Cells in Excel - PMT Example

Here are the steps to lock these cells:

  • Select the entire sheet, and click on the dialog box launcher in the Alignment group in the Home tab (you can also use the keyboard shortcut Control + 1).Dialog box launcher to Open format cells dialog box
  • In the Format Cells dialog box, in the Protection tab, uncheck the box for Locked.How to Lock Cells in Excel - locked unchecked
  • Click OK.
  • Select the cells that you want to lock (in this case, B2 and B3).
  • Again click on the dialog box launcher in the Alignment group within the Home tab (or use the keyboard shortcut Control + 1).
  • In the Format Cells dialog box, in the Protection tab, check the box for Locked. The steps so far would unlock all the cells in the worksheet except the ones that you want to lock (B2 and B3 in this case).How to Lock Cells in Excel - locking cell B2 and B3
  • Go to the Review tab.
  • In the Changes group, click on Protect Sheet.
  • In the Protect Sheet dialog box:
    • Make sure you’ve checked the box ‘Protect worksheet and contents of locked cells’ (it’s checked by default).Protect Sheet dialog box - checked
    • Enter a Password (if you want to password protect the sheet).Protect Sheet dialog box2 when locking cells in Excel
    • Specify what all a user is allowed to do. By default the first two boxes are checked that allows a user select locked and unlocked cells. You can allow other options as well such as formatting or inserting rows/columns.How to Lock Cells in Excel - Protect Sheet dialog box3
    • Click OK.How to Lock Cells in Excel - Protect Sheet dialog box4

If you have used a password, it will ask you to reconfirm the password.

Protect the Entire Sheet (except a few cells)

If you want to protect the entire worksheet, but keep some cell unlocked, you can do that as well.

This can be the case when you have interactive features (such as a drop-down list) that you want to keep functioning even in a protected worksheet.

Here are the steps to do this:

  1. Select the cell(s) that you want to keep unlocked.
  2. Press Control + 1 (hold the control key and then press 1).
  3. In the Format Cells box that opens, click on the ‘Protection’ tab.
  4.  Uncheck the Locked option.Format Cells Dialog box when protecting a worksheet
  5. Click OK.

Now when you protect the entire worksheet, these cells would continue to work as normal. So if you have a drop-down list in it, you can continue to use it (even when the rest of the sheet is locked).

Here are the steps to now protect the entire sheet (except the selected cells):

  • Click the Review tab.
  • In the Changes group, click on Protect Sheet.Protect Sheet option in the ribbon
  • In the Protect Sheet dialog box:
    • Make sure that you’ve checked the box ‘Protect worksheet and contents of locked cells’ (it’s checked by default).Protect Sheet dialog box - selected locked cells
    • Enter a Password (if you want to password protect the sheet).Lock Cells in Excel - Protect Sheet dialog box enter password
    • Specify what a user is allowed to do. By default, the first two boxes are checked that allows a user to select locked and unlocked cells. You can allow other options as well such as formatting or inserting rows/columns.How to Lock Cells in Excel - Protect Sheet dialog box3
    • Click OK.How to Lock Cells in Excel - Protect Sheet dialog box4

If you have used a password, it will ask you to reconfirm the password.

Hide Formula When the Cells are Locked

Once you lock a cell in Excel, and that cell contains a formula, it’s visible in the formula bar when the cell is selected.

If you don’t want the formula to be visible, here are the steps:

  • Select the cells that you want to lock and also hide the formula from being displayed in the formula bar.
  • Click on the dialog box launcher in the Alignment group in the Home tab (or use the keyboard shortcut Control + 1).
  • In the Format Cells dialog box, in the Protection tab, check the Hidden box.How to Lock Cells in Excel - hide formula

Now when you protect the cells, the formula in it wouldn’t be visible in the formula bar.

TIP: Another way to hide the formula from getting displayed is by disabling selection of the cell. Since the user can’t select the cell, it’s content wouldn’t get displayed in the formula bar.

You May Also Like the Following Excel Tutorials:

  • Khairul says:

    Thanks Mr. Sumit. Its realy helpful.

    • Sumit Bansal says:

      Thanks for commenting Khairul.. I am glad you’re finding the tutorial helpful!

  • Kamangũ Mũndũ says:

    Hi Sumit, thanks for the excellent post. These tips and tricks keep me in my day job as the resident excel guru. Thanks a million.

    • Sumit Bansal says:

      Thanks for the kind words.. I am glad these tutorials are helping you in your day job!

  • indzara says:

    Very nicely written. Thanks for sharing, Sumit. Is there any way to protect the sheet and still have a Table auto-expand? I have some calculated columns in table and would like the auto-expand functionality but lock the formulas in calculated columns. Is it possible? Thanks in advance for your time.

    • Sumit Bansal says:

      I can’t think of doing it any way except VBA

      • indzara says:

        Thanks, Sumit. If you know of any articles that will help me write that code, please provide links. Thank you.

  • >