How To Lock Cells In Excel – Protecting Excel Worksheets

Whether you are trying to steer clear of accidental overwrites, feeling fickle, or trying to protect some important data; all your reasons are good enough to lock cells in Excel. Locking a cell means that the contents of the cell cannot be changed and the user will receive a prompt when trying to edit a locked cell, not allowing edits to the locked cell.

Some fun facts about cell locking:

  • All cells are locked by default (but not protected).
  • The cells only get their locked properties when the sheet is protected.
  • Once the cells are locked and the sheet protected, the locked cells cannot be edited.
  • When trying to edit a locked cell, a prompt will appear, hampering the edit.

There's a whole world of cell locking but also of cell unlocking. This tutorial will give you the 101 on locking cells, formulas, sheets, and more with locking's sidekick; unlocking.

How To Lock Cells In Excel

How to Lock Cells in Excel

All the cells in a workbook are locked by default. Great, so why are we here? Because these locked cells need to be protected in order for them to work as expected.

You can think of it as – Lock is a kind of flag associated with each cell that enables Excel to understand if that particular cell should or shouldn't be allowed for editing when sheet protection is turned on. Without the sheet protection feature turned on both locked as well as unlocked cells behave the same way.

For you to lock cells in a way that their contents cannot be altered by other users, the cells need a double treatment:

  • Step 1 – The cells have to be locked and
  • Step 2 – The sheet has to be protected.

A little heads up. Whether locking a cell, a range of cells, formulas in cells, a column; essentially all of this is "cell" locking and requires relevant selection of the cells. Now let's find out more about the mentioned double treatment; locking and protecting.

How to Lock all the Cells in a Worksheet

Let's try to understand how to lock all the cells in a worksheet.

Step 1 – All Cells Need to be Locked

The first part of the double treatment for all the cells is already automatically done; since all the cells are locked by default in Excel. So, we can directly move to Step 2. In Step 2, the sheet protection feature needs to be activated. Let's see how to get it done.

Step 2 – Protecting the Worksheet

Time to protect the worksheet.

  • In the Review tab, from the Protect section, click the Protect Sheet.

Locking-Cells-In-Excel-By-Protecting-Sheet-01

  • Clicking this button will have a Protect Sheet dialog box appear as shown below.

Protect-Sheet-Dialog-Excel-02

  • The first text box is for setting a password on the sheet which needs to be entered when trying to unprotect the sheet. This is optional and can be skipped. The 'Protect worksheet and contents of locked cells' tick box should be automatically checked. Leave it checked. Under Allow all users of this worksheet to: select how much control you would like to provide the users by checking the tick boxes against the relevant options and click OK.

Protect-Sheet-Dialog-Excel-Confirm-Password-03

  • If you used a password, you would see a dialog box asking to reconfirm the password. If you haven't specified a password, your sheet should be protected now. Nearly all of the options on the ribbon should be disabled.

Protect-Sheet-Popup-Message-04

  • Trying to change any cell on the worksheet will not work, and a window hindering the attempted changes will pop up.

How to Lock Specific Cells in a Worksheet

Interestingly, locking specific cells has a few additional steps than locking all of the cells. We will go back to assuming the initial state of a workbook i.e. when all cells are locked by default and the worksheet is unprotected. We need to unlock all the cells and then lock the chosen cells. Here are the steps to do this:

The cells with the values in the blue-colored font are the 2 cells we want to lock on the sheet so that their values cannot be changed. Apart from these two, we wish to lock and protect all the other cells on the sheet.

Lock-Specific-Cells-On-A-Worksheet-08

Step 1 – Unlock all Cells on the Worksheet

So, the prerequisite here is to unlock all the cells on the worksheet. To do this follow along.

  • Select all the cells on the sheet and navigate to the 'Home' tab, in the 'Alignment' section, click the 'Alignment Settings' option (the little diagonally pointing arrow in the bottom right corner).

