How to Categorize Bank Transactions in Excel (Rules + Formulas)
A converted bank statement tells you what happened. Categorising it tells you what it means: how much went on groceries, what your subscriptions really cost, which expenses belong on a tax return. You could tag every row by hand, but a small rules table and one formula will do most of the work for you, and keep doing it every month.
This guide builds that system step by step: a keyword rules table, three formula options (with the trade-offs of each), sensible handling of transfers and refunds, and a monthly pivot table. A worked example with ten transactions shows the whole thing end to end.
Before you start: one clean table
You need one row per transaction with:
- A real date in column A.
=ISNUMBER(A2)should return TRUE. - The description in column B, as printed on the statement.
- One signed amount in column C: positive for money in, negative for money out. If your data has separate Debit and Credit columns, calculate credit minus debit in a new column, for example
=D2-C2when Debit is in C and Credit in D, then paste the results as values.
If you’re starting from PDFs, convert them first. pdf2statement runs in your browser and can export one signed Amount column directly to Excel or Google Sheets. If you have several months, combine them into one sheet before categorising; see how to combine multiple bank statements into one spreadsheet.
It’s also worth confirming the data is complete. A categorised total is only as good as the rows underneath it, and reconciling a bank statement in Excel shows how to check every row against the running balance.
Step 1: Build a keyword rules table
Create a sheet called Rules with two columns: Keyword in A and Category in B, headers in row 1. Each row says “if the description contains this text, use this category.”
Tips for good rules:
- Use the distinctive part of the description as your bank prints it. Card payments often carry prefixes, store numbers or locations, so pick the part that stays the same.
- Case doesn’t matter. SEARCH, used in all the formulas below, ignores upper and lower case.
- Avoid very short keywords. “BP” would also match any description that happens to contain those two letters together. Longer, more specific text is safer.
- Wildcards work. SEARCH understands
*and?, so a keyword such asAMZN*MKTPmatches “AMZN MKTP” and “AMZN Mktp UK”. To search for a literal asterisk or question mark, put a~in front of it. - Order matters. When a description matches two rules, the formula’s precedence decides which wins, and the formulas below differ in which one that is.
- No blank rows inside the range. SEARCH treats an empty keyword as matching everything. Either make your formula ranges end exactly at the last rule, or select the rules and use Insert > Table so ranges grow and shrink automatically.
Step 2: Choose a categorising formula
Assume your transactions are on a sheet with Date in A, Description in B, Amount in C, and the category going in D. The rules occupy Rules!A2:B9.
Option A: XLOOKUP and SEARCH (Microsoft 365, Excel 2021 and later)
In D2:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(Rules!$A$2:$A$9,B2)),Rules!$B$2:$B$9,"Uncategorised")
How it works: SEARCH looks for every keyword inside the description and returns a position number where it finds one, or an error where it doesn’t. ISNUMBER converts that into TRUE or FALSE for each rule, and XLOOKUP returns the category of the first rule that’s TRUE. Anything that matches nothing gets “Uncategorised”. Put specific rules above general ones, for example “AMAZON PRIME” above “AMAZON”.
You might expect XLOOKUP’s own wildcard mode to handle this. It doesn’t fit the job, because XLOOKUP applies wildcards only to the value you’re looking up, which would find a description containing a given keyword, the opposite of what you need. Wildcards inside your keywords still work here, because SEARCH is the function handling them.
If your rules are in a Table named tblRules, the same formula reads =XLOOKUP(TRUE,ISNUMBER(SEARCH(tblRules[Keyword],B2)),tblRules[Category],"Uncategorised").
Option B: LOOKUP(2^15, SEARCH(…)) for any Excel version
In D2:
=IFERROR(LOOKUP(2^15,SEARCH(Rules!$A$2:$A$9,B2),Rules!$B$2:$B$9),"Uncategorised")
How it works: SEARCH again returns positions or errors. LOOKUP searches for 2^15 (32,768), a number bigger than any possible position, because a cell holds at most 32,767 characters. LOOKUP ignores the errors and settles on the last numeric result, returning that rule’s category. If nothing matches, LOOKUP returns an error and IFERROR substitutes “Uncategorised”. It works in old and new versions alike, with no Ctrl+Shift+Enter needed.
The catch is precedence: here the last match wins, so specific rules go below general ones. Don’t switch between options A and B without reversing the order of your rules.
Option C: IFS for a handful of rules
For a short, fixed list, IFS (Excel 2019 and later) keeps everything in one formula and can use the amount as well as the text:
=IFS(ISNUMBER(SEARCH("PAYROLL",B2)),"Income",ISNUMBER(SEARCH("TRANSFER",B2)),"Transfer",C2<0,"Other spending",TRUE,"Other income")
Conditions are tested in order, and TRUE at the end catches everything else. It’s readable for four or five rules, but because the rules live inside the formula, every change means editing and refilling it. Beyond a handful of categories, use a rules table.
Manual overrides
Some rows will always need a human decision. Add an Override column (E) for those, and a Final category column with =IF(E2<>"",E2,D2). Build your summaries on Final category, and the override survives even when you change the rules.
Step 3: Handle transfers, refunds and awkward rows
- Transfers between your own accounts aren’t income or spending. Give them a Transfer category and exclude it from totals. Otherwise a 500 transfer to savings looks like 500 of spending, and it’s counted twice if you also include the savings account.
- Credit card payments are transfers too, if you track the card itself. The card statement holds the real purchases; see credit card statement to Excel.
- Refunds should share the original purchase’s category. With signed amounts, they net off automatically. A separate “Refunds” category hides the fact that you spent less.
- Cash withdrawals deserve their own category, since you can’t see what the cash was spent on.
- Mixed purchases, such as a supermarket shop that included a gift, can be split into two rows by hand or handled with an override.
Step 4: Summarise by month with a pivot table
Add a Month column with =DATE(YEAR(A2),MONTH(A2),1), formatted as mmm yyyy. Then:
- Click anywhere in the data and choose Insert > PivotTable, placing it on a new worksheet.
- Drag Category (or Final category) to Rows, Month to Columns and Amount to Values.
- If Excel groups the months into years and months automatically, that’s fine. If it shows individual dates, right-click one, choose Group, and select Months and Years.
- If the values say “Count of Amount” instead of “Sum of Amount”, some amounts are stored as text. Fix them, or change the calculation under Value Field Settings.
- Use the filter on the row labels to untick Transfer.
- After adding rows or changing rules, choose Data > Refresh All.
Prefer formulas? With categories in A2 down and month start dates across B1, C1 and so on of a summary sheet:
=SUMIFS(Transactions!$C:$C,Transactions!$D:$D,$A2,Transactions!$A:$A,">="&B$1,Transactions!$A:$A,"<="&EOMONTH(B$1,0))
Worked example
The rules (sheet Rules, A1:B9)
| Keyword | Category |
|---|---|
| PAYROLL | Income |
| TRANSFER | Transfer |
| GREENLEAF | Groceries |
| FRESHWAY | Groceries |
| VOLTA | Utilities |
| STREAMBOX | Subscriptions |
| NORTHSIDE FUEL | Transport |
| CAFE | Eating out |
The transactions, with option A or B in column D
| Date | Description | Amount | Category |
|---|---|---|---|
| 2026-03-02 | PAYROLL ACME LTD | 2,800.00 | Income |
| 2026-03-04 | GREENLEAF MARKET | -64.20 | Groceries |
| 2026-03-09 | VOLTA ENERGY DD | -88.50 | Utilities |
| 2026-03-12 | CORNER CAFE | -12.40 | Eating out |
| 2026-03-15 | TRANSFER TO SAVINGS | -500.00 | Transfer |
| 2026-03-27 | NORTHSIDE FUEL | -58.00 | Transport |
| 2026-04-01 | PAYROLL ACME LTD | 2,800.00 | Income |
| 2026-04-03 | FRESHWAY SUPERMARKET | -102.35 | Groceries |
| 2026-04-06 | GREENLEAF MARKET REFUND | 15.00 | Groceries |
| 2026-04-08 | STREAMBOX SUBSCRIPTION | -11.99 | Subscriptions |
Each description matches exactly one keyword, so options A and B give the same result here. The refund matches GREENLEAF, so it lands in Groceries rather than looking like income.
The resulting pivot table
| Category | Mar 2026 | Apr 2026 | Total |
|---|---|---|---|
| Eating out | -12.40 | -12.40 | |
| Groceries | -64.20 | -87.35 | -151.55 |
| Income | 2,800.00 | 2,800.00 | 5,600.00 |
| Subscriptions | -11.99 | -11.99 | |
| Transfer | -500.00 | -500.00 | |
| Transport | -58.00 | -58.00 | |
| Utilities | -88.50 | -88.50 | |
| Grand Total | 2,076.90 | 2,700.66 | 4,777.56 |
Reading it:
- April groceries are -87.35, the -102.35 shop less the 15.00 refund.
- Spending excluding income and transfers was 223.10 in March and 99.34 in April. That’s the grand total you see (as -223.10 and -99.34) after unticking Income and Transfer in the row filter.
- The 500.00 transfer isn’t spending. It’s still in your money, just in a different account.
Keep the rules growing
Filter column D for “Uncategorised” once a month, add keywords for anything that recurs, and refresh. After a couple of months, most rows will categorise themselves. Save the workbook as a template so next month’s statement only needs pasting in.
If you use accounting software, its bank rules do a similar job once transactions are imported; see our guides to importing into QuickBooks and importing into Xero. And if you’re preparing for a loan, a categorised summary of regular outgoings is a useful starting point; see bank statements for a mortgage application.