To protect formulas in Excel, you first unlock the cells where you type, then protect the sheet so every other cell stays locked. By default every cell in a worksheet is already marked Locked, but that setting does nothing until the sheet is protected. So the order is: free up the entry cells, then protect.
This matters in an expense workbook because the damage is quiet. One stray keystroke over a SUMIFS total and your monthly figure is wrong for weeks before you notice.
Which cells should stay open for typing?
Decide this before touching any menu. In a typical expense sheet the cells you type into are the date, merchant, category, amount and a note. Everything else is calculated: month helper columns, category totals, balance, the summary block. Say your data runs from A2 to E500 and your formulas live in column F and on a Summary tab. The plan is to unlock A2:E500 and leave F and Summary locked.
How do I unlock the entry columns and lock the formulas?
- Select the entry range, for example A2:E500.
- Press Ctrl+1 to open Format Cells and go to the Protection tab.
- Untick Locked and press OK. Those cells will now stay editable after protection.
- Check the formula cells the other way round: select column F, press Ctrl+1 and confirm Locked is ticked. If you want formulas hidden in the formula bar as well, tick Hidden here too.
- Go to Review, then Protect Sheet.
If you would rather lock by rule than by memory, press Ctrl+G, choose Special, then Formulas. Excel selects every cell holding a formula in one go, and you can tick Locked for exactly those.
What should I tick in the Protect Sheet box?
The dialog lists what users may still do on a protected sheet. Two ticks are on already: select locked cells and select unlocked cells. For an expense workbook, add these:
- Use AutoFilter, so you can narrow to one category or month.
- Use Sort, so you can order rows by date or amount.
- Format columns, if you like adjusting widths.
Leave Insert rows and Delete rows unticked unless you really need them. A password is optional. If you set one, write it down somewhere sensible, because Excel will not recover it for you.
Why does my table stop growing after protection?
If your data is an Excel Table, it cannot expand to a new row on a protected sheet. There are two practical routes. Either keep a long unlocked range (say 500 rows, with formulas already filled down and blank-safe like =IF(D2="","",TEXT(A2,"mmm-yy"))), or protect only the Summary tab and leave the data tab unprotected, since the data tab holds no totals worth guarding.
Is sheet protection real security?
No. It is a seat belt against accidents, not a lock against people. Anyone determined can strip it with freely available tools, so do not rely on it to hide private figures. For that, control who gets the file at all, and keep your own backup copy before sharing it.
| Cell type | Locked? | Example |
|---|---|---|
| Typed entries | No | Date, merchant, category, amount |
| Row-level formulas | Yes | Month helper in column F |
| Summary totals | Yes | =SUMIFS on the Summary tab |
| Inputs such as budget limits | No | Monthly cap for Food |
Checklist before you protect
- Type something in every unlocked column to confirm it accepts input.
- Try to overtype a total. Excel should refuse with a warning.
- Test the filter and a sort on a copy first.
- Save a backup, then protect and save again.
When the sheet gets heavy with monthly statements to key in, SpendixAI is one alternative: you upload the statement, review the rows and keep the spreadsheet for special reports only.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


