Spreadsheets

Lock cells and protect formulas in an expense workbook

Lock the formula cells in your expense workbook, keep input cells editable, choose sorting and filter options, and learn why protection is not encryption.

Editorial illustration for Lock cells and protect formulas in an expense workbook

To protect formulas in Excel, unlock the cells where you type, then switch on Review, Protect Sheet. Every cell is "locked" by default, but the lock does nothing until the sheet is protected. So the job is really the reverse of what it sounds like: you mark the cells people may edit, then protect everything else.

How do I lock only the formula cells?

Say your expense sheet has dates in column A, categories in column B and amounts in column C, and a summary block in column E with SUMIFS formulas. You want columns A to C to stay editable and everything in E to be safe from a stray keystroke.

  1. Select the whole sheet with Ctrl+A, press Ctrl+1 to open Format Cells, go to the Protection tab and untick Locked. Now nothing is locked.
  2. Select the cells that hold formulas. A fast way: press F5, choose Special, tick Formulas and press OK. Excel highlights every formula cell in one go.
  3. Press Ctrl+1 again and tick Locked on these cells. Optionally tick Hidden as well, which stops the formula showing in the formula bar.
  4. Open the Review tab and choose Protect Sheet. Leave the password box empty for now, pick the options below and press OK.

Test it. Click a formula cell and try typing: Excel should refuse. Click an amount cell and type normally.

Which protection options should I tick?

The Protect Sheet box has a list of things you are allowing people to do even while the sheet is protected. For an expense workbook that you use daily, a sensible starting point is:

OptionTick it?Why
Select locked cellsYesYou still want to click a formula and read it
Select unlocked cellsYesNeeded to type in the input area
Insert rowsYesNew month, new rows; the summary ranges should stretch
SortOnly with careSorting works only if every cell in the sorted range is unlocked
Use AutoFilterYesSwitch the filter arrows on before you protect, not after
Format cellsNoStops colours and number formats drifting

Two traps catch most people. If the data range contains even one locked formula cell, Sort will fail with an error. And AutoFilter arrows must already exist when you protect the sheet; otherwise the option is ticked but nothing happens.

Can I let some people edit only certain ranges?

Yes. Before protecting, go to Review, Allow Edit Ranges, press New, and select the range such as $A$2:$C$500. You can give the range a title and, if you wish, its own password. This is handy on a shared family workbook: one range for you, another range on a separate tab for a spouse. The ranges only take effect once the sheet itself is protected.

In Google Sheets the equivalent is Data, Protect sheets and ranges. You choose a range or a whole sheet, then decide who may edit it, or show a warning only.

Do I need a password at all?

Not for accident-proofing. If your aim is to stop yourself overwriting =SUMIFS($C$2:$C$500,$B$2:$B$500,"Food") with a typed number, a blank-password protection is enough, and you can switch it off in two clicks from Review, Unprotect Sheet. A password adds friction, and a forgotten one means a painful recovery, so keep one in a place you trust if you use it.

What should I check after protecting the sheet?

  • Type a new expense in the next empty row: does the total update?
  • Add a new category in your list: is the dropdown or lookup range still stretching?
  • Open the file on your phone or in Excel for the web, and see whether the behaviour is the same. Some editing features differ, so check before relying on it.
  • Keep an unprotected copy named with the date, in case a protected layout needs a big change later.

When protecting formulas becomes a monthly chore of unlocking, fixing and relocking, that is a sign the sheet is doing a tracker's job. SpendixAI can import your statements and categorise them, so the formulas you were guarding are no longer something you maintain yourself.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports