Transactions, Categories and Summary are the three sheets needed for a working personal finance tracker in Excel. Enter or paste each transaction once, pick its category from a dropdown, and let SUMIFS add everything up by month. It takes about half an hour to set up and works the same in Google Sheets.
What goes on the Transactions sheet?
Create these columns in row 1: Date (A), Description (B), Amount (C), Category (D), Account (E), Month (F). Enter spending as positive numbers and income in a separate column or with a clear Type column. Keep the data as a plain block with no blank rows and no merged cells, because formulas hate both.
In F2 put =TEXT(A2,"yyyy-mm") and copy it down. That gives a month key such as 2026-11. This works in Excel and in Google Sheets.
How do you make the category dropdown?
- Type your category names on the Categories sheet, A2 downwards.
- Select D2:D500 on the Transactions sheet.
- Open Data, then Data Validation, choose List and point the source to the category range.
- Add new categories at the bottom of the list when you need them.
Keep to ten or twelve categories. More than that and entries get inconsistent.
Which formulas build the summary?
On the Summary sheet, list the categories down column A and the months across row 1 (for example B1 as 2026-11). In B2 use:
=SUMIFS(Transactions!$C$2:$C$500, Transactions!$D$2:$D$500, $A2, Transactions!$F$2:$F$500, B$1)
The sum range comes first, then each pair of range and criterion. Copy it right and down. Add a total row with =SUM(B2:B13) and a check cell that compares it with =SUMIFS(Transactions!$C$2:$C$500, Transactions!$F$2:$F$500, B$1), so you notice if a row has a missing category.
| Sheet | Purpose | Key formula |
|---|---|---|
| Transactions | One row per payment | =TEXT(A2,"yyyy-mm") |
| Categories | List behind the dropdown | Plain list, no formula |
| Summary | Category by month | =SUMIFS(...) as above |
| Check | Catch uncategorised rows | =COUNTBLANK(Transactions!D2:D500) |
A worked example
Say November has food rows of ₹420, ₹1,850, ₹960 and ₹310, transport rows of ₹1,200 and ₹650, and one rent row of ₹18,000. The Summary shows Food as 420 + 1,850 + 960 + 310 = ₹3,540, Transport as ₹1,850 and Rent as ₹18,000, for a total of ₹23,390. If the check cell gives a different total, a row has no category.
Where do spreadsheets start to hurt?
- Pasting a whole bank statement every month and cleaning dates and amounts by hand.
- Categorising hundreds of repeat rows such as the same milk vendor.
- Keeping receipts linked to the right row.
- Using it from a phone.
That is the point where a sheet stops being enough, and an app that reads statements and learns your categories is worth a look. SpendixAI accepts PDF, CSV and Excel statements, shows the rows for review, and can export CSV when you want your data back in a sheet.
Three extra touches that keep the file honest
- Freeze the top row so headers stay visible while you scroll.
- Add a column called Source with values such as Statement or Cash, so you can tell which rows came from a bank file.
- Colour the check cell red with conditional formatting when it is not zero, so a missing category cannot hide.
If you use Google Sheets, the same formulas work as written. Excel 365 users can also turn the Transactions block into a Table with Ctrl+T, which makes ranges grow automatically; if you do, formulas can refer to column names instead of $C$2:$C$500, but the fixed-range version above works in every version.
Template checklist
- One row per transaction.
- Real dates, not text.
- Dropdown categories.
- A check cell on the summary.
- A new file every April.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