Selecting-All-Cells-On-The-Worksheet-05

  • This will open the Format Cells dialog box. In the Format Cells dialog box, select the Protection tab. The Locked checkbox will be checked by default. Uncheck it to unlock all the cells on the worksheet.

FormatCells-Dialog-Locked-Checkbox-06

  • Click OK and all the cells in the current worksheet will be unlocked.

Step 2 – Lock Specific Cells on the Worksheet

Now we will select the cells on the sheet that we want to lock i.e., D10 and E11. To lock these cells follow the below steps:

  • Select the D10 and E11 cells, we will again launch the Format Cells dialog box to lock the chosen cells. Click the 'Alignment Settings' option in the Alignment section under the Home.

FormatCells-Dialog-Locked-Checkbox-07

  • In the Format Cells dialog box, select the Protection tab. Check the Locked tick box to lock the chosen cells.

Step 3 – Protecting the Worksheet

Now we have locked the chosen cells. If you try, you will notice that you can still edit the locked cells. For cell locking to work the worksheet needs to be protected and this is what we are going to do now.

  • Under the Review tab, in the Protect section, click the Protect Sheet. This will launch the Protect Sheet dialog box.

Protect-Sheet-dialog-box-09

  • Add a password if you wish (optional). Check if the Protect worksheet and contents of locked cells tick box is automatically checked. Leave it checked. Under Allow all users of this worksheet to: select how much control you would like to provide the users by checking the tick boxes against the relevant options and click OK.
  • Reconfirm the password if any, is used.

Now you can check the sheet; only meddling with the locked cells will trigger the sheet protection prompt. The other cells are still editable.

Recommended Reading: All About Page Breaks in Excel

How to Lock all Formula Cells in a Worksheet

To protect formulas in a sheet, you need to unlock all the cells in the sheet, select the formula cells, lock them and then protect the sheet. Pretty much like how we lock specific cells (mentioned above). For big and complex worksheets, this would surely be a very daunting task with so many formula cells to lock. Not to worry, we will have Excel select the formula cells itself, which we will then lock and protect the sheet.

Easy now, right? Let's see the steps for this:

Step 1 – Unlock all Cells on the Worksheet

So, the prerequisite here is to unlock all the cells on the worksheet. To do this follow along.

  • Select all the cells on the sheet and navigate to the 'Home' tab, in the 'Alignment' section, click the 'Alignment Settings' option (the little diagonally pointing arrow in the bottom right corner).

Selecting-All-Cells-On-The-Worksheet-05

  • This will open the Format Cells dialog box. In the Format Cells dialog box, select the Protection tab. The Locked checkbox will be checked by default. Uncheck it to unlock all the cells on the worksheet.

FormatCells-Dialog-Locked-Checkbox-06

  • Click OK and all the cells in the current worksheet will be unlocked.

Step 2 – Find and Lock Cells Containing Formulas

Now, let's quickly try to find and lock the cells that contain formulas:

  • Under the Home tab, from the Editing section, click on the Find & Select button, then click on the Go To Special. This will open the Go To Special window.

Go-To-Special-Option-In-Excel-11

  • In the Go To Special dialog box, select Formulas from the radio buttons. All the checkboxes boxes under the Formulas radio button should get automatically checked.

Go-To-Special-Select-Formulas-In-Excel-12

  • Click OK and all the formula cells on the worksheet will be selected now. Be careful not to click any other cell on the worksheet as that will unselect the formula cells.
  • After selecting the cells, we will again launch the Format Cells window to lock the chosen cells. Click the 'Alignment Settings' option in the Alignment section under the Home.

Lock-Selected-Cells-Alignment-Settings-13

  • In the Format Cells dialog box, select the Protection Check the Locked tick box to lock the chosen cells.

This has locked the chosen cells. If you try, you will notice that you can still edit the locked cells. The sheet needs to be protected for the cell locking to work.

Pro Tip: If you want your formulas to be hidden and not displayed in the formula bar after locking the cells, click the Hidden checkbox on the Format cells window.

