How-To10 min read

How to Lock Cells in Excel: Complete Step-by-Step Guide

Locking cells prevents formulas from being accidentally overwritten, stops users from editing cells they should not touch, and preserves the structure of shared templates. Excel's cell locking system has one counterintuitive step that trips most people up — you must unlock all cells first before selectively locking the ones you want to protect.

How Excel Cell Locking Works

Excel's locking system has two separate components that must both be in place for a cell to actually be locked:

  1. The Locked property on the cell (Format Cells > Protection > Locked checkbox)
  2. Sheet protection enabled (Review > Protect Sheet)

Neither component alone is sufficient. A cell with Locked = TRUE is freely editable until the sheet is protected. A protected sheet with no locked cells lets everyone edit everything. Both must be configured for locking to work.

By default, every cell in a new Excel workbook has Locked = TRUE — but since new workbooks are not protected, none of the cells are actually locked. This is the cause of the most common mistake: people assume adding sheet protection alone locks all the cells. It does — but only because all cells start with Locked = TRUE by default. If you want to lock only some cells while leaving others editable, you must unlock the editable ones first before protecting the sheet.

Step-by-Step: Lock Specific Cells (Protect Some, Leave Others Editable)

1

Unlock all cells first (critical first step)

By default, every cell in a new Excel workbook has the Locked property set to TRUE — but this has no effect until the sheet is protected. Before selectively locking cells, press Ctrl+A to select all cells, then go to Format Cells (Ctrl+1) > Protection tab and uncheck Locked. Click OK. Now all cells are unlocked, giving you a clean slate to selectively lock only the cells you want to protect.

2

Select the cells you want to lock

Select the cells, ranges, or columns that should be protected from editing. These might be formula cells, header rows, lookup tables, or configuration values. You can select multiple disconnected ranges by holding Ctrl while clicking.

3

Mark the selected cells as Locked

With your target cells selected, right-click and choose Format Cells (or press Ctrl+1). Go to the Protection tab. Check the Locked checkbox. Click OK. The cells are now marked as locked, but protection still has no effect until you complete step 4.

4

Protect the sheet

Go to Review > Protect Sheet. In the dialog, configure which actions unprotected users can still take (select cells, sort, use AutoFilter, etc.). Optionally enter a password — leave blank for accidental-edit protection without a password requirement. Click OK. The locked cells are now protected.

5

Test the protection

Click a cell that should be locked and try typing. You should see a message saying the cell is protected. Click a cell that should remain editable and confirm you can type normally. Save the file.

Quick Method: Lock All Cells on the Sheet

If you want to lock every cell (no cells should be editable at all):

  1. Do not change any Locked properties — all cells are already marked Locked = TRUE by default.
  2. Go to Review > Protect Sheet.
  3. Optionally add a password. Click OK.

All cells are now locked. This is useful for distributing read-only reports or dashboards where the data should not be changed.

Lock Only Formula Cells (Most Common Pattern)

The most widely used approach in shared workbooks: lock formula cells so they cannot be accidentally overwritten, while leaving input cells freely editable.

  1. Press Ctrl + A to select all cells.
  2. Press Ctrl + 1 to open Format Cells > go to Protection tab.
  3. Uncheck Locked. Click OK. All cells are now unlocked.
  4. Go to Home > Find & Select > Go To Special.
  5. Choose Formulas and click OK. Excel selects all formula cells.
  6. Open Format Cells again (Ctrl + 1) > Protection tab > check Locked. Click OK.
  7. Go to Review > Protect Sheet. Set a password if needed. Click OK.

Now formula cells are locked, and all input cells (without formulas) are freely editable. Users can fill in data but cannot touch the formulas.

Allow Different Users to Edit Different Ranges

For more complex scenarios where different users or groups need to edit different ranges:

  1. Go to Review > Allow Edit Ranges.
  2. Click New to define a range that specific users can edit.
  3. Set the range address and optionally a range-specific password.
  4. Repeat for each different user group or range.
  5. After defining all editable ranges, protect the sheet via Review > Protect Sheet with the sheet password.

With this setup, each user or group can edit their designated ranges using their range password, while the rest of the sheet is protected by the sheet password.

What Permissions to Allow on a Protected Sheet

