How to categorize bank transactions in Excel or Google Sheets

Once your bank transactions are in a spreadsheet, the next question is usually where the money went. That means giving each transaction a category, such as groceries, rent or software, and totalling them. Here's a way to do it in Excel or Google Sheets that does most of the work with formulas and leaves you to fix the rest by hand.

1. Start with a clean table

One row per transaction, with a date, a description and the amount. A single amount column, negative for money out, makes the totals simplest. Add an empty Category column at the end. If your dates or amounts came in as text, fix that first; see Excel shows bank statement dates as text.

2. Let a formula suggest categories

Bank descriptions are messy ("AMZN MKTP US*2K4", "SQ *COFFEE HOUSE"), but most contain a word you can match on. In the first Category cell, check the description for keywords:

=IFS(
  ISNUMBER(SEARCH("AMZN", B2)), "Shopping",
  ISNUMBER(SEARCH("SHELL", B2)), "Fuel",
  ISNUMBER(SEARCH("PAYROLL", B2)), "Income",
  TRUE, "Uncategorized"
)

SEARCH ignores upper and lower case and finds the word anywhere in the description. IFS checks each rule in turn and uses the first match; the last line catches everything else. Fill the formula down the column. IFS works in Google Sheets and in Excel 2019 and later. In older Excel, nest IF functions instead.

Start with the dozen or so payees that appear most often. Sort by description to spot them, add a rule for each, and the "Uncategorized" pile shrinks quickly.

3. Fix the leftovers by hand

Formulas won't catch everything, and some transactions need judgment: a transfer, a refund, a purchase split between business and personal. Copy the Category column and paste it back as values (Paste Special → Values in Excel, or Paste special → Values only in Sheets), then overwrite the ones that need it. That way your hand edits aren't recalculated away.

4. Total by category

A summary table with one row per category and a SUMIFS next to each name:

=SUMIFS(C:C, D:D, F2)

Here C is the amount, D is the category, and F2 holds the category name. Money out totals come out negative; wrap the formula in -(…) if you'd rather see spending as positive numbers.

A pivot table does the same in a few clicks. In Excel, select the table and choose Insert → PivotTable. In Google Sheets, choose Insert → Pivot table. Put Category in Rows and Amount in Values.

5. Check nothing was lost

The category totals should add up to the same net figure as the whole Amount column. And if these transactions came from statements, the total should match each statement's money in and money out. The reconciliation template checks that in one step.

Getting the transactions in

If you're starting from PDF statements, the statement converter gives you this clean table directly: one row per transaction, real dates and amounts, and a check against each statement's closing balance. If you have a bank CSV, the bank CSV converter tidies it into the same shape.