Step 3 – Protecting the Worksheet

Now we have locked the chosen cells. If you try, you will notice that you can still edit the locked cells. For cell locking to work, the worksheet needs to be protected and this is what we are going to do now.

  • Under the Review tab, in the Protect section, click the Protect Sheet. This will launch the Protect Sheet dialog box.

Protect-Sheet-dialog-box-09

  • Add a password if you wish (optional). Check if the Protect worksheet and contents of locked cells tick box is automatically checked. Leave it checked. Under Allow all users of this worksheet to: select how much control you would like to provide the users by checking the tick boxes against the relevant options and click OK.
  • Reconfirm the password if any is used.

If you check now, the entire worksheet can be edited except the cells with the formulas.

How to Lock a Column in a Worksheet

Locking a column works on pretty much the same principle – unlock all the cells in the sheet, select all the cells in the column(s) you wish to lock, lock the cells, protect the sheet.

Here are the detailed steps:

Step 1 – Unlock all Cells on the Worksheet

So, the prerequisite here is to unlock all the cells on the worksheet. To do this follow along.

  • Select all the cells on the sheet and navigate to the 'Home' tab, in the 'Alignment' section, click the 'Alignment Settings' option (the little diagonally pointing arrow in the bottom right corner).

Selecting-All-Cells-On-The-Worksheet-05

  • This will open the Format Cells dialog box. In the Format Cells dialog box, select the Protection tab. The Locked checkbox will be checked by default. Uncheck it to unlock all the cells on the worksheet.

FormatCells-Dialog-Locked-Checkbox-06

  • Click OK and all the cells in the current worksheet will be unlocked.

Step 2 – Select & Lock all the Column Cells

Since all the cells in the worksheet are unlocked now, so we will now try to select the column that we want to lock and set the Locked property to true for all its cells.

  • Select the column(s) you want to lock. For our case, we want to lock the prices so we'll select column C by clicking on C. You can also select multiple columns.

Select-Column-To-Be-Locked-Click-Alignment-Settings-14

  • After selecting the column, we will again launch the Format Cells dialog box to lock the chosen column. Click the Alignment Settings Option in the Alignment section under the Home.

Lock-Column-Cells-In-Excel-15

  • In the Format Cells dialog box, select the Protection Check the Locked tick box to lock the chosen column.

This has locked the chosen column. If you try, you will notice that you can still edit the cells in the locked column. In order for the locking to work, the sheet needs to be protected.

Step 3 – Protecting the Worksheet

To protect the sheet:

  • Under the Review tab, in the Protect section, click the Protect Sheet This will launch the Protect Sheet dialog box.

Protect-Sheet-dialog-box-09

  • Add a password if you wish (optional). Check if the Protect worksheet and contents of locked cells tick box is automatically checked. Leave it checked. Under Allow all users of this worksheet to: select how much control you would like to provide the users by checking the tick boxes against the relevant options and click OK.
  • Reconfirm the password if any is used.

In this way, the entire column cannot be edited and gets locked.

How to Lock an Entire Workbook

Now that you know how to protect sheets, you may have noticed a button right next to Protect Sheet which is for protecting the whole workbook. Protecting a workbook doesn't allow users to make changes to the structure of the workbook. For example – users will not be able to add, move, rename or delete sheets in a workbook. However, they can edit and delete the cells unless the cells are locked and worksheet protection is turned on.

To lock a workbook follow these steps:

  • Under the Review tab, in the Protect section, click the button Protect Workbook.

Protect-WorkBook-In-Excel-16

  • This will launch the Protect Structure and Windows dialog. On this dialog box make sure the Structure tick box is checked. Adding the password is optional. Add password in the Password box if you wish.

Protect-WorkBook-Structure-In-Excel-17

  • Once done, click OK. It will prompt you to re-enter the password if you have added one, re-enter the password and we are done.

Your entire workbook is now protected. Workbook protection can be verified in three ways:

  • The highlighted Protect Workbook button as shown below.

