How to Categorize Bank Transactions in Excel (With Formulas)

Step 1: Get the transactions into Excel

You have three options:

• CSV from online banking. Fast for recent months, but most banks limit how far back you can download, and the column layout differs by bank.
• PDF statements. Often the only source for older periods, closed accounts, or files a client sends. A bank statement to Excel converter turns them into rows.
• Manual entry. Only sensible for a handful of transactions.

When a client sends a mix of PDFs and CSVs covering different accounts, combine them into one sheet with an Account column, and remove duplicates where the same period arrived in both formats (match on date, amount, and description).

Before categorizing anything, check that the transactions tie out: opening balance plus deposits minus withdrawals should equal the closing balance printed on the statement. If it does not, a page or a period is missing. See how to reconcile a bank statement.

Step 2: Format the data as a table

Select the data and press Ctrl+T to make it an Excel table, then name it Transactions (Table Design, Table Name). Tables expand automatically and let formulas use column names instead of cell ranges.

Use these columns, in this order: Date, Description, Amount, Category, Override, Final.

Use one signed Amount column: positive for money in, negative for money out. If the bank exported separate Debit and Credit columns, add a column with =[@Credit]-[@Debit] and use that.

Step 3: Build a categories list and a rules table

On a second sheet, create two tables.

Categories (a table named Categories with one column, also headed Categories): your category list, ideally the client's chart of accounts, such as Sales, Software, Rent, Utilities, Bank Fees, Meals, Owner Draws, Transfers, Uncategorized.

Rules (named Rules), with two columns:

Order matters: the formula returns the first match, so put specific keywords above general ones ("AMAZON WEB SERVICES" before "AMAZON").

Step 4: Add the categorization formula

In the Category column of Transactions, enter:

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

How it works: SEARCH looks for every keyword inside the description (case-insensitive), ISNUMBER turns hits into TRUE, and XLOOKUP returns the category of the first TRUE. Anything without a match becomes "Uncategorized", which is your review queue.

If your Excel version has no XLOOKUP (Excel 2019 and earlier), use this instead, entered with Ctrl+Shift+Enter in older versions:

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

To route by direction as well as keyword (for example, any uncategorized deposit should be reviewed as possible income), wrap it: =IF(AND([@Amount]>0, <formula>="Uncategorized"), "Review: Income", <formula>).

Step 5: Add a dropdown for overrides

Rules will not catch everything. Rather than overwriting the formula, add an Override column with a dropdown:

1. Select the Override column.
2. Data, Data Validation, Allow: List.
3. Source: =INDIRECT("Categories[Categories]") (data validation does not accept table references directly, so INDIRECT is needed; a named range also works).

Then in the Final column:

=IF([@Override]<>"", [@Override], [@Category])

When the same override keeps recurring, add a rule instead, so next month it happens automatically.

Step 6: Summarize by category

For a monthly category report, list categories down column A and month start dates across row 1, then use:

=SUMIFS(Transactions[Amount], Transactions[Final], $A2, Transactions[Date], ">="&B$1, Transactions[Date], "<"&EDATE(B$1,1))

Or insert a pivot table from Transactions: Final in Rows, Date (grouped by month) in Columns, Amount in Values. The pivot is faster to build; SUMIFS is easier to format as a fixed report.

Check the result: the sum of all categories for a month should equal the net change in the bank balance for that month. Transfers between the client's own accounts should net to zero across accounts.

When Excel stops being the right tool

This workbook is fine for one client and a few hundred rows. It gets slow when you are converting dozens of PDF statements, maintaining rules per client, or exporting into QuickBooks or Xero. Transaction categorization software does the same keyword rules plus suggestions for unmatched lines, maps categories to the client's chart of accounts, and exports to Excel, CSV, QuickBooks, or Xero. DocuClipper reconciles each statement against its printed balances before you categorize. For tool options, see automatic transaction categorization.

FAQ

Can Excel categorize bank transactions automatically?

Yes. With a rules table of keywords and categories, a single XLOOKUP and SEARCH formula assigns a category to every description that contains a known keyword. Unmatched rows show as Uncategorized for manual review.

Why does my formula return the wrong category?

The formula returns the first matching keyword, so a general keyword placed above a specific one wins. Move specific keywords, such as "AMAZON WEB SERVICES", above general ones, such as "AMAZON".

How do I add a category dropdown in Excel?

Use Data Validation with Allow set to List and a source of =INDIRECT("Categories[Categories]"), or a named range pointing at your category list.

How do I total expenses by category and month?

Use SUMIFS on the Amount column with criteria for the category and a date range, or build a pivot table with categories in rows and dates grouped by month in columns.

Can I categorize transactions from PDF bank statements?

Yes, after converting the PDFs to Excel. Check that the converted transactions tie to the statement's opening and closing balances before categorizing.

Written by the DocuClipper editorial team. Last updated October 9, 2026.