Track Spending Spreadsheet for Multiple Accounts
Published
Key takeaways
- A track spending spreadsheet needs seven core columns: date, account, payee, category, amount, cleared flag, and notes.
- SUMIFS formulas total spending by any combination of account, category, and month across all your accounts.
- Log credit card payments as transfers on both tabs so money never counts as spending twice.
- A 20-minute monthly routine of entering, reconciling, recategorising, and rolling forward keeps the file trustworthy.
- Spreadsheets cannot automate bank import or reminders, which is where dedicated tools earn their keep.
A track spending spreadsheet works best with one transactions table, a fixed set of columns (date, account, payee, category, amount, balance), and SUMIFS formulas that total spending by category and account. This walkthrough gives you the exact columns and formulas to type, plus a monthly review routine, sized for years of history across current accounts, savings, ISAs, credit cards and a mortgage.
What columns does a spending tracker spreadsheet actually need?
The core seven columns
Start with: Date, Account, Payee, Category, Amount, Cleared, and Notes. Date, Account and Payee let you reconcile against statements the way you did in Microsoft Money. Cleared holds a simple Y or N so you know which transactions match the bank's version. Notes is where you record the detail that categories lose, like which child a payment was for.
Columns multi-account households should add
Add Recurring (Y/N) so your direct debits and standing orders are filterable, and Budget Month to pin a transaction to the month it belongs to rather than the date it cleared. A Type column (debit, credit, transfer) stops internal transfers between your current account and ISA from double-counting as spending. If you budget by envelope, a Budget Category column separate from the reporting category lets you split, say, groceries into weekly envelopes. The SUMIFS function is what makes these columns pay off, since it sums by any combination of account, category and month; the syntax is documented in Microsoft's own Excel function reference.
A worked example row
| Date | Account | Payee | Category | Amount | Cleared | Recurring | Type |
|---|---|---|---|---|---|---|---|
| 2024-03-14 | Current | British Gas | Utilities | -86.40 | Y | Y | debit |
Negative amounts for money leaving, positive for money arriving, keeps every formula simple. If you would rather skip the setup entirely, our spreadsheet alternative white paper covers where this structure breaks down.
Which formulas do the work in Excel or Google Sheets?
Four formulas carry almost the whole file. Everything else is formatting.
Running balance
Put your opening balance in the first data row, then in every row after it:
=E2 + IF(A3="Credit", D3, -D3)
Adjust to your layout: if you keep money in and money out as separate columns, it is simply =E2+C3-D3. Sort the sheet by date before you trust the balance, because a running balance is only correct while rows stay in date order. Credit card accounts get their own balance column on the same principle, starting from that card's opening balance.
Spending by category with SUMIFS
One formula answers "how much did we spend on groceries this month?":
=SUMIFS(Transactions!$F:$F, Transactions!$C:$C, "Groceries", Transactions!$B:$B, ">="&DATE(2025,6,1), Transactions!$B:$B, "<"&DATE(2025,7,1))
Here column F is the amount, column C the category, column B the date. Point the category condition at a header cell instead of typing "Groceries" and you can copy the formula down a summary sheet, once per category, for every month. Add a third condition on an Account column and the same SUMIFS tells you what each current account, ISA or credit card holds, which is how you keep six accounts in one file without them blurring together.
What's left to budget after bills
This is the number worth quoting. List your recurring bills with their amounts in a small table, then:
=Income - SUM(Bills[Amount]) - SUMIFS(Transactions!$F:$F, Transactions!$B:$B, ">="&DATE(2025,6,1))
Income minus committed bills minus what has already left the account this month. That figure is what's left to budget, and it is the one a spreadsheet gives you more honestly than most apps, because you built the bill list yourself. It is also the point where some people decide the formulas have done their job and move to dedicated software; if that thought crosses your mind, see whether you actually want to retire your spreadsheet budget or just tidy it.
How do you handle credit cards and the mortgage in one file?
Credit cards as negative balances
Give each credit card its own tab, exactly like a current account, but treat the balance as what you owe, not what you have. When you buy groceries on the card, log the transaction on the card tab; when you pay the card from your current account, log it as a transfer on both tabs so it never counts as spending twice. Your "left to spend after every bill" figure should come from current accounts only, because money already spent on the card is money that will leave next month.
Tracking the mortgage
Add a liabilities tab with the outstanding balance, the interest rate, and the monthly payment. One row per month: opening balance, interest charged, payment made, closing balance. The interest column is a simple multiplication; the closing balance rolls forward with a formula. A SUMIFS-style approach works here too, and Google's Sheets function help documents the same syntax in plain terms.
Net worth is then current accounts plus savings and ISAs, minus cards and the mortgage. It updates itself as long as you reconcile against statements monthly.
If you would rather have balances and what's left to budget kept in step for you, Thrifty Owl does that across your household's accounts (register here), though a spreadsheet remains perfectly workable.
What does a monthly review routine look like?
The 20-minute monthly checklist
Pick a fixed day, ideally the day your main statement closes, and work through the same four steps every month, in the same order, without skipping any of them. First, import or enter the month's transactions into each account sheet, including the credit cards and any savings accounts you hold. Second, reconcile against statements using the method described below. Third, scan the category column for anything misfiled, because a supermarket transaction tagged as "household" instead of "groceries" will quietly distort what's left to budget. Fourth, roll the budget forward: copy the closing balances for each envelope into the new month's rows, carrying over any underspend if you use rollover.
Finish by updating your cash flow forecast. Enter the recurring bills for next month, direct debits first, then check the "left to spend after every bill" figure still covers them. If it doesn't, move money between categories before the month starts, not after it goes wrong.
Reconciling against bank statements
Open the statement alongside the sheet and tick each transaction in a reconciliation column. Any transaction on the statement but not in the sheet gets entered; anything in the sheet but not on the statement is usually a pending card payment, so leave it for next month. When the closing balance in your sheet matches the statement to the penny, mark the month reconciled.
If you would rather not maintain this by hand, tools such as Buckets (budgetwithbuckets.com) or Moneydance (infinitekind.com) handle the running balance and budget rollover for you, and our white paper on exporting your Money file covers getting your history out first.
How do two people share one spending spreadsheet without overwriting each other?
Emailing an Excel file back and forth is the surest way to lose transactions: one partner reconciles against statements, the other opens an older copy, and somebody's entries vanish. If you are both editing, use Google Sheets instead. Both of you work in the same file at the same time, every change is versioned, and File > Version history lets you restore yesterday's version if a formula gets clobbered. Excel via OneDrive offers similar co-editing if you would rather keep your years of history in.xlsx.
The trade-off: shared cloud files mean both of you can see everything, and both can break things. Agree on rules — who enters which accounts, who reconciles, and never sort a range while the other is typing. Microsoft Money, discontinued in 2009, never solved this either; single-file tools like Moneydance still expect one keeper. If shared admin keeps causing friction, it may be time to look at a purpose-built budgeting app with no ads instead.
When does a track spending spreadsheet stop being enough?
The honest limits of the spreadsheet method
A spreadsheet has three gaps you cannot formula your way out of. First, manual entry: every transaction is typed by hand, so the file is only as current as you are. Second, no bank import, so reconciliation against statements stays a copy-and-check job. Third, no reminders: a spreadsheet will not chase you about a direct debit rising or a credit card payment due.
If you are the household member who enjoys the ritual, those limits are features. If your partner dreads them, the file goes stale the week you stop.
Where dedicated tools fit: YNAB, Buckets, Moneydance, Money Manager EX and Thrifty Owl
Dedicated tools earn their keep on exactly those three gaps. YNAB and Buckets are envelope budgeting tools with budget rollover; YNAB charges a subscription (check YNAB's pricing page for the current figure), while Buckets is a one-time purchase. Moneydance handles bank import and multi-account net worth tracking. Money Manager EX is free and closest in spirit to a structured spreadsheet.
Thrifty Owl runs the same criteria honestly: it answers "what's left to spend after every bill" for a household across accounts, but it is not a transaction-level replacement for your years of Money-style history. Keep the spreadsheet as your archive; use a tool for the weekly routine.
Can you bring your old Microsoft Money history into a spreadsheet?
Yes, by exporting your.mny file to QIF and importing that into Excel or Google Sheets. In Microsoft Money, open each account, use File then Export, and choose QIF format. QIF is plain text, so once the file opens in your spreadsheet you can split it into columns: date, payee, amount, category.
Do one account at a time, and check a few months against your paper statements before trusting the import. Dates and negative amounts are the usual things to fix. Keep the imported history on its own tab, then start fresh columns for this month onward. If Money refuses to export, older versions on a spare Windows machine usually still will.
FAQ
Is Excel or Google Sheets better for tracking spending?
Google Sheets wins for households with two people entering transactions, since both can edit the same file at once and version history recovers mistakes. Excel is fine for a single keeper and handles years of imported .mny history comfortably. If you already have years of .xlsx archives, staying in Excel avoids a conversion; otherwise Sheets' co-editing makes it the safer default.
How do I categorise transactions automatically in a spreadsheet?
Build a lookup table mapping payees to categories, then use a formula like VLOOKUP or XLOOKUP so typing "British Gas" fills the category column for you. New payees need one manual entry, after which the rule sticks. Keep the mapping table on its own tab so you can audit it, and scan monthly for anything the lookup misfiled.
How much detail should I record per transaction?
Seven core columns is enough: date, account, payee, category, amount, a cleared flag, and notes. The notes column is where detail earns its keep, capturing what categories lose, such as which child a payment covered. Anything more granular, like line items per shop, turns every entry into a chore and makes the file go stale faster.
Can I track spending in a spreadsheet without linking my bank?
Yes, and that is the default for a spreadsheet. You enter or import transactions yourself, then reconcile monthly against statements by ticking each one off in a column. It costs 20 minutes a month and means no credentials shared with anything. The trade-off is currency: the file is only as current as your last entry, not your last card tap.
What's the difference between tracking spending and budgeting?
Tracking is the record: what actually left each account, by category and month. Budgeting is the plan: what each category is allowed before the month starts. Tracking without a budget tells you where money went; budgeting without tracking tells you nothing about whether you stuck to it. The spreadsheet does both, with the "left to budget" formula bridging them.