How to Lock Cells and Protect Data in Excel Worksheets

Excel Screenshot

Ted French

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

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

  1. Locking/unlocking specific cells or objects, such as charts or graphics, in a worksheet.
  2. Applying the protect sheet option; until step 2 is completed, all worksheet elements and data are vulnerable to change.

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

Lock or unlock cells

Lock / Unlock Cells and Protect Worksheet in Excel

Ted French

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

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

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.

Certain cells in a worksheet may be left unlocked so that new data can be added or existing data modified. Cells containing formulas or other important data are kept locked so that once the protect sheet/workbook option has been applied, these cells cannot be changed.

Unlocking cells in Excel

Steps for locking or unlocking cells:

  1. As an example in an Excel worksheet, select cells I6 to J10.
  2. Click the Home tab.
  3. Click Format on the ribbon to open the dropdown 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 they may be changed once the sheet is protected:

  1. Select the object to be unlocked; doing so adds the Format tab to the ribbon.
  2. Click the Format tab.
  3. In the Size group on the righthand side of the ribbon, click the dialog box launcher button (small down arrow) next to the word Size to open the formatting task pane (Format Picture dialog box in Excel 2010 and 2007)
  4. In the Properties section of the task pane, uncheck the Locked check box, and if active uncheck the Lock text check box.

Apply the protect sheet option

Lock / Unlock Cells and Protect Worksheet in Excel

Ted French

The second step in the process, protecting the entire worksheet, is applied using the protect sheet dialog box. The dialog box contains a series of options that determine what elements of a worksheet can be changed. These elements include:

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

If the two 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 a sheet is protected, all cells can be formatted.

The sort option, on the other hand, allows only those cells that have been unlocked before the sheet was protected to be sorted. 

Applying the protect sheet option

  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.
  5. Check or uncheck the desired options.
  6. Click OK to close the dialog box and protect the worksheet.

Turning off worksheet protection

To unprotect a worksheet so that all cells can be edited:

  1. Click the Home tab.
  2. Click Format on the ribbon to open the dropdown list.
  3. Click Unprotect Sheet at the bottom of the list to unprotect the sheet.

Note: Unprotecting a worksheet has no effect on the state of locked or unlocked cells.