Getting started

Build your own personal finance app in Excel: a template that works

Build a personal finance Excel template with three sheets, working SUMIFS and dropdown categories, plus signs it is time to move beyond a spreadsheet.

Editorial illustration for Build your own personal finance app in Excel: a template that works

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?

  1. Type your category names on the Categories sheet, A2 downwards.
  2. Select D2:D500 on the Transactions sheet.
  3. Open Data, then Data Validation, choose List and point the source to the category range.
  4. 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.

SheetPurposeKey formula
TransactionsOne row per payment=TEXT(A2,"yyyy-mm")
CategoriesList behind the dropdownPlain list, no formula
SummaryCategory by month=SUMIFS(...) as above
CheckCatch 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.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports