How to Categorize Bank Transactions and Expenses in Excel: Formulas, Rules and Pivot Tables

Excel is still the most common tool for categorising bank transactions, especially for freelancers, small businesses, personal budgets, and accountants doing one-off analysis or catch-up work. It is flexible, transparent and free for anyone who already has Microsoft 365. With the right structure, a year of transactions can be categorised in minutes rather than hours.

This tutorial builds a complete categorisation workbook step by step: getting the data in, structuring it as a table, creating a rules table, writing the lookup formula, handling exceptions, and summarising the results. It works in Microsoft 365 Excel; notes explain alternatives for older versions and Google Sheets.

Step 1: Get clean transaction data into Excel

Categorisation is only as good as the data underneath it. You need, at minimum, a date, a description and a signed amount (money in positive, money out negative) for every transaction.

From your bank's CSV export: download the CSV for the period, open it in Excel, and check that dates are real dates (right-aligned) and amounts are numbers.

From PDF statements: convert them first. Copy-paste rarely produces clean columns; a statement converter such as our bank statement to Excel tool exports a ready-made table and confirms the rows reproduce the statement's balances. Our guide to converting a bank statement PDF to Excel compares manual methods if you prefer.

Multiple accounts: stack them into one sheet with an extra "Account" column, so one set of rules covers everything.

Whatever the source, make the sign convention consistent before you start. If the data has separate debit and credit columns, create a single amount column with =[@Credit]-[@Debit].

Step 2: Turn the range into an Excel table

Click anywhere in the data and press Ctrl+T (Cmd+T on a Mac). Confirm the table has headers, then name it on the Table Design tab, for example Tx.

Avoid merged cells, blank rows and subtotal rows inside the data; each row should be exactly one transaction. If the source contains an opening balance line, move it out of the table.

Tables have three advantages for this job:

• Formulas fill down automatically when you add rows.
• Structured references like [@Description] are easier to read than B2.
• PivotTables and charts based on the table expand with it.

Add these columns to the right of the data: Month, Rule category, Override, Category.

For the month column, use =TEXT([@Date],"yyyy-mm"), which produces sortable values like "2026-03".

Step 3: Build a rules table

On a new sheet called "Rules", create a two-column table named Rules with headers Keyword and Category. Each row is one rule. Keywords are fragments of text that appear in descriptions.

Keyword	Category
GOOGLE ADS	Advertising
META ADS	Advertising
ADOBE	Software
MICROSOFT	Software
ELM STREET PROPS	Rent
CITY UTILITIES	Utilities
SVC CHG	Bank fees
MAINT FEE	Bank fees
INTEREST PAID	Interest income
PAYROLL	Wages
TRANSFER TO SAV	Transfer
ATM	Cash withdrawal
UBER	Travel
SHELL	Vehicle fuel

Tips for good keywords:

• Use the shortest text that is unique to the payee. "ADOBE" is better than the full descriptor, which may change month to month.
• Use bank codes for transaction types. Our bank statement abbreviations list explains codes like SVC CHG, DD and POS.
• Put more specific keywords above general ones if you use a "first match wins" formula. "AMAZON WEB SERVICES" should sit above "AMAZON".
• Keep categories consistent with your chart of accounts. If you are not sure what categories to use, see how to categorise business expenses.

Step 4: The categorisation formula

The goal is a formula that looks through every keyword in the rules table and returns the category of the first keyword found anywhere in the description, ignoring case.

Microsoft 365 version (XLOOKUP + SEARCH)

In the Rule category column:

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

How it works:

• SEARCH(Rules[Keyword], [@Description]) checks every keyword against the description and returns a position number where found, or an error where not.
• ISNUMBER(...) converts that into an array of TRUE and FALSE values.
• XLOOKUP(TRUE, ..., Rules[Category]) returns the category for the first TRUE, which is the first matching keyword in the rules table order.
• The last argument supplies a default when nothing matches.

SEARCH is case-insensitive, so "Adobe", "ADOBE" and "adobe" all match. If you need case-sensitive matching, use FIND instead.

Older Excel versions (LOOKUP trick)

Without XLOOKUP, this classic formula does a similar job:

=IFERROR(LOOKUP(2^15, SEARCH(Rules[Keyword], [@Description]), Rules[Category]), "Uncategorised")

LOOKUP searches for a number larger than any possible position (2^15 = 32,768) and returns the category for the last keyword that matched, not the first. Order your rules with the most specific keywords at the bottom when using this version.

Google Sheets

Google Sheets supports XLOOKUP in current versions. Use regular cell ranges instead of structured references, and wrap with ARRAYFORMULA if you want one formula to fill the column.

Step 5: Add a manual override