When you protect a sheet, the Protect Sheet dialog lets you specify what protected users can still do without unprotecting. Common settings:

  • Select locked cells: Let users click locked cells to read their values (usually leave this checked).
  • Select unlocked cells: Let users navigate to unlocked input cells (always leave this checked).
  • Sort: Check if you want users to be able to sort the data even though the sheet is protected.
  • Use AutoFilter: Check if users need to filter the data.
  • Insert rows: Uncheck if you do not want users adding rows.
  • Delete rows: Uncheck if you do not want users removing rows.

Password Considerations

Sheet protection in Excel is designed to prevent accidental edits, not to provide enterprise-grade security. A determined user with basic technical knowledge can remove Excel sheet protection without the password using freely available tools.

Use passwords for:

  • Preventing colleagues from accidentally modifying protected cells
  • Making it clear to non-technical users that a sheet is intentionally restricted
  • Adding a layer of friction so casual modification requires deliberate effort

Do not use Excel sheet protection as the primary security mechanism for sensitive financial data, personal information, or regulatory compliance. For genuine access control, use SharePoint or OneDrive file permissions at the platform level.

If you protect with a password: write it down in a secure location immediately. Excel sheet passwords cannot be recovered — if lost, only third-party tools (or recreating the workbook) can help.

Common Cell Locking Mistakes

  • Protecting the sheet without unlocking input cells first: Because all cells start Locked = TRUE, protecting without first unlocking input cells locks everything, including the cells you intended to be editable.
  • Forgetting to protect the sheet: The Locked property is meaningless without sheet protection. After checking Format Cells > Locked, you must also go to Review > Protect Sheet to activate the locking.
  • Locking cells on the wrong sheet: In workbooks with multiple sheets, confirm you are on the correct sheet before protecting. Protection is per-sheet — each sheet requires individual protection.
  • Expecting workbook protection to lock cells: Review > Protect Workbook protects the workbook structure (prevents adding, deleting, or renaming sheets), not the cells within sheets. These are separate protection levels.
  • Hidden sheets are not automatically protected: If a hidden sheet contains sensitive formulas or data, hide it AND protect it. A hidden but unprotected sheet can be unhidden by anyone via Format > Sheet > Unhide.

Checking What Is Locked Before Sharing

Before distributing a workbook, verify the protection is configured correctly:

  1. Try clicking and typing in cells that should be locked. Confirm you get the protection message.
  2. Try clicking and typing in cells that should be editable. Confirm they accept input.
  3. Try accessing Review > Unprotect Sheet and confirm a password is required (if you set one).
  4. On the Review tab, check that "Protect Sheet" shows as "Unprotect Sheet" — indicating the sheet is currently protected.

The Spreadsheet Auditor can also flag unprotected formula cells in a workbook — useful for identifying formulas that were accidentally left unprotected before sharing.

Frequently Asked Questions

How do I lock a cell in Excel using a keyboard shortcut?

There is no single keyboard shortcut to mark a cell as Locked. The fastest route is: select cells > Ctrl + 1 (Format Cells) > Tab to the Protection tab > Space to toggle the Locked checkbox > Enter to confirm. Protecting the sheet via Review > Protect Sheet does not have a standard shortcut, but you can access it via the Alt key ribbon navigation: Alt + R + P + S.

Can I lock cells in Excel Online?

Yes. Excel Online (via Microsoft 365 web) supports sheet protection. Go to Review > Protect Sheet. The functionality is similar to the desktop app, though some advanced options like Allow Edit Ranges may not be available in the web version.

How do I see which cells are locked on a sheet?

Go to Home > Find & Select > Go To Special. Unfortunately, Go To Special does not have a "Locked cells" option directly. To visually identify locked cells, use Conditional Formatting with a custom formula: apply formatting based on =CELL("protect",A1)=1, which returns 1 for locked cells and 0 for unlocked cells. This highlights all locked cells so you can review the pattern before protecting the sheet.

Check your workbook before sharing

The Spreadsheet Auditor identifies unprotected formula cells, formula errors, and inconsistencies before you distribute your file.

Audit My Spreadsheet →

Related Guides

→ Create a Drop-Down List in Excel→ Excel Best Practices→ Unhide Sheets in Excel→ Excel Password Remover