Highlighted-Protect-WorkBook-Button-In-Excel-18

  • The inaccessibility of many options (like insert, delete, rename, etc) on the sheet tab right-click menu as shown below.

Disabled-Options-On-Sheet-Right-Click-Menu-19

  • The Protect Workbook confirmation on the info page of the file. To check this, navigate to the File tab, then the Info tab to view this.

Sheet-Protection-Status-On-Info-Tab-20

How To Unlock Specific Cells Ranges in a Worksheet

If you wish to lock specific cells in a sheet, it's equally possible to want to unlock specific cells in a protected sheet for users to be able to change just a certain range of cells. We will use the Allow Edit Ranges feature to unlock some chosen range of cells. If you find yourself unable to use the feature, unprotect the sheet to make it available.

Without a password, Allow Edit Ranges feature works similar to the selective locking procedure that we have seen above (Unlock all cells, Lock specific cells and then Protect the worksheet). But the real difference can be seen when we use a range editing password.

With a range editing password, the cell ranges that we mark editable only become editable after the password is entered. It should be noted that the range editing password can be (should be) very different from the sheet protection password.

The benefit of this can be clearly seen with an example –

Suppose you have a spreadsheet that needs to be shared with multiple teams in your project. Now, you want that only one team should be able to edit a preset range of cells from the sheet. While all the other teams should just be able to use the spreadsheet in protected form. So, in this case, you can easily protect the worksheet allowing the specific range to be editable with a password using this feature. And share the password only with the team that needs to make the changes.

In this way, anyone who has the range editing password would be able to edit the editable range in the worksheet. While for others the entire worksheet will be locked. So, this just adds a layer of protection over the sheet protection feature of Excel.

Now let's come back to our example and understand how to make use of this feature. The cells we would want to keep unlocked are D3:D7 to be able to edit the quantities. Follow along –

  • Select the cells you would want to keep unlocked.

Range-To-Be-Unlocked-22

  • Next, in the Review tab, in the Protect section, click Allow Edit Ranges option. The Allow Users to Edit Ranges dialog box will appear.

All-Users-To-Edit-Ranges-23

  • Click New. In the New Range dialog box, Excel has already provided the title (changing it is optional) and the selected cell references (the range that we want to keep unlocked). Add the range editing password and click OK.

Allow-Users-To-Edit-Ranges-New-Range-24

  • Reenter the password to confirm it. Click OK.

Allow-Users-To-Edit-Ranges-New-Range-25

  • After the range editing password is set, click the Protect Sheet button and protect the sheet with or without a password. And we are done

Trying to change any locked cell on the worksheet will not work and a window hindering the attempted changes will pop up.

Protect-Sheet-Popup-Message-04

Upon trying to edit any cells from D3:D7, you will receive a prompt demanding the password to unlock the cells. Type the password to unlock the cells.

Unlock-Range-Password-Prompt-26

How to Unprotect Sheets in Excel

Let's talk about unprotecting worksheets. To talk about this feature let's assume we are working on a sheet that is protected and all the cells are locked for editing.

Follow along  to unprotect and unlock the cells:

  • The only thing that we need to do is – to Unprotect the sheet. To do this navigate to the Review tab and click the Unprotect Sheet button. The Unprotect Sheet button will only be available on the ribbon if the sheet is already protected.

UnProtect-Sheet-Button-On-Ribbon-21

  • If the sheet was locked without a password it would get unlocked right away. However, Excel will ask you to enter the password if the sheet was locked with a password.
  • After the sheet protection is turned off all the cells on the sheet should become editable.

How to Find/Format Locked and Unlocked Cells

After knocking into locked cells over and over while trying to edit them and being unable, we need to get this straight. Let's find out on the face of it, which cells are unlocked and which aren't. If you're feeling a tad impatient, a speedy way to look locked and unlocked cells up is through the CELL function which will display locked cells as 1 (1 means TRUE) and unlocked as 0 (0 means FALSE).

See the formula:

