Categories
Microsoft Excel

Protected Worksheets: How to Lock and Unlock Cells in Excel

Microsoft Excel’s protected worksheet feature that lets you lock down specific cells or the entire worksheet. This can be an effective way to keep people from changing formulas, headings, or other information in an Excel spreadsheet while still allowing them to enter data in certain cells.

Participants who attend our Microsoft Excel training report that being able to restrict data entry to specific cells is incredibly useful!

Here’s the thing. Every cell in a Microsoft Excel spreadsheet starts out locked. You’ll see what I mean shortly but here’s a high-level overview of the process, including how to unlock cells in Excel.

If you want to lock your worksheet down, first you have to unlock cells where people should perform data entry – then click protect sheet. Once your worksheet is protected, users will still be able to select cells in Excel but they won’t be able to change data or formulas in locked cells.

Let’s look at the process for setting up a protected worksheet.

How to Unlock Cells in Excel

Here’s a little irony for you. You don’t need to lock cells in Excel – they all start out locked. You need to select just the cells that users should modify and unlock them.

With the designated cells unlocked, users will be able to enter data and use the protected worksheet the same way they would any other worksheet. They just won’t be able to change values or formulas in locked cells!

Here is how to unlock cells in Excel:

  1. Select the cells you’d like to unlock.
  2. Right-click anywhere in the selected range and choose Format Cells… from the shortcut menu (or press Ctrl + Shift + F).
    The Format Cells dialog box appears.
  3. Select the Protection tab in the Format Cells dialog box then click to deselect the Locked checkbox.
  4. Click OK.
The Protection tab in the Format Cells dialog box

In the example (above), cells in the price column are selected and currently locked. This isn’t a surprise at all because all cells start out locked.

After you unlock cells where users will enter data, it’s time to protect your worksheet.

How to Protect an Excel Worksheet

Once you’ve unlocked cells where users will do data entry, you need to lock cells that shouldn’t be modified by enabling worksheet protection.

Here’s how!

  1. Click the Protect Sheet button in the Review > Protect group.
    The Protect Sheet dialog box appears.
  2. Enter a password in the Password to unprotect sheet: box.
  3. Select options in the Allow all users of this worksheet to: area. Click to select or deselect options, as desired.
  4. Click OK.
  5. Re-enter your password, if prompted then click OK again.
The Protect Sheet dialog box

In the example (above), I entered a password then clicked OK. With worksheet protection enabled, users will be unable to change data or formulas in locked cells unless you click unprotect sheet to disable worksheet protection.

How to Unprotect an Excel Worksheet

If you need to update a protected worksheet, you’ll need to unprotect it then make the required changes.

Here is how to unprotect a worksheet.

  1. Click the Unprotect Sheet button in the Review > Protect group.
  2. Enter your password, if prompted then click OK.

With worksheet protection turned off, you’ll be able to edit the entire Excel spreadsheet – even data in locked cells.

Best Practices for Working with Protected Worksheets

Now that you know how to unlock cells in Excel, enabling worksheet protection will be a breeze… but there are a few things you’ll want to keep in mind.

  • The checkbox to unlock cells (or lock them) is in the Format Cells dialog box. Ctrl + Shift + F is the shortcut to display Format Cells.
  • All cells start out locked. You’ll need to unlock cells that users should be able to change.
  • Don’t forget the password that you enter after clicking the Protect Sheet button. You might want to save a copy of the unprotected version of your file in addition to the protected worksheet.

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.