How to Lock Cells in Excel Worksheets and Protect Data

Don't lose your valuable data in Excel

A drawng of a lock on a basic Excel worksheet.

To prevent accidental or deliberate changes to data contained in cells of a worksheet or workbook, Excel has tools for locking certain worksheet elements with or without a password.

Protecting data from a change in Excel worksheet is a two-step process.

  • Locking or unlocking specific cells or objects, such as charts or graphics, in a worksheet.
  • Applying the protect sheet option; all worksheet elements and data are vulnerable to change until you do this.

Protecting worksheet elements should not be confused with workbook-level password security, which prevents users from opening a file at all.

Instructions in this article apply to Excel 2019, 2016, 2013, 2010, and 2007.

Locking and Unlocking Cells

By default, all cells in an Excel worksheet are locked, which makes it easy to secure all data and formatting in a single sheet by applying the protect sheet option.

Excel's Format drop-down menu.

To protect the data in all sheets in a workbook, you must apply the protect sheet option individually. Unlocking specific cells permits changes to be made to only these cells after you've applied the protect sheet/workbook option.

Individual cells can be locked or unlocked using the Lock Cell toggle. This option works like a switch, either locking or unlocking a cell. Since all cells are initially locked in the worksheet, clicking Lock Cell will unlock all selected cells.

Individual cells in a worksheet may be left unlocked so that users can add new content or modify existing data. Cells containing formulas or other vital data are kept locked so that once you've applied the protect sheet/workbook option, no one can change these cells.

  1. As an example in an Excel worksheet, select cells I6 to J10.

  2. Click the Home tab.

  3. Click Format in the Cells group to open the drop-down list.

  4. Click Lock Cell at the bottom of the list.

  5. The highlighted cells I6 to J10 are now unlocked.

Unlock Charts, Text Boxes, and Graphics

By default, all charts, text boxes, and graphics objects such as pictures, clip art, shapes, and Smart Art, in a worksheet are locked and, therefore, protected when the protect sheet option is applied.

To leave these objects unlocked so that users can edit them once you've protected the sheet:

  1. Select the object you want to unlock.

  2. Click the Format tab.

    Excel's Format tab for illustrations and other art.

    IMAGE CALLOUT: TINY ARROW ON THE BOTTOM RIGHT OF THE HEIGHT/WIDTH BOX

  3. In the Size group on the right-hand side of the ribbon, click the dialog box launcher button (small down arrow on the bottom right) next to the word Size to open the formatting task pane (Format Picture dialog box in Excel 2010 and 2007)

    Excel's format shape task pane.

    IMAGE CALLOUT: LOCKED CHECKBOX ON BOTTOM RIGHT

  4. In the Properties section of the task pane, uncheck the Locked check box, and if active uncheck the Lock text check box.

Applying the Protect Sheet Option

The second step in the process, protecting the entire worksheet, uses the protect sheet dialog box, which contains a series of options that determine what elements users can change in a spreadsheet.

Protect sheet dialog box in Excel with password.

These elements include:

Adding a password here does not prevent users from opening the worksheet and viewing the contents.

If the options that allow a user to highlight locked and unlocked cells are turned off, users will not be able to make any changes to a worksheet, even if it contains unlocked cells.

The remaining options, such as formatting cells and sorting data, do not all work the same. For instance, if the format cells option is checked off when you protect a sheet, all cells can be formatted.

The sort option, on the other hand, allows sorts those cells that were unlocked before you protected the sheet. 

  1. Unlock or lock the desired cells in the worksheet.

  2. Click on the Home tab.

  3. Click Format on the ribbon to open the dropdown list.

  4. Click Protect Sheet at the bottom of the list to open the protect sheet dialog box.

    Setting a password to protect an Excel spreadsheet.
  5. Check or uncheck the desired options.

  6. Click OK to close the dialog box and protect the worksheet.

Turning Off Worksheet Protection

It's easy to unprotect a worksheet so that you can modify all cells.

  1. Click the Home tab.

  2. Click Format on the ribbon to open the dropdown list.

    Excel's Format drop-down menu with Unprotect Sheet selected.
  3. Click Unprotect Sheet at the bottom of the list to unprotect the sheet.

Unprotecting a worksheet doesn't change the state of locked or unlocked cells.

Was this page helpful?