Categorize bank transactions automatically

How automatic categorization works

Bank descriptions are written for the bank's systems, not for people: card terminal codes, store numbers, masked card numbers and reference strings. Categorizing them starts with working out who was actually paid. So every transaction first gets a clean payee name, then a category.

Your own rules are applied first. Everything they don't cover is categorized by AI (Anthropic's Claude), which reads the whole description, the amount and whether money went in or out, and picks one of your categories. Anything that doesn't fit lands in the catch-all Uncategorized, where it's easy to find. For example:

Use your own categories, in any language

You start with a default list in your language — Income, Transfers, Groceries, Restaurants & Cafes, Transport, Housing, Utilities, Software & Subscriptions, Professional Services, Bank Fees, Taxes and more — that works for both business and personal accounts. Rename, add or remove categories until the list matches how you actually report.

That might be your chart of accounts ("6100 Software"), the expense lines of a tax form, or a household budget. Category names can be in any language, independent of the statement's language: a Spanish or German statement can be sorted into English categories, and the other way round. For help choosing a list, see business expense categories.

Rules that always win

Some things only you know: that a transfer to J. Smith is rent, or that a payment from a person's name is a client invoice. When you change a transaction's category, you can turn it into a rule — description contains X → category — and it applies to every future statement before the AI is asked. Typical rules:

• Transfers between your own accounts — e.g. a savings account number → Transfers, so they don't show up as spending or income
• A landlord or contractor paid by personal name → Housing or Professional Services
• Client payments that arrive with an invoice reference → Income
• Owner's drawings or personal spending on a business card → a category you name yourself

Rules always beat the AI, so once a rule exists you get the same category every time, on every statement.

Totals by category and month

The Excel export has a Summary sheet next to the transactions, with the totals people usually build by hand:

• Money in, money out and net change for the period
• Money in, money out, net and number of transactions per category
• The same totals per month, for statements that span several months

Select a full year of statements and export them together to get one workbook with annual totals per category — the starting point for tax preparation or a yearly personal budget review.

How to categorize bank transactions in Excel

If you'd rather do it by hand, the usual Excel method is a keyword table. Put the keywords and categories in an Excel table (Insert → Table) named Rules, with a Keyword and a Category column — for example WHOLEFDS → Groceries, UBER → Transport, ADOBE → Software. Then, next to a description in B2, use this formula in Excel 365:

=XLOOKUP(TRUE, ISNUMBER(SEARCH(Rules[Keyword], B2)), Rules[Category], "Uncategorized")

SEARCH looks for every keyword inside the description, and XLOOKUP returns the category of the first keyword it finds, so put specific keywords (UBER EATS) above general ones (UBER). Keep the table free of blank rows, because an empty keyword matches every description. Then total with =SUMIFS(D:D, C:C, "Groceries") (amounts in column D, categories in C) or a PivotTable with Category in Rows and the date grouped by month in Columns.

This works, but the keyword list never stops growing: every new merchant, every bank that abbreviates differently, every card terminal code needs a new row. Starting from an Excel export that is already categorized leaves you only the exceptions to fix — and you can still add your own formulas next to it.

Categorize before import, or in QuickBooks and Xero?

QuickBooks Online, Xero and Zoho Books all have bank rules that categorize transactions coming in through their bank feeds, and if all your accounts are connected, those rules are the natural place to work. Categorizing the statement first is the better fit when you need a categorized spreadsheet rather than ledger entries — for a tax return, a client review, a lender or a budget — when an account isn't connected, or when you're working through months of old PDFs.

Categories travel with the Excel, CSV and QIF exports, and in the Analysis code column of the Xero CSV. The QuickBooks files carry the clean payee name and the statement text, and you assign accounts in QuickBooks as usual.

Check and correct in one place

No automatic categorization is right every time, so everything stays editable. On the review screen you can change any date, description, amount or category; the balance check re-runs when you save, so a fix never silently breaks the totals. Corrections you turn into rules make the next statement more consistent.

Your statements aren't used to train AI, the uploaded file isn't stored, and there is never a bank login or bank connection. Wondering whether a general chatbot could do the same? See ChatGPT vs a bank statement converter.

Questions