=CELL("protect",C3)

C3 refers to the cell you want to check for its locked or unlocked status. In our sheet, we have locked the cells E3:E7. Here is what our sheet looks like after applying the CELL function:

CELL-Function-To-Check-IF-A-Cell-Is-Locked-Or-Not-27

For a small sheet, this is quite alright but we assure you there's a better way than having your eyes sift through 1's and 0's. We will still use the CELL function but we will use it with the Conditional Formatting feature. Here we have the steps:

  • Select all the cells on the worksheet.
  • Go to Home > Styles section > Conditional Formatting button > New Rule.

Locked-Cells-Conditonal-Formatting-28

  • In the Formatting Rule dialog box. Select Use a formula to determine which cells to format.

Locked-Cells-Conditonal-Formatting-29

  • Enter the following formula and set the format you want for the locked cells by clicking on the Format (We will select gray color fill).
=CELL("protect", A1) = 1
  • Click OK.

Locked-Cells-Conditonal-Formatting-30

Our locked cells E3:E7 have been conditionally formatted and we can easily identify them based on the background fill.

Prevent Locked Cells from Being Selected

Instead of the users trying to edit a locked cell, receiving the locked cell prompt, clicking OK, and then moving onto what they should do, you can save their time (and yours) by preventing locked cells from being selected in the first place.

You select a cell by clicking on it with the mouse or by navigating to it with the keyboard. All you need to do to prevent locked cells from being selected is unchecking the Select locked cells tick box in the sheet protection dialog box.

  • In the Review tab, from the Protection section, click the Protect Sheet button.
  • Uncheck the Select locked cells tick box from the Protect Sheet dialog box as shown.

Prevent-Locked-Cells-From-being-selected-31

  • Click OK.

Now the locked cells cannot be selected by any means. If you try to click a locked cell, no selection on the cell will be made and the selection will remain on any previously selected cell. If you try to navigate to it, the locked cell will be skipped and the nearest editable cell will be selected.

Hide Formulas In Locked Cells

The formula in a cell can be viewed in the Formula bar when that cell is selected, no matter if the cell is locked or unlocked. If you're feeling secretive and do not want one or more formulas to show in locked cells, we can achieve it in a couple of ways.

You can either prevent the selection of the locked cells with the formulas (mentioned above). If the cell won't be selected, you can't view the formula. Or we can hide the formulas. This can be done to already locked cells or the cells can be locked and the formulas can be hidden together. You only have to check the Hidden tick box while locking cells. The steps are as follows:

Step 1 – Mark the Cells Locked and Hidden

  • Select the cells containing the formulas you want to hide and also lock. In our case, we are going to lock and hide the formulas of E3:E7.
  • After selecting the desired cells, we will launch the Format Cells dialog box to lock the chosen column. Click the Alignment Settings Option in the Alignment section under the Home.

Hide-Fromula-From-Locked-Cells-Excel-32

  • In the Format Cells dialog box, make sure to check both the tick boxes; Locked and Hidden.

Step 2: Protecting the Worksheet

To protect the sheet:

  • Under the Review tab, in the Protect section, click the Protect Sheet This will launch the Protect Sheet dialog box.

Protect-Sheet-dialog-box-09

  • Add a password if you wish (optional). Check if the Protect worksheet and contents of locked cells tick box is automatically checked. Leave it checked. Under Allow all users of this worksheet to: select how much control you would like to provide the users by checking the tick boxes against the relevant options and click OK.
  • Reconfirm the password if any is used.

Hide-Fromula-From-Locked-Cells-Excel-33

And we are done! If you select the locked cells now, you can't see the formula in the formula bar. The selection of these cells doesn't display anything in the formula bar, not even the value itself.

Recommended Reading: Lock & Hide Formulas in Excel – 2 Easy Ways

And that's that! We hope to have given you some command over cell locking and protection. While you and the sheets are sworn to secrecy, we'll prepare some more Excel-ling for you; "locked" and loaded.

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.