Rules handle the routine; people handle the exceptions. Add a drop-down to the Override column so you can set a category by hand without breaking formulas.

1. On the Rules sheet, create a second table named Categories with a single column listing every allowed category.
2. Select the Override column in the Tx table.
3. Go to Data > Data Validation, choose List, and enter the source =INDIRECT("Categories[Category]") (or select the range directly).

Restricting overrides to a list prevents near-duplicate categories such as "Software" and "Softwares" from creeping in.

Then the final Category column is simply:

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

This design keeps an audit trail: you can always see what the rule suggested and where someone chose differently.

Step 6: Handle splits, transfers and refunds

A few transaction types need special treatment regardless of the formula.

Split transactions. A marketplace order containing office supplies and a personal item, or a loan payment containing principal and interest, belongs in more than one category. Duplicate the row, split the amount across the copies, and add a "Split" note. Make sure the copies still sum to the original amount.

Transfers. Moving money between your own accounts or paying a credit card is not income or expense. Categorise these as "Transfer" and exclude them from income and expense summaries.

Refunds. A refund should reduce the original category, not appear as income. If the rules table sends a merchant to "Office supplies", the refund will land in the same category automatically because the payee is the same, with a positive amount that offsets the earlier spending.

Card processor deposits. Payouts from Stripe, PayPal or Square are net of fees. If you need gross sales and fees, use the processor's report to split them.

Step 7: Check what is still uncategorised

Filter the Category column for "Uncategorised" and work through the list. For each one, either add a new rule (if the payee will recur) or set an override (if it is a one-off).

To see which payees cause the most uncategorised lines, create a quick count:

=COUNTIFS(Tx[Category], "Uncategorised")

and a PivotTable of uncategorised rows by description. Tackling the top ten descriptions often clears most of the backlog.

Highlighting helps too. Select the Category column, choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, and enter "Uncategorised" with a red fill.

Step 8: Summarise with SUMIFS

For a fixed monthly summary layout, build a grid with categories down the side and months across the top, then use:

=SUMIFS(Tx[Amount], Tx[Category], $A2, Tx[Month], B$1)

Money out will appear as negative numbers. If you prefer positive expense figures, wrap the formula in a minus sign or use ABS for expense rows.

Add a total row and column, and a check cell that compares the grid total with =SUM(Tx[Amount]) excluding transfers. If the check is not zero, some transactions fall outside the categories in the grid.

Step 9: Summarise with a PivotTable

PivotTables are faster to build and easier to change:

1. Click inside the Tx table and choose Insert > PivotTable.
2. Drag Category to Rows, Month to Columns and Amount to Values.
3. Add Account to Filters if you combined several accounts.
4. Use the Category filter to exclude "Transfer".

From here you can add a PivotChart for monthly spending by category, or sort categories by total to see where the money goes. Refresh the PivotTable after adding transactions (Data > Refresh All).

Step 10: Reuse the workbook every month

The real payoff comes in month two. Paste or append new transactions into the Tx table, and formulas, overrides and PivotTables update. Each month you only deal with new payees.

A few habits keep the workbook healthy:

• Never delete the rules sheet; it is the most valuable part of the file.
• Add rules, not overrides, for anything that recurs.
• Review the rules table quarterly, removing rules for suppliers you no longer use.
• Keep a copy of the raw data on a separate sheet before editing.
• Date-stamp the workbook when you finish a month, for example by saving a copy named with the period, so you can return to the state you reported.
• Reconcile the totals to the bank statements each month. Our reconciliation guide shows how.

Adding a tax line and a business-use column

If the workbook feeds a tax return, two extra columns save a lot of year-end work.

Tax line. Add a third column to the Categories table that maps each category to the line on your tax form, for example "Software" to "Other expenses" or "Advertising" to the advertising line. A simple XLOOKUP from Category to that column gives every transaction a tax line, and a PivotTable by tax line produces the numbers your accountant needs.

Business-use percentage. For mixed-use categories such as phone, internet or vehicle costs, add a percentage column to the Categories table (100% for purely business categories, 0% for personal, and your documented percentage for mixed ones). Then calculate a deductible amount with [@Amount] multiplied by the looked-up percentage. Keep the evidence for the percentage, such as a mileage log, alongside the workbook.

These columns also make it obvious where judgement was applied, which is helpful if anyone later asks how a figure was calculated.

Using the same workbook for a personal budget

The structure works equally well for household finances. Replace business categories with budget categories such as Housing, Groceries, Transport, Utilities, Insurance, Subscriptions, Eating out, Savings transfers and Income. Then add a Budget table with a monthly target per category and a variance column in the summary:

