Excel Formulas to Categorize Bank Transactions

TL;DR: Keep your rules in a separate table (keyword → category), then use one formula to look up the first keyword found in each description. XLOOKUP with wildcards or a SEARCH-based array formula both work. Pair it with an "Override" column for exceptions and a pivot table for reports, and you'll categorize a year of transactions in minutes instead of hours.

Categorizing transactions is the part of bookkeeping that nobody enjoys and everybody needs. It's also the part where Excel shines, if you set it up right. The trick isn't a clever formula. It's separating the rules from the data, so you write each rule once and every future statement benefits.

This guide starts simple and builds up. Every formula here works in Microsoft 365 Excel; we note where older versions need a different approach.

The setup we use

Two tables:

Transactions (an Excel Table named Txn):

Date	Description	Amount	Category	Override

Rules (an Excel Table named Rules):

Keyword	Category
AMAZON	Office supplies
SHELL	Fuel
GUSTO	Payroll
ADOBE	Software
UBER	Travel
TRANSFER TO SAV	Transfer

The Category column in Txn holds a formula. The Override column is for manual exceptions. A final column (or the pivot itself) uses Override if present, otherwise Category.

Why tables? Because formulas referencing Rules[Keyword] grow automatically when you add a rule. No range adjustments, no broken references.

If your transactions came from a PDF, get them into rows first. Our bank statement to Excel converter does that and checks the balance, so you're categorizing complete data.

Level 1: a single IF

For a handful of rules, a plain formula works:

=IF(ISNUMBER(SEARCH("AMAZON",[@Description])),"Office supplies","Uncategorized")

SEARCH returns the position of the text if found (case-insensitive) and an error if not. ISNUMBER turns that into TRUE or FALSE.

This is fine for one rule. It becomes a mess at five.

Level 2: IFS for a few rules

=IFS(
 ISNUMBER(SEARCH("AMAZON",[@Description])),"Office supplies",
 ISNUMBER(SEARCH("SHELL",[@Description])),"Fuel",
 ISNUMBER(SEARCH("GUSTO",[@Description])),"Payroll",
 TRUE,"Uncategorized")

Readable, but the rules live inside the formula. Adding one means editing every cell (or at least the column formula), and you can't easily see all your rules at once. We'd move on quickly.

Level 3: lookup against a rules table

This is the approach we recommend. It finds the first keyword in Rules that appears in the description:

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

How it works:

1. SEARCH(Rules[Keyword],[@Description]) checks every keyword against the description at once, returning an array of positions or errors.
2. ISNUMBER converts that to an array of TRUE/FALSE.
3. MATCH(TRUE, hit, 0) finds the first TRUE.
4. INDEX returns the matching category.
5. IFERROR returns "Uncategorized" when nothing matches.

Because it returns the first match, rule order matters. Put specific rules above general ones. For example, "AMAZON WEB SERVICES" → Hosting should sit above "AMAZON" → Office supplies.

In older Excel versions without dynamic arrays, enter the same logic (without LET) as an array formula with Ctrl+Shift+Enter.

The XLOOKUP alternative

If your keywords are at the start of descriptions, XLOOKUP with wildcards is neat:

=XLOOKUP([@Description], Rules[Keyword]&"*", Rules[Category], "Uncategorized", 2)

The 2 enables wildcard matching. Using "*"&Rules[Keyword]&"*" matches anywhere in the description. Note that XLOOKUP's lookup direction here is "find the description against a list of patterns," which works because of the wildcard mode. Some people find the SEARCH version easier to reason about; both are fine.

Level 4: rules with amount conditions

Sometimes the description isn't enough. A transfer to your savings might be "Transfer" when it's under a certain amount and "Owner draw" when it's a large monthly sum. Or a payment to one vendor might be rent if it's the fixed monthly amount and repairs otherwise.

Extend the rules table:

Keyword	Min	Max	Category
ACME PROPERTY	2000	2000	Rent
ACME PROPERTY			Repairs

And the formula:

=LET(
 d,[@Description], a,ABS([@Amount]),
 ok, ISNUMBER(SEARCH(Rules[Keyword],d))
   * ((Rules[Min]="")+(a>=Rules[Min])>0)
   * ((Rules[Max]="")+(a<=Rules[Max])>0),
 IFERROR(INDEX(Rules[Category], MATCH(1, ok, 0)), "Uncategorized"))

Each condition becomes 1 or 0; multiplying them gives 1 only when all are true. Blank Min or Max means "no limit."

Level 5: direction-aware rules

The same payee can appear as money in and money out. A refund from a vendor shouldn't be categorized the same way as a purchase. Add a Direction column to rules (In, Out, or blank) and one more condition:

* ((Rules[Direction]="")+(Rules[Direction]=IF([@Amount]>0,"In","Out"))>0)

The Override column

No rule system is perfect. You'll have one-off transactions, ambiguous payees and things that need a human decision. Rather than overwriting the formula, type the category in Override. Then a Final column:

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

This keeps your rules intact, makes manual decisions visible, and lets you filter for overrides later to see which rules you should add.

Splitting one transaction across categories

Some transactions belong to more than one category. A warehouse club receipt might be part office supplies and part groceries; a single payment to a contractor might cover labor and materials. Bank data only shows the total, so a formula can't split it for you.

Our approach is to split the row itself. Duplicate the transaction, set the amounts on each copy to the portions (they must add up to the original), give each its own category, and mark both with a Split flag and a note. The balance still ties out because the portions sum to the original amount, and your reports show the right totals per category. Keep the receipt or invoice that justifies the split with your records.

If splits are rare, this is fine to do by hand. If they're frequent, for example a business owner who uses one card for both business and personal purchases at the same stores, the cleaner fix is upstream: separate cards for separate purposes. No formula is as good as data that doesn't need splitting in the first place.

Building the rules table quickly

The first time, start from the data:

1. Add a Payee helper column with a shortened description, for example =TRIM(LEFT([@Description],20)).
2. Make a pivot table with Payee in rows and Count of Amount and Sum of Amount in values.
3. Sort by count descending.
4. Write rules for the top 30 to 50 payees.

In most small business statements we've worked on, a few dozen payees account for the large majority of transactions. Handle those first; categorize the long tail with overrides or broad rules.

After each month, filter Final for "Uncategorized," add rules for anything recurring, and you'll find fewer each month.

Cleaning descriptions before matching

Bank descriptions are noisy. A few helper formulas make matching more reliable:

Uppercase everything (SEARCH is case-insensitive, but cleaned text is easier to read):

=UPPER([@Description])

Remove numbers (store numbers, dates, IDs) in Microsoft 365:

=TEXTJOIN("",TRUE,IF(ISERROR(--MID([@Description],SEQUENCE(LEN([@Description])),1)),MID([@Description],SEQUENCE(LEN([@Description])),1),""))

Collapse spaces:

=TRIM([@Description])

Strip common prefixes like "POS PURCHASE" or "DEBIT CARD":

=TRIM(SUBSTITUTE(SUBSTITUTE([@Description],"POS PURCHASE",""),"DEBIT CARD",""))

Use the cleaned text in your matching formula, but always keep the original Description column for the audit trail.

Reporting with SUMIFS and pivots

Once categorized, reporting is easy.

Total per category:

=SUMIFS(Txn[Amount], Txn[Final], "Software")

Per category per month (with a Month column =TEXT([@Date],"yyyy-mm")):

=SUMIFS(Txn[Amount], Txn[Final], $A2, Txn[Month], B$1)

Put categories down column A and months across row 1, and you have a full monthly report that updates when data changes.

Pivot table: Insert > PivotTable, Final in Rows, Month in Columns, Amount in Values. Faster to build, easier to slice, and you can add a slicer for Account if you're combining several.

Excluding transfers: filter the Final field to exclude "Transfer," or in SUMIFS add Txn[Final],"<>Transfer".

A worked example

Let's say a converted statement has these rows:

Description	Amount
POS PURCHASE SHELL OIL 12345	−52.10
ACH GUSTO PAYROLL 0329	−4,820.00
AMAZON WEB SERVICES AWS.AMAZON	−212.44
AMAZON MKTPLACE PMTS	−38.99
ONLINE TRANSFER TO SAV XXXX1234	−1,000.00
STRIPE TRANSFER	3,145.60

With rules in this order:

Keyword	Category
AMAZON WEB SERVICES	Hosting
AMAZON	Office supplies
SHELL	Fuel
GUSTO	Payroll
TRANSFER TO SAV	Transfer
STRIPE	Sales

The formula assigns Fuel, Payroll, Hosting, Office supplies, Transfer and Sales. Note that "AMAZON WEB SERVICES" wins over "AMAZON" because it's listed first. If you reversed the order, AWS would land in Office supplies. Rule order is the most common source of miscategorization we see.

One caution on "Sales": a payment processor deposit is often net of fees and refunds. If you need gross sales, you'll need the processor's reports. Categorizing the net deposit as "Sales" is a reasonable simplification for a quick view, but it's not how most accountants want it recorded.

Choosing the category list itself

Formulas are only half the job. The other half is deciding which categories exist, and that decision matters more than any formula.

For a household, we like a short list that answers "where does the money go?": Housing, Utilities, Groceries, Dining out, Transport, Insurance, Health, Childcare, Subscriptions, Shopping, Travel, Gifts, Income, Transfer, and Other. Fifteen categories is enough to see patterns without spending all evening deciding whether a pharmacy run was Health or Shopping.

For a small business, use your accountant's chart of accounts if you have one. If you don't, start with the expense lines your tax return or your accountant's template uses, because those are the totals someone will eventually ask for. Common examples include advertising, bank fees, contractors, insurance, meals, office expenses, professional fees, rent, software, travel and utilities. The exact list depends on your business and jurisdiction, so it's worth a short conversation with your accountant before you categorize a year of data. Changing categories later is possible, but it's tedious.

Two principles hold for both: keep categories mutually exclusive (a transaction should obviously belong to one), and keep a catch-all such as "Other" or "Review" so nothing gets forced into a wrong bucket just to be categorized.

Dynamic array tricks for reviewing categories

