How to Categorize a Bank Statement in Excel (Step-by-Step)

1. Export your bank statement to CSV

Most banks let you download a statement as CSV or Excel from the transactions screen. You'll typically get columns for Date, Description, and Amount (sometimes split into Debit/Credit).

Open the file in Excel and convert the range to a Table (Insert → Table) so formulas auto-fill as you add rows.

2. Build a category lookup table

On a second sheet called Categories, create two columns: a Keyword that matches part of a transaction description, and the Category you want assigned. For example:

• UBER → Transport
• TESCO → Groceries
• NETFLIX → Subscriptions
• SALARY → Income

This table is the brain of your system — every category comes from here.

3. Auto-assign categories with a formula

In your transactions sheet, add a Category column and use aVLOOKUP with wildcards, or the more flexibleINDEX/MATCH + SEARCH combination:

=IFERROR(
  INDEX(Categories[Category],
    MATCH(TRUE, ISNUMBER(SEARCH(Categories[Keyword], [@Description])), 0)
  ),
  "Uncategorized"
)

On older Excel versions, enter it as an array formula (Ctrl+Shift+Enter). Anything that doesn't match a keyword shows up as Uncategorized so you can spot gaps quickly.

4. Summarize spending with a pivot table

Select your transactions table and choose Insert → PivotTable. Drag Category into Rows,Amount into Values (set to Sum), and Date into Columns grouped by month. You now have a monthly category breakdown that updates whenever you add transactions.

5. Maintain it over time

Each month: paste new rows into the transactions table, scan for Uncategorized entries, and add the missing keywords to yourCategories sheet. Refresh the pivot table and you're done.