A spreadsheet that was right yesterday and wrong today has usually been sorted, copied or tidied in a way that moved the cells its formulas rely on. Anchor the references that must not move, keep the data in one plain block, and avoid merged cells in it. This is the shortest route to a sheet you can trust.
What does anchoring actually do?
A reference such as C2 is relative. Copy the formula one row down and it becomes C3. A reference written $C$2 is absolute and stays fixed. Say your budget cap is in cell F1 and you want each category's spend as a percentage. Writing =D2/F1 works in the first row, but copied down it becomes D3/F2, then D4/F3, and the percentages are nonsense. Writing =D2/$F$1 keeps the cap fixed.
| You write | Copied one row down | Use when |
|---|---|---|
| =D2/F1 | =D3/F2 | Almost never for a fixed cap |
| =D2/$F$1 | =D3/$F$1 | The divisor is a single cell |
| =$D2/F$1 | =$D3/G$1 | Mixed: lock the column, free the row |
Why do merged cells cause trouble?
Merged cells look neat for headings but break sorting, filtering and copying, because the merged area counts as one cell in some operations and several in others. Keep merged cells out of the data block. If a heading needs to look centred, use Center Across Selection from the alignment options, which does not merge.
Worked example: a sort that scrambled a total
Say column D holds a running balance formula =D2+C3, starting from an opening balance of C$1,200. You sort the rows by category and the running balance now follows the new order, so the figures no longer match the bank. The fix is to avoid running balances in data you sort. Use a SUM of the whole amount column for the total, and keep a separate sorted copy for reading. With rows of -45.00, -120.00 and 500.00, the net is -45 - 120 + 500 = 335.00 whichever order they sit in.
- Sort a copy, not your master data
- Do not sort part of a block; select the whole block first
- Keep the header row frozen
- Use SUM and SUMIFS, which do not care about row order
What does a safe layout look like?
- One tab named Data with plain headers: date, description, amount, category.
- One tab named Report with the formulas.
- No merged cells, blank rows or subtotals inside the data tab.
- Formulas such as =SUMIFS(Data!$C$2:$C$500,Data!$D$2:$D$500,A2) pointing at whole data ranges.
- A check cell that compares the report total with =SUM(Data!$C$2:$C$500).
When to stop patching
If you keep fixing the same breakages, the sheet may not suit your volume. SpendixAI is built around India and INR, so it does not serve Canadian dollar records, but its reports come from the stored transactions rather than from formulas you maintain.
How do you test a sheet after changing it?
Keep a small test block of three or four rows with a known answer, such as 45.00, 120.00 and 500.00 in the same category. After any change, look at the report line for that category and confirm it still reads 665.00. A known-answer test takes ten seconds and tells you immediately if a sort, a copy or a deleted row has moved something. Delete the test rows before sharing the report, or move them to a hidden tab that you keep for exactly this purpose.
Checklist
- Absolute references where needed
- No merged cells in data
- Data and report on separate tabs
- Check cell reads zero
- Backup before every big change
Useful ideas start with a clearer picture of your own spending.
Explore your reports


