# Categorize Bank Transactions in Excel With Free Rules

- Author: CheapBankConvert (https://cheapbankconvert.com)
- Published: 2026-10-17
- Category: Budgeting from your statements
- Canonical: https://cheapbankconvert.com/blog/categorize-bank-transactions-in-excel

> Categorize bank transactions in Excel for free with a payee lookup table, XLOOKUP rules and a catch-all check for new merchants each month.
To categorize bank transactions in Excel for free, keep a small table of payee keywords and the category each one belongs to, then let one XLOOKUP formula match every statement row against it. Rows that match nothing get a "Check" label. Each month you only label the new merchants, and the rules grow with you.

## What do you need before you categorize bank transactions in Excel?

You need your transactions as rows, not as a PDF: one row per transaction with a date, a description and a numeric amount. A CSV export from online banking or a converted statement both work. You also need a copy of Excel that has XLOOKUP, and about twenty minutes for the first setup.

### The transaction sheet

Put the rows on a sheet called Transactions and turn the range into an Excel table with Ctrl+T. A table matters here because formulas written against it fill down by themselves when you paste next month's rows underneath. Name it `Tx` in Table Design. The columns this guide uses are:

- **Date**, stored as a real date, not text.
- **Description**, the payee text exactly as the bank printed it.
- **Amount**, a number, negative for money out and positive for money in.
- **Category**, empty for now. The formula fills it.

If your bank gives two columns for withdrawals and deposits, add a helper column with `=[@Deposit]-[@Withdrawal]` and use that as Amount. One signed column keeps every later total simple.

### Checking the amounts are numbers

Select the Amount column and look at the status bar at the bottom of the window. If it shows a Sum, the values are numbers. If it shows only a Count, Excel is holding them as text, and every category total will come out as zero. Fix that first, because no rule can repair it later.

### Which Excel versions have XLOOKUP?

Microsoft's [XLOOKUP function reference](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929) lists the function for Microsoft 365, Excel 2021 and Excel 2024, and notes that it is not available in Excel 2016 and Excel 2019. If you are on one of those older copies, check the version under File, then Account, before you build the sheet around this formula.

## Building a payee lookup table for transaction categories

A payee lookup table is a two-column list on its own sheet: a keyword that appears inside a bank description, and the category that keyword means. You do not type full descriptions. You type the shortest piece of text that identifies the merchant, such as "NETFLIX" or "SHELL", and one rule then catches every variation the bank prints.

Create a sheet called Rules, enter the two headings, and make it a table named `Rules`. Here is an example of a starting table. The keywords and categories are illustrative, so replace them with merchants from your own statement.

| Keyword | Category |
| --- | --- |
| PAYROLL | Income |
| RENT | Housing |
| CITY WATER | Utilities |
| SHELL | Transport |
| UBER | Transport |
| KROGER | Groceries |
| ALDI | Groceries |
| NETFLIX | Subscriptions |
| SPOTIFY | Subscriptions |
| CHIPOTLE | Eating out |

### Picking keywords that survive the bank's formatting

Banks and card processors decorate payee names. The same coffee shop can appear as "SQ *BEAN THERE 0412", "SQ *BEAN THERE SEATTLE WA" and "BEAN THERE LLC". The part that never changes is "BEAN THERE", so that is the keyword. Leave out store numbers, dates, city names and processor prefixes such as "SQ *" or "TST*", because those change from one row to the next.

Keep keywords in capitals if your bank prints in capitals. The SEARCH function used below ignores case anyway, but matching the bank's style makes the table easier to scan.

### Choosing the category names

The category column should use one fixed list of names, spelled the same way every time. "Groceries" and "Grocery" become two separate totals in any summary. If you have not settled on a list yet, the [budget categories list built from real statement rows](/blog/budget-categories-list) gives a starting set you can trim. To stop spelling drift, add a third sheet with your category names and apply Data Validation to the Category column of the Rules table, so it only accepts names from that list.

### Order matters when two keywords match

When a description contains two keywords, the formula below returns the first rule in the table that matches. Put specific rules above general ones. "AMAZON PRIME" belongs above "AMAZON", otherwise the Prime fee lands wherever ordinary Amazon orders go. Sorting the table alphabetically will break this, so keep the specific rules at the top by hand.

## The XLOOKUP formula that puts a category on each transaction

The formula searches each description for every keyword in the Rules table at once, picks the first keyword it finds, and returns that keyword's category. If no keyword appears, it returns "Check". You enter it once, in the first row of the Category column, and the table copies it down.

Type this in the Category column of the `Tx` table:

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

### What each part does

Read it from the inside out:

1. `SEARCH(Rules[Keyword], [@Description])` looks for every keyword inside this row's description. For each keyword it returns a position number if found, or an error if not.
2. `ISNUMBER(...)` turns that list into TRUE for found and FALSE for not found.
3. `XLOOKUP(TRUE, ..., Rules[Category], "Check")` finds the first TRUE and returns the category on the same line of the Rules table.

The fourth argument is what Microsoft's reference calls if_not_found. Without it, an unmatched row shows #N/A, which also breaks any SUM over the column. With it, the unmatched row shows a word you can filter on.

### A worked example on three rows

Take three example rows from a statement and the Rules table above:

| Description | Amount | Category returned |
| --- | --- | --- |
| KROGER #0521 COLUMBUS OH | -84.17 | Groceries |
| SQ *BEAN THERE 0412 | -5.40 | Check |
| SPOTIFY USA 877-778-1161 | -11.99 | Subscriptions |

The coffee shop has no rule yet, so it says Check. Add one line to the Rules table, "BEAN THERE" and "Eating out", and the row updates at once. So does every past and future row from that shop.

### Why not a nested IF?

A long chain of `IF(ISNUMBER(SEARCH("KROGER",...)),"Groceries",IF(...))` works for five merchants and becomes unreadable at fifty. Every new merchant means editing a formula. The lookup table keeps the logic in rows you can read, sort and extend, and the formula never changes.

## Which bank transactions should a payee rule leave alone?

Some rows look like spending but are not, and some carry a payee name that says nothing about what you bought. Transfers between your own accounts, card payments, refunds and marketplace orders all need a person, or a rule written with care. A payee rule that files them blindly makes every total wrong.

### Transfers and card payments

A transfer from checking to savings, or a payment to your credit card, moves money between your own pockets. The spending already happened, or has not happened yet. Give these rows a category of their own, such as "Transfer", and leave that category out of every spending total. A rule on "ONLINE TRANSFER" or your card issuer's payment text is safe, because those rows are always transfers.

### Refunds

A refund from a shop carries the shop's name, so the keyword rule gives it the same category as the purchase. That is usually what you want, because the positive amount then reduces that category's total. The trap is a refund that comes back in a later month, after you have already judged that month. The later month's total then looks lower than its real spending, which also skews a percentage check such as [the 50/30/20 budget worked from statement rows](/blog/50-30-20-budget-from-bank-statements).

### Marketplaces and payment apps

"AMAZON MKTPLACE", "PAYPAL *" and "VENMO" tell you who processed the payment, not what it was for. One Amazon order is groceries, the next is a birthday gift. You have three honest choices:

- Give them a broad category such as "Shopping" and accept a less precise budget.
- Leave them out of the Rules table so they always show as Check, and label each one by hand.
- Overwrite the formula in that one row with a typed category. Excel tables allow this, though the cell then stops updating.

The second option costs a minute a month for most households and keeps the categories true.

## The catch-all check for merchants your payee rules miss

The catch-all check is a monthly look at every row the formula labelled "Check". Those rows are the merchants your rules have never seen. You filter for them, decide each category, and add a keyword to the Rules table so the same merchant is handled next month. Over a few months the Check list shrinks to a handful.

### The monthly steps

Here is the routine, from new statement to finished categories:

1. Paste the new month's rows below the last row of the `Tx` table. The Category formula fills in by itself.
2. Filter the Category column to show only Check.
3. For each Check row, find the stable part of the description and add it to the Rules table with a category.
4. Clear the filter and confirm the Check count is zero, or only rows you mean to label by hand.
5. Sum spending by category for the month with SUMIFS or a pivot table.

To see the count without filtering, put `=COUNTIF(Tx[Category],"Check")` in a cell at the top of the sheet. When it reads 0, the month is done.

### How many rules does a household need?

That depends on how many different places you buy from. A household that shops at the same few stores may cover most rows with a couple of dozen keywords after two or three months. Someone who travels or tries new restaurants every week will keep a longer Check list. Either way, the first month is the slow one, because every merchant is new.

### Getting the rows in without retyping

The routine only works if the rows arrive clean. If your bank offers a CSV download, start there. If you only have PDF statements, a converter saves the typing. [CheapBankConvert](/), which is getting ready to open, turns a PDF statement into a CSV or Excel file with amounts stored as numbers, and you pay once for that document rather than a monthly fee.

## Payee rule mistakes that misfile spending in Excel

Most misfiled rows come from keywords that are too short, too long or in the wrong order. A keyword that is too short matches merchants it was never meant for. One that is too long misses the next variation of the same merchant. A general rule above a specific one swallows the specific case.

### Keywords that are too short

The keyword "BP" for the fuel station also matches "BPS PHOTO" and any description containing "BP" inside another word. SEARCH finds text anywhere in the description, not whole words only. Use a longer keyword such as "BP#" or the full station brand name as printed, and test it with a quick filter on the Description column before you trust it.

### Keywords copied with the store number

If you copy "KROGER #0521" into the Rules table, the store across town, printed as "KROGER #0388", will not match. Strip the number and keep "KROGER".

### Spaces and invisible characters

Some bank exports pad descriptions with trailing spaces or non-breaking spaces. A keyword typed with a trailing space of its own, such as "NETFLIX ", can then fail on rows that end right after the name. Use `=LEN()` on a suspicious keyword to count its characters, and clean descriptions in a helper column if the problem is in the bank's data. `=TRIM(SUBSTITUTE([@Description],CHAR(160)," "))` swaps non-breaking spaces for ordinary ones and then trims the ends.

### When a spreadsheet stops being the right tool

Rules in a spreadsheet suit a personal or household budget. If you are also keeping books for a side business, with invoices, receipts and tax categories, the trade-offs change. [A free bookkeeping app against a spreadsheet](/blog/free-bookkeeping-app-vs-spreadsheet) compares what each one costs you in time once the business side grows. For a budget, the lookup table and one formula are usually all you need.