Lock cells and protect data in Excel spreadsheets

To prevent accidental or intentional changes to the data in the cells of a worksheet or workbook, Excel has tools to lock certain worksheet elements that can be used with or without a password.

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

  1. Lock/unlock specific cells or objects such as charts or graphs on a worksheet.

  2. Apply sheet protection option; until step 2 is complete, all elements and sheet data are vulnerable to modification.

Sheet item protection should not be confused with book-level password protection, which provides a file-level security layer that prevents users from opening the file at all.

The instructions in this article apply to Excel 2016, 2013, 2010, and 2007.

  1. Lock or unlock cells
  2. Unlock cells in Excel
  3. Unlock charts, text fields and images
  4. Apply “Protect Sheet” option
  5. Apply the Protect Sheet option
  6. Disable sheet protection

Lock or unlock cells

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

To protect data on all sheets of a workbook, the sheet protection setting must be applied to each sheet individually. By unlocking specific cells, you can only make changes to those cells after you apply the Protect Sheet/Workbook option.

Individual cells can be locked or unlocked using the Lock Cell switch. This option works like a toggle, blocking or unblocking a cell. Since all cells are initially locked on the sheet, press lock cell unlocks all selected cells.

Some cells on the worksheet can remain unlocked so that you can add new data or modify existing data. Cells containing formulas or other sensitive data remain locked, so once the Protect Sheet/Workbook option is applied, these cells cannot be changed.

Unlock cells in Excel

Steps to lock or unlock cells:

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

  2. Go to the tab House

  3. Click Format in Group cells to open the drop-down list.

  4. Click lock cell bottom of the list.

  5. Selected cells from I6 to J10 are now unlocked.

Unlock charts, text fields and images

By default, all charts, text boxes, and graphical objects such as images, clip art, shapes, and smart art are locked on the sheet and therefore protected when the sheet security setting is applied.

To leave these objects unlocked so that they can be modified after the sheet is protected:

  1. Select the item you want to unlock.

  2. Open a tab Format

  3. In the Size group on the right side of the ribbon, click the dialog box launcher (small arrow down ) 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 taskbar, uncheck the box Locked and, if it is active, uncheck Block text

Apply “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 number of options that determine which plate members can be modified. These elements include:

  • Add a password to control who can disable sheet protection.

  • Choice of locked and unlocked cells.

  • Edit, move or delete data.

  • Add/remove rows and columns.

  • Hide or show columns and rows.

  • Change the format of numbers, such as currency symbols, percent signs, number of decimal places, etc.

  • Change cell formatting, such as changing cell color or font, changing text wrapping, adding or removing borders, and more.

Remark † Adding a password here prevents users from opening the sheet and viewing its contents.

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

Other options, such as cell formatting and data sorting, don’t all work the same way. For example, if the cell formatting option is disabled when the worksheet is protected, all cells can be formatted.

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

Apply the Protect Sheet option

  1. Unlock or lock the desired cells on the sheet.

  2. Click on the tab House

  3. Click Format on the ribbon to open the drop-down list.

  4. Click protective sheet at the bottom of the list to open the Protective Sheet dialog box.

  5. Check or uncheck the desired options.

  6. Click Okay to close the dialog box and protect the sheet.

Disable sheet protection

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

  1. Go to the tab House

  2. Click Format on the ribbon to open the drop-down list.

  3. Click Remove leaf protection at the bottom of the list to unprotect the sheet.

Remark † Unprotecting a sheet does not affect the state of locked or unlocked cells.

Leave a Reply

Your email address will not be published. Required fields are marked *