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

You might be surprised to learn that all cells are locked when you create a new Excel worksheet.

If you only want to lock specific cells or protect an entire 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.

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.

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.

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.

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.