For personal finances, one master transaction table with a financial-year column is usually the better default, and a yearly file is best kept as a frozen archive copy rather than your working file. The master makes year-on-year comparison a single formula. Separate workbooks make it a chore of opening files.
What are you really choosing between?
| Question | One master table | One workbook per year |
|---|---|---|
| Year-on-year comparison | One SUMIFS across years | Open both files, or link between them |
| File size and speed | Grows each year; manageable for a household | Each file stays small |
| Changing a category rule | Fix it once, applies to all years | Repeat in each file |
| Archiving | Save a dated copy at year end | The files are the archive |
| Risk of breaking history | A bad edit can affect everything | A bad edit stays in one year |
How big does a master table get?
Excel sheets hold over a million rows, and a household produces a few thousand rows a year, so size alone rarely forces a split. Slowness usually comes from heavy formulas over whole columns, repeated lookups and many volatile functions, not from the number of rows. If the workbook feels slow, fix those first: use ranges or a Table, and avoid recalculating helper columns that never change by pasting them as values.
Backups matter more as the master grows. Take a dated copy before any big change, and keep one copy somewhere other than the computer you work on.
How do you compare years in a master table?
Add a financial-year column. For an April to March year, in a Table called Txns enter this in a column named FY:
=IF(MONTH([@Date])>=4,YEAR([@Date]),YEAR([@Date])-1)
It returns 2025 for dates from April 2025 to March 2026, so the number is the year in which the financial year starts. Then a comparison is:
- Food in the 2025 year: =SUMIFS(Txns[Debit],Txns[Category],"Food",Txns[FY],2025)
- Food in the 2024 year: the same formula with 2024.
- Change between years: one cell dividing the two, wrapped in IFERROR in case the earlier year was zero.
With separate workbooks, the same comparison needs both files open, or external links that break when a file moves.
When do yearly files make sense?
When a year is truly finished and you want it frozen. Tax or accounts work, handing a year to a CA, or keeping a record you must not alter are good reasons. Save a copy named with the year, for example expenses-FY2025-26-final, paste the data as values, and keep it read-only. Keep working in the master.
A reasonable rule: when a CA or anyone else asks for a particular year, give them a values-only copy of that year, not the live master with every other year inside it.
What about several accounts?
Keep all accounts in the same master with an Account column, instead of one master per bank. Filtering by account takes a second, while reconciling three separate workbooks takes an evening. Each account's balance chain can then be tested from the same table.
What should you decide today?
- Choose a single column layout: Date, Narration, Debit, Credit, Account, Category, FY.
- Keep category names in one list, so spellings never drift between years.
- Take a backup copy before any big edit, and a dated archive at the end of each financial year.
A spreadsheet is a fine master for a while. When maintaining rules, imports and reports starts to feel like a job, SpendixAI keeps transactions from your statements and manual entries in one personal workspace, with a free plan for statement import and a Pro plan with budgets, bills and custom reports.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


