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.

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.

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.

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.

  • As an example in an Excel worksheet, select cells I6 to J10.
  • Click the Home tab.
  • Click Format in the Cells group to open the drop-down list.
  • Click Lock Cell at the bottom of the list.
  • 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.

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

Click the Home tab.

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

Click Lock Cell at the bottom of the list.

The highlighted cells I6 to J10 are now unlocked.

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

  • Select the object you want to unlock.
  • Click the Format tab.
  • IMAGE CALLOUT: TINY ARROW ON THE BOTTOM RIGHT OF THE HEIGHT/WIDTH BOX
  • 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)
  • IMAGE CALLOUT: LOCKED CHECKBOX ON BOTTOM RIGHT
  • 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.

Select the object you want to unlock.

Click the Format tab.

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

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)

IMAGE CALLOUT: LOCKED CHECKBOX ON BOTTOM RIGHT

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

These elements include:

  • Adding a password to control who can turn worksheet protection off.
  • Selecting locked and unlocked cells.
  • Editing, moving or deleting data.
  • Adding/deleting rows and columns.
  • Hiding or unhiding column and rows.
  • Changing number formatting, such as currency symbols, percent signs, number of decimal places, etc.
  • Changing cell formatting, such as changing cell or font colors, altering text wrapping, adding and removing borders, and others.

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.

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

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. 

  • Unlock or lock the desired cells in the worksheet.
  • Click on the Home tab.
  • Click Format on the ribbon to open the dropdown list.
  • Click Protect Sheet at the bottom of the list to open the protect sheet dialog box.
  • Check or uncheck the desired options.
  • 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.

Unlock or lock the desired cells in the worksheet.

Click on the Home tab.

Click Format on the ribbon to open the dropdown list.

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

Check or uncheck the desired options.

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

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

Click Format on the ribbon to open the dropdown list.

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.

Get the Latest Tech News Delivered Every Day