How to lock cells with formulas in Excel

How locking works (and why nothing seems to happen)

You built a quote calculator: hours and rate in, discount and total out. A coworker types over the Total cell with a number "just this once", and next month the totals are wrong. Locking the formulas stops that.

Here's the part that confuses everyone. Every cell in Excel is already Locked; you can see the ticked box in Format Cells > Protection. But Locked does nothing until the sheet is protected. So the job has two steps: decide which cells stay open (unlock them), then switch protection on.

The cells to protect are the formulas. In this sheet B5 to B7 calculate the subtotal, discount and total; with 12 hours at 65 and a 10% discount, the total in B7 is 702. The inputs B2 to B4 are what people should change.

Method 1: unlock the inputs, protect the rest

This is the fastest for a calculator or a form where only a few cells are inputs. Everything you don't unlock stays locked, so new formulas you add later are protected by default.

1. Select the input cells, here B2:B4. Hold Ctrl to pick cells that aren't next to each other.
2. Press Ctrl+1 (Cmd+1 on a Mac), open the Protection tab, untick Locked and click OK.
3. Optional: give the input cells a light fill color so people can see where to type.
4. Go to Review > Protect Sheet.
5. Leave "Select unlocked cells" ticked. Untick "Select locked cells" if you want people unable to even click the formulas.
6. Type a password if you want one, click OK, and confirm it.

Method 2: lock only the formula cells

On a big sheet with data scattered everywhere, it's easier to find the formulas than the inputs. Go To Special selects every cell that holds a formula in one step.

1. Press Ctrl+A to select the whole sheet, press Ctrl+1, untick Locked on the Protection tab and click OK. Now nothing is locked.
2. Home > Find & Select > Go To Special, choose Formulas (keep all four types ticked) and click OK. Only the formula cells are selected.
3. Press Ctrl+1 again, tick Locked, and click OK.
4. Review > Protect Sheet, choose the options and click OK.

Hide formulas and choose what people can still do

Locked stops edits, but anyone can still click B7 and read =B5-B6 in the formula bar. If you'd rather show only the result, tick Hidden next to Locked in Ctrl+1 > Protection for those cells before protecting the sheet. Like Locked, Hidden only works once the sheet is protected.

The Protect Sheet box has a list of things people may still do. Tick Format cells or Format columns if they need to widen columns, Insert rows or Delete rows for a growing list, and Use AutoFilter if they filter. Two catches: the filter arrows must already be on before you protect (people can use them but not add them), and Sort only works on ranges with no locked cells.

On a protected sheet, Tab jumps from one unlocked cell to the next, which makes a form fast to fill in.

Unprotect, and what protection doesn't do

To make changes, go to Review > Unprotect Sheet and enter the password. Keep the password somewhere safe: Excel can't show it to you, and Microsoft can't recover it.

Sheet protection is for preventing accidents, not for keeping secrets. Anyone can still see the values, copy the cells to another workbook, and the protection is not designed to stop a determined person. For confidential files, use File > Info > Protect Workbook > Encrypt with Password, which requires a password to open the file at all.

Review > Protect Workbook is different again: it locks the structure, so people can't add, delete, rename or move sheets. Use it together with Protect Sheet when a template must stay in one piece. If a formula in your template is the problem, not the protection, Cellwise Fix (cellwise.app/excel-formula-helper/) can check it against your cells.