How to categorize bank transactions in Excel (formulas included)

You have a few hundred rows of bank transactions in a spreadsheet and no idea what you actually spent on groceries last quarter. Manually tagging them takes an evening and has to be redone next month. This is a twenty-minute setup that tags them automatically, improves every time you use it, and gives you a month-by-month breakdown at the end. Every formula below is copy-paste ready.

Start from clean rows

The setup assumes a sheet named Data with one transaction per row:

Amounts in one signed column — negative for money out. If your rows came out of a PDF with descriptions split across lines or amounts as text, fix that first (converting a statement to Excel covers it); formulas can't categorize a mess. If you convert with Statement Mill, column E arrives already filled — the categorization below then becomes a way to override with your own rules rather than start from zero.

Step 1 — Build the rules table

New sheet, name it Rules. Two columns: a keyword found in the description, and the category it means.

Two things matter here. Order is priority — the first matching keyword wins, so put specific rules above general ones ("AMAZON PRIME" → Subscriptions above "AMAZON" → Shopping). And keep it in one sheet: this table is the asset. It grows for a few months and then covers nearly everything you spend money on.

Step 2 — The formula that tags every row

In Data!E2, then fill down:

=IFERROR(INDEX(Rules!$B$2:$B$200,
   MATCH(TRUE, ISNUMBER(SEARCH(Rules!$A$2:$A$200, $B2)), 0)),
 "Uncategorized")

Reading it inside-out: SEARCH looks for each keyword inside the description and returns a position or an error; ISNUMBER turns that into TRUE/FALSE; MATCH(TRUE, …, 0) finds the first TRUE — the first matching rule; INDEX returns that rule's category; and IFERROR catches rows nothing matched, labelling them Uncategorized rather than leaving a confusing #N/A.

Two notes. SEARCH is case-insensitive and matches anywhere in the text, which is what you want for bank descriptions (use FIND instead if you ever need case sensitivity). And in Excel 2019 or older, this is an array formula — confirm it with Ctrl+Shift+Enter. Microsoft 365 and Excel 2021 handle it normally.

Prefer newer functions? This is the same logic in Microsoft 365:

=LET(hits, ISNUMBER(SEARCH(Rules!$A$2:$A$200, $B2)),
     IFERROR(INDEX(Rules!$B$2:$B$200, XMATCH(TRUE, hits)), "Uncategorized"))

Step 3 — Work the Uncategorized pile

This is the part that makes the system worth keeping. Filter column E to Uncategorized and you'll see maybe 15% of rows on the first run. For each recurring merchant, add one line to Rules — the formula re-tags instantly, and that merchant is handled forever.

Two or three passes and Uncategorized drops to genuine one-offs. Don't chase zero: a small unmatched tail is normal and honest, and it's better than inventing a rule so broad it mislabels things quietly.

Step 4 — Total by category

New sheet, Summary. Column A: your category names. Column B:

=SUMIFS(Data!$D:$D, Data!$E:$E, $A2)

Spending arrives negative, which is correct but reads oddly in a report. To show positive spend:

=-SUMIFS(Data!$D:$D, Data!$E:$E, $A2, Data!$D:$D, "<0")

And a count of transactions per category, which surfaces subscriptions you forgot you had:

=COUNTIFS(Data!$E:$E, $A2)

Step 5 — The month-by-month view

Categories along the rows, months across the columns. Put a real first-of-month date in each column header (2026-01-01, 2026-02-01, …) and format it as MMM YYYY so it displays as a month but stays a date. Then in the grid:

=-SUMIFS(Data!$D:$D,
    Data!$E:$E, $A3,
    Data!$A:$A, ">=" & B$2,
    Data!$A:$A, "<" & EDATE(B$2, 1),
    Data!$D:$D, "<0")

The mixed references ($A3, B$2) mean you write it once and drag across the whole grid. EDATE(B$2,1) is the first of the next month, so each column captures exactly its own month regardless of length.

Now a trend jumps out: subscriptions creeping up, one month where groceries doubled, the quarter your fuel spend changed. That's the actual point of categorizing.

The PivotTable alternative

If you'd rather not maintain formulas: select the Data range → Insert → PivotTable → Category in Rows, Date in Columns (right-click a date → Group → Months), Amount in Values. Same result in thirty seconds, less control, and it needs refreshing when data changes. Use whichever you'll actually keep up.

Before you trust any of it: check the total

Every number above is built on the assumption that all your transactions made it into the sheet. Verify it against the statement's own arithmetic:

=<opening balance> + SUM(Data!$D:$D)

That must equal the closing balance printed on your statement. If it does, no row was lost or misread and your category totals mean something. If it doesn't, the gap is in your data, not your formulas — and every chart above is quietly wrong. (This check is why Statement Mill reconciles every export against the statement's balances before you download it; the spreadsheet version above is the manual equivalent.)

Reusing it next month

Keep the workbook. Next month, paste new rows under the existing ones in Data, drag column E down, and your rules apply instantly — the setup cost is paid once. If you're converting statements each month anyway, export straight to Excel and paste; between an already-categorized column and your own rules on top, the evening of manual tagging becomes about two minutes.