• Actual spending per category per month from the PivotTable or SUMIFS grid.
• Target from the Budget table.
• Variance as target minus actual, with conditional formatting to highlight overspending.

Personal statements often contain many small card payments with cryptic merchant descriptors. Building rules for the twenty payees you use most will usually categorise the large majority of lines automatically.

Optional: Power Query for repeat imports

If you receive a CSV from the same bank every month, Power Query can automate the cleaning steps: removing header rows, fixing dates, combining debit and credit columns and appending new files from a folder. Point a query at a folder of monthly CSVs and refresh to pull them all into one table. Your categorisation formulas then sit in columns next to the query output.

Power Query's merge feature can also perform the categorisation itself with a fuzzy match against the rules table, but formula-based keyword matching is easier to understand and audit for most people.

Worked example

Suppose the Tx table contains:

Date	Description	Amount	Rule category
2026-03-02	GOOGLE ADS 4421 CA	-320.00	Advertising
2026-03-04	ELM STREET PROPS RENT	-1,850.00	Rent
2026-03-05	ADOBE *CREATIVE CLD	-59.99	Software
2026-03-09	ONLINE TRANSFER TO SAV 7781	-2,000.00	Transfer
2026-03-12	ATM W/D 5TH AVE	-100.00	Cash withdrawal
2026-03-14	BISTRO 21 NEW YORK	-64.80	Uncategorised
2026-03-28	MONTHLY SVC CHG	-15.00	Bank fees

The rules caught six of seven lines. The bistro charge was a client lunch, so you set the Override to "Meals" and, if you eat there with clients often, add a rule for "BISTRO 21". Note that "TRANSFER TO SAV" matched inside "ONLINE TRANSFER TO SAV 7781" because SEARCH looks anywhere in the text.

The resulting PivotTable for March, excluding transfers, shows Rent 1,850.00, Advertising 320.00, Cash withdrawal 100.00, Meals 64.80, Software 59.99 and Bank fees 15.00.

Common problems and fixes

Problem	Cause	Fix
Everything is "Uncategorised"	Rules table not referenced correctly, or keywords have trailing spaces	Check table names; use TRIM on keywords
Wrong category for some payees	A general keyword matches before a specific one	Reorder rules (specific first for XLOOKUP, last for LOOKUP)
Short keywords match too much	"ATM" also matches "ATMOSPHERE CAFE"	Use longer keywords such as "ATM W/D" or include a space
Formulas slow on large files	Thousands of rows times hundreds of rules	Convert finished months to values, or use Power Query
Dates group incorrectly	Dates stored as text	Convert with Text to Columns before building the Month column
Totals don't match the statement	Missing rows or wrong signs	Reconcile the source data first

When to move beyond Excel

Excel works well up to a few thousand transactions a year and a handful of accounts. Consider accounting software when you need invoicing, receivables, payables, multi-user access or tax filing integration. The categories and rules you built in Excel transfer directly; most accounting systems support similar bank rules. Our guides for QuickBooks and Xero show how to import statement data.

Frequently asked questions

How do I automatically categorise bank transactions in Excel?

Create a rules table of keywords and categories, then use a formula such as =XLOOKUP(TRUE, ISNUMBER(SEARCH(Rules[Keyword],[@Description])), Rules[Category], "Uncategorised") in your transactions table. Every new transaction is categorised as soon as it is added.

Can I categorise expenses in Excel without XLOOKUP?

Yes. Use =IFERROR(LOOKUP(2^15, SEARCH(Rules[Keyword],[@Description]), Rules[Category]), "Uncategorised"). It returns the last matching keyword, so put the most specific keywords at the bottom of the rules table.

How do I make a category drop-down list in Excel?

Create a list of allowed categories, select the column, go to Data > Data Validation, choose List and point the source at the category list. Use it in an override column so manual choices take priority over formulas.

What is the best way to summarise categorised transactions?

A PivotTable with Category in rows, Month in columns and Amount in values is the quickest. SUMIFS formulas suit fixed report layouts such as a monthly budget template.

How do I handle transactions that belong to two categories?

Split the row into two or more rows whose amounts add up to the original, give each its own category, and add a note so the split is clear later.

Is it safe to keep bank transactions in Excel?

It can be, if the workbook is stored securely and protected with a password or access controls. Bank data is sensitive; avoid emailing unprotected workbooks and keep backups. Our security page explains how we handle statements during conversion.

Summary

The most reliable Excel setup is a transactions table, a separate rules table, a SEARCH-based lookup formula, an override column for exceptions and a PivotTable for summaries. Build it once and each month only new payees need attention. If your statements arrive as PDFs, convert them to Excel free with every balance checked, and spend your time on decisions rather than data entry. For trend analysis, continue with cash flow analysis from bank statements.