How to Lock Cells in Excel

How to Lock Cells in Excel

Lock formulas and headings so nobody can change them, while keeping input cells open for editing.

How cell locking works

In Excel every cell is locked by default, but the lock does nothing until you protect the sheet. So locking cells is a two step job: choose which cells stay unlocked, then turn on sheet protection.

Lock only some cells

  1. Select the whole sheet with Ctrl+A.
  2. Press Ctrl+1 to open Format Cells and go to the Protection tab.
  3. Untick Locked and click OK. All cells are now unlocked.
  4. Select the cells you want to lock, such as your formulas, and press Ctrl+1 again.
  5. Tick Locked and click OK.
  6. Go to Review > Protect Sheet, optionally enter a password and click OK.

Now only the cells you locked are protected. Everything else can still be edited.

Lock all cells

Skip the unlocking steps and go straight to Review > Protect Sheet. Every cell becomes read-only.

Lock cells that contain formulas

Press F5, choose Special, select Formulas and click OK. Excel selects every formula cell. Lock these cells, then protect the sheet.

Hide formulas

On the Protection tab also tick Hidden. After the sheet is protected, the formula bar stays empty for those cells.

Unlock a protected sheet

Go to Review > Unprotect Sheet and enter the password if one was set. Keep the password safe. Excel cannot recover it for you.

Frequently asked questions

Why can I still edit locked cells?

Locking only works after you protect the sheet. Go to Review > Protect Sheet.

How do I lock only formula cells?

Use Go To Special (F5 > Special > Formulas) to select them, lock them, then protect the sheet.

Does sheet protection stop someone from copying my data?

It stops edits to locked cells, but users can still copy visible values. It is not strong security.

What if I forget the sheet password?

Excel cannot recover it. Keep a copy of the password somewhere safe before you protect an important file.

Can I let users select locked cells but not edit them?

Yes. In the Protect Sheet dialog, tick Select locked cells and leave editing options unticked.

Need Excel work done for you?

Talk to us

More free tools: All free calculators · Excel automation services · Excel templates · Budget template