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?
- Select the entire sheet with Ctrl+A, press Ctrl+1, open the Protection tab and untick Locked. This frees every cell.
- 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.
- Open Review, Protect Sheet. Leave Select locked cells and Select unlocked cells ticked so people can still click around.
- 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.
- Click OK, then try typing over a total. You should get a message that the cell is protected.
And in Google Sheets?
- Open Data, Protect sheets and ranges, then Add a sheet or range.
- Choose Sheet, tick Except certain cells, and add the input ranges, for example Log!A2:D500 and Budget!B2:B20.
- Click Set permissions. Choose Restrict who can edit this range and pick Only you, or add particular people.
- 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?
| Area | Why | Suggested setting |
|---|---|---|
| Summary and total cells | They hold your formulas | Locked |
| Settings tab | One wrong rate changes everything | Locked, edited only by you |
| Category list | Edits break dropdowns | Locked or warning only |
| Expense log input cells | People need to type here | Unlocked |
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.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