Microsoft 365's dynamic array functions make review faster.

List every uncategorized payee, once:

=UNIQUE(FILTER(Txn[Description], Txn[Final]="Uncategorized"))

Work down this list adding rules, and it shrinks as you go.

Show the biggest uncategorized items first:

=SORT(FILTER(Txn[[Description]:[Amount]], Txn[Final]="Uncategorized"), 2, 1)

Sorting by amount ascending puts the largest outflows at the top. Big items matter most for accuracy, so handle them first.

Count how many rows each rule catches:

=MAP(Rules[Keyword], LAMBDA(k, SUM(--ISNUMBER(SEARCH(k, Txn[Description])))))

A rule that catches zero rows is either dead or misspelled. A rule that catches hundreds is either very good or too broad, and worth a quick look.

Doing it in Power Query instead

If you load statements through Power Query, you can categorize there too. Merge the transactions query with the rules table using a custom column that tests Text.Contains for each keyword, keep the first match, and expand the category. It's more setup than a formula, but the result refreshes with your data and keeps the workbook light, which helps once you're past tens of thousands of rows. For most people, the worksheet formula is easier to understand and maintain, so we'd only switch when performance becomes a problem. Our Power Query guide covers loading statements this way.

Testing your rules

Before trusting a new set of rules on a year of data, test them on one month you've already categorized by hand. Put the manual categories in a column next to the formula's results and add =[@Manual]=[@Final]. Every FALSE is either a rule to fix or a manual choice to reconsider. It takes ten minutes, and it's the difference between "the formula works" and "we think the formula works."

Common mistakes

Keywords that are too short. "UB" will match "UBER" and also "PUB" and "CLUB." Use distinctive keywords, and consider padding with spaces if needed.

Overlapping rules in the wrong order. Specific before general, every time.

Categorizing transfers as income or expense. Transfers between your own accounts are neither. Give them their own category and exclude them from reports.

Editing formulas row by row. If one row needs something different, use Override. Don't break the column formula.

Not verifying the data first. Categorizing an incomplete statement gives you neat, wrong totals. Check the balance before you start. Our statement balance checker helps.

Where this fits in a bigger workflow

Categorized transactions feed most of what follows: tax prep summaries, expense reports, budgeting and reconciliation. If you're preparing for tax time, our guide on categorizing business expenses from bank statements suggests a category list. If you're importing into accounting software, the categories there come from your chart of accounts, and the software has its own bank rules; Excel rules are most useful for analysis, catch-up projects and any workflow that lives outside accounting software.

Our take

Don't put rules in formulas. Put rules in a table, use one lookup formula, keep an Override column, and grow the rules a little each month. It's a small setup cost that pays off every single month after.

Troubleshooting categorization formulas

Everything comes back as "Uncategorized". Check for extra spaces or different capitalization. SEARCH is case-insensitive but sensitive to hidden characters; clean descriptions with =TRIM(CLEAN(B2)) first.

The wrong rule wins. When several keywords match, the first match in your rules table wins. Put specific keywords ("AMAZON WEB SERVICES") above general ones ("AMAZON").

Formulas are slow. Thousands of rows times hundreds of rules can slow a workbook. Limit ranges, use Tables, or move the matching to Power Query.

Refunds are categorized as income. Combine the keyword match with the sign: a positive amount from a store you normally pay is probably a refund for that category.

New merchants keep appearing. Review uncategorized rows monthly and add rules. After a few months, most rows are covered.

A mini checklist for your rules table

1. One keyword per row, in a Table.
2. Specific keywords above general ones.
3. Category names match your chart of accounts or budget.
4. A default for unmatched rows.
5. Reviewed monthly for new merchants.

A second worked example

A bookkeeper's rules table for a client had grown to 180 rows. Categorization worked, but she noticed "Software" was 20% higher than last year. Filtering showed "APPLE" matching both app subscriptions and an employee's hardware purchase. She split the rule into "APPLE.COM/BILL" for subscriptions and left hardware to manual review. Small rules have big effects at scale; a quick monthly review catches them.

FAQ

What's the best Excel formula to categorize bank transactions?

A lookup against a rules table: INDEX and MATCH with ISNUMBER(SEARCH(...)) to find the first keyword contained in each description. It scales because rules live in a table, not inside the formula.

Can XLOOKUP match partial text?

Yes. Set the match mode to 2 for wildcard matching and wrap keywords in asterisks, for example "*"&Rules[Keyword]&"*", to match keywords anywhere in the description.

Why are some transactions getting the wrong category?

Usually rule order. The formula returns the first match, so a general keyword placed above a specific one will win. Move specific rules higher.

How do I handle one-off exceptions?

Add an Override column and type the category there. A final column uses the override when present, otherwise the formula's result.

Does this work in Google Sheets?

Mostly, yes. Google Sheets supports SEARCH, INDEX, MATCH and array logic. Use ARRAYFORMULA where needed; structured table references work differently, so use regular ranges.

Should transfers between my accounts get a category?

Yes, give them a Transfer category and exclude it from income and expense reports, so money moving between your own accounts isn't counted twice.