Spreadsheets

Protect formulas in your expense sheet from accidental edits

Lock the formula cells in your expense sheet and leave the input cells open, in Excel or Google Sheets, so family edits cannot overwrite your totals.

Editorial illustration for Protect formulas in your expense sheet from accidental edits

To protect formulas in an expense sheet, mark only the cells people should type in as unlocked, then switch on sheet protection. In Excel that means Format Cells, Protection, then Review, Protect Sheet. In Google Sheets you use Data, Protect sheets and ranges. Both stop accidental overwriting; neither stops a determined person.

What does protection actually do?

It prevents mistakes, not misuse. A spouse typing over a total, a child pasting into the wrong row, a drag-fill that runs past the table: these are the real risks in a shared household sheet, and protection handles them well. It is not security. Anyone with enough access to the file can change settings or copy the contents, so keep the sheet away from sensitive account details altogether.

How do I protect formulas in Excel?

  1. Select the entire sheet with Ctrl+A, press Ctrl+1, open the Protection tab and untick Locked. This frees every cell.
  2. Now select only the cells people may type in, such as Date, Description, Amount and Category in the log, plus the Budget column. Press Ctrl+1 and tick Locked again for the cells you do not want touched. A shortcut: Home, Find and Select, Go To Special, Formulas selects every formula cell at once, and you can lock those.
  3. Open Review, Protect Sheet. Leave Select locked cells and Select unlocked cells ticked so people can still click around.
  4. Add a password only if you will record it somewhere safe. If you do, write it down; a lost one is a nuisance to recover. You can also leave it blank, which still blocks accidental edits.
  5. Click OK, then try typing over a total. You should get a message that the cell is protected.

And in Google Sheets?

  1. Open Data, Protect sheets and ranges, then Add a sheet or range.
  2. Choose Sheet, tick Except certain cells, and add the input ranges, for example Log!A2:D500 and Budget!B2:B20.
  3. Click Set permissions. Choose Restrict who can edit this range and pick Only you, or add particular people.
  4. For a softer option choose Show a warning when editing this range. Anyone can still edit, but they see a prompt first.

In Sheets, someone with only Viewer or Commenter access cannot edit anyway. Protection matters for people you have made Editors.

What about people who need to add a new row?

This is where protection annoys people. If your log is a table that grows, leave a generous block of unlocked rows, say rows 2 to 1000, rather than only the rows in use today. In Excel, also allow Insert rows in the Protect Sheet options if you want rows added, though this can behave oddly with tables. If it is more trouble than it is worth, protect only the Summary tab and leave the Log open.

Two more surprises are worth knowing. On a protected Excel sheet, sorting and filtering stop working unless you tick Use AutoFilter and Sort in the Protect Sheet window, and sorting a locked range does not work at all. In Google Sheets, people can still create their own filter views without changing your data, which is a gentler way for family members to explore the log.

What should I protect, in order of importance?

AreaWhySuggested setting
Summary and total cellsThey hold your formulasLocked
Settings tabOne wrong rate changes everythingLocked, edited only by you
Category listEdits break dropdownsLocked or warning only
Expense log input cellsPeople need to type hereUnlocked

A short checklist to finish

  • Keep an untouched backup copy before you switch protection on.
  • Note in a cell on the Settings tab which cells are meant to be typed in.
  • Colour input cells lightly so everyone sees where to type.
  • Remember that if the whole thing turns into a chore, an app that reads statements directly, such as SpendixAI, removes most hand typing and so most chances to damage a formula.
SPENDIXAI JOURNAL

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

Explore your reports