Categories
Excel

How to Lock Cells in Excel to Protect Data and Formulas

Some Excel files contain data and formulas that you don’t want people to change.

Maybe it’s a template for recording mileage expenses or it could be a worksheet for capturing sales data and calculating commissions.

You can lock cells in Excel to prevent data and formulas from being changed.

All Cells Start Out Locked

Participants in Microsoft Excel training are always surprised to learn that all cells are locked when you create a new Excel worksheet.

If you want to lock cells in Excel and protect a worksheet, there are two ways to do it.

  1. You can unlock every cell in your spreadsheet then lock specific cells – usually the ones with values or formulas that shouldn’t be changed.
  2. You can leave the locked cells alone and unlock the cells that users need to change.

Either way, to complete the process you need to protect the sheet containing the locked cells when you’re done.

An example may be helpful.

How to Lock Cells in Excel and Enable Worksheet Protection

Look at this spreadsheet.

Food and drink sales
Worksheet for tracking food and drink sales

I don’t want anyone to change the product categories in column A or the formulas for calculating totals in row 6.

I do want people to input sales figures in B2:E5.

Unlock All Cells Using the Format Cells Dialog Box

First, we’re going to unlock all cells.

  1. Click the Select All button or press Ctrl + A to select every cell in the current worksheet.
  2. Right-click any selected cell and select Format Cells
  3. In the Format Cells dialog box, select the Protection tab then click to clear the checkbox beside Locked.
  4. Click OK.
Format Cells dialog box
The Protection tab in the Format Cells dialog box

Every cell in the worksheet is unlocked.

Lock the Specific Cells That Shouldn’t Be Changed

Next, we want to lock cells in column A and row 6.

  1. Drag to select cells in the range A2:A5 then hold the Ctrl button as you drag to select B6:E6.
    Both ranges are selected.
  2. Right-click any selected cell and select Format Cells
  3. In the Format Cells dialog box, select the Protection tab then click to select the checkbox beside Locked.
  4. Click OK.
Two selected ranges
Two ranges selected at the same time

All cells containing category labels and formulas are now locked.

Protect the Worksheet

To protect the worksheet, click the Protect Sheet button in the Review > Protect group. Enter a password when prompted to provide an additional layer of security – but a password isn’t required.

Protect Sheet button
The Protect Sheet button

Once you protect the worksheet, no one will be able to change the category labels in column A or the formulas in row 6.

What’s Next?

I hope you enjoyed learning how to lock cells in Excel. If you are interested in learning about other topics related to security in Microsoft Excel, you might want to check out Removing Protection from an Excel Workbook.

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.