How to Lock Cells and Protect Data in Excel Worksheets

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 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.

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.

  • Notebook on top of a printed spreadsheet/ledger.

    Here Are Easy Instructions on How to Create a Drop-Down List in Excel

  • Close-Up Of Protractor Over White Background

    Learn How to Convert Angles from Degrees to Radians in Excel

  • Businessman on Computer

    Great Tips for Entering Data into Excel

  • Colored file folders arranged alphabetically.

    Changing Tab Colors in Excel is a Breeze

  • A man using a laptop while talking with two women.

    How to Select Rows, Columns, or Worksheets in Excel

  • Young woman touching windowpane with graph in office.

    5 Steps to Making & Formatting a Line Graph in Excel

  • A person working with spreadsheet data

    Paste Links for Data, Charts, and Formulas in Excel, Word and PowerPoint

  • Close up of person making calculations.

    Multiply in Excel with the PRODUCT Function

  • A woman using a complicated spreadsheet on a laptop while seated at a desk.

    Use an Excel Data Form to Quickly Add Data to a Worksheet

  • formulas superimposed over laptop with hand on keyboard

    Can't See Formulas in Google Sheets? This Is How You Toggle Them

  • Close up of a spreadsheet, computer, and calculator.

    Finding Minimum Values in Excel is a Snap

  • Hide/Unhide Scroll Bars in Excel

    How to Hide Scroll Bars and Reset the Vertical Slider Range in Microsoft Excel

  • Math on a blackboard

    Learn How to Use the Formula Bar in Excel and Google Sheets

  • A hand pointing at avdata report shown on screen

    How to Utilize Column and Row Headings in Excel Spreadsheets

  • The Cell Styles Gallery in Excel

    Learn How to Create, Copy, and Modify Custom Cell Styles in Excel

  • Accountant working with graphs and modern touch screen computer

    23 Spreadsheet Shortcut Keys Every Excel User Should Know