How to Lock Cells and Protect Worksheets in Excel

01
of 02

Lock/Unlock Cells in Excel

Lock / Unlock Cells and Protect Worksheet in Excel
Lock and Unlock Cells in Excel. © Ted French

Worksheet Protection Overview

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

Protecting data from 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 higher level of security and can be used to prevent users from opening a file altogether.

Step 1: Lock/Unlock Cells in Excel

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 these cells after the protect sheet/workbook option has been applied.

Cells can be unlocked using the Lock Cell option. This option works like a toggle switch - it has only two states or positions - ON or OFF. Since all cells are initially locked in the worksheet, clicking on the option unlocks 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.

Example: Unlock Cells in Excel

In the image above, protection has been applied to cells. The steps below related to the worksheet example in the image above.

In this example:

  • Cells I6 to J10 have been unlocked to allow data to be added or edited as necessary.
  • Cells I10 and J10, which contain formulas for calculating total revenues, have been left locked.
  • The protect sheet option has been activated for this worksheet.

Steps to locking / unlocking cells:

  1. Highlight cells I6 to J10 to select them.
  2. Click on the Home tab.
  3. Choose the Format option on the ribbon to open the drop down list.
  4. Click on Lock Cell option at the bottom of the list.
  5.  The highlighted cells I6 to J10 are now unlocked.

Unlock Charts, Textboxes and Graphics

By default all charts, textboxes, and graphic objects - such as pictures, clip art, shapes, and Smart Art- present in a worksheet are locked and, therefore, protected when the Protect Sheet option is applied.

To leave such 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 on the Format tab.
  3. In the Size group on the right hand side of the ribbon, click the dialog box launcher button (small downward pointing 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, remove the check mark from the Locked check box, and if active, from the Lock text check box.

02
of 02

Applying the Protect Sheet Option in Excel

Lock / Unlock Cells and Protect Worksheet in Excel
Protect Sheet Options in Excel. © Ted French

Step 2: Applying the Protect Sheet Option

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 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 on those cells that have been unlocked before the sheet was protected to be sorted. 

Example: Applying the Protect Sheet Option

  1. Unlock or lock the desired cells in the current worksheet
  2. Click on the Home tab
  3. Choose the Format option on the ribbon to open the drop down list
  4. Click on Protect Sheet option 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 on the Home tab
  2. Choose the Format option on the ribbon to open the drop down list
  3. Click on Unprotect Sheet option at the bottom of the list to unprotect the sheet.

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