Bank Statement Categorization: A Spreadsheet Method

Quick Answer: Get the statement into a spreadsheet with one row per transaction, write a short category list, then build a rules table of keywords and categories and let a lookup formula label every row it recognizes. Treat transfers, card payments and refunds as their own types rather than income or spending, and review the "Uncategorized" rows by hand at the end.

Bank statement categorization is the job of giving every transaction on a statement a label, such as Rent, Groceries, Software or Transfer, so the totals mean something. You can do it outside any accounting software with a spreadsheet, a category list of fifteen to thirty entries, and a keyword rules table. After the first month, most rows categorize themselves and you only check the leftovers.

Which transactions need special handling?

Most rows are plain purchases. The ones below cause almost every wrong total, so decide how you treat each type before you write a single rule.

Step 1: Get one clean row per transaction

Categorizing only works on a table where each row is one transaction with a date, a description and one signed amount. You have a few ways to get there:

• The bank's own CSV export, if it covers the period you need. This is the best source when available.
• The statement PDF, if the export only goes back a few months. Excel's Get Data from PDF can work on simple layouts. QuickBankConvert is built for statement layouts and returns date, description, a suggested category and a signed amount per row as CSV or Excel; the PDF to Excel page shows the output columns.
• Several months at once: put them in one sheet and add an Account column if they come from different accounts. Our guide on merging multiple CSV statements covers lining up columns.

Make money out negative and money in positive. If your source has separate debit and credit columns, add a column that combines them, for example =C2-D2, so every rule works on one number.

Step 2: Write a category list you will actually use

Fewer categories, applied consistently, beat a detailed list applied loosely. A workable starting point:

• Household: Housing, Utilities, Groceries, Dining, Transport, Insurance, Health, Subscriptions, Shopping, Personal care, Gifts and donations, Fees and interest.
• Small business: Advertising, Software, Contractors, Office supplies, Travel, Meals, Bank fees, Professional services, Rent, Utilities, Inventory, Sales income.
• Always add: Transfer, Card payment, Refund (or use negative amounts in the purchase category), and Uncategorized.

If the end use is a tax return, align the business list with the lines on the form you file. The IRS overview of deducting business expenses explains what counts as ordinary and necessary, and our list of deduction categories for bank statements maps common merchants to those headings.

Step 3: Build a keyword rules table

On a second sheet, make a table named Rules with two columns: Keyword and Category. Each row is a piece of text that appears in a transaction description:

Keep keywords short but specific. "AMZN" catches every Amazon purchase; "AMZN MKTP" and "AMAZON WEB SERVICES" separate marketplace shopping from cloud hosting. Put the specific keywords above the general ones, because the formula below returns the first hit.

Step 4: Let a formula apply the rules

In Excel for Microsoft 365, Excel 2021 or Excel 2024, add a Category column next to the description and enter:

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

SEARCH looks for each keyword inside the description and ignores case. ISNUMBER turns the hits into TRUE or FALSE, and XLOOKUP returns the category beside the first TRUE, or "Uncategorized" when nothing matches. Microsoft's XLOOKUP reference notes that the function is not available in Excel 2016 or 2019; on those versions use INDEX and MATCH with the same ISNUMBER(SEARCH()) test, entered as an array formula.

In Google Sheets, the same logic works inside ARRAYFORMULA:

=ARRAYFORMULA(IFERROR(INDEX(Rules!B2:B60, MATCH(TRUE, ISNUMBER(SEARCH(Rules!A2:A60, B2)), 0)), "Uncategorized"))

Two cautions. Do not leave blank rows inside the keyword range: SEARCH treats an empty keyword as a match for everything, so every row would get the first blank row's category. And if you need patterns rather than plain text, for example "UBER" but not "UBER EATS", Google Sheets' REGEXMATCH lets you write a regular expression for that rule.

Step 5: Review the remainder and teach the rules

Filter the Category column to Uncategorized. For each row:

1. Work out what it was, using receipts, your email, or the merchant's website.
2. If it will recur, add a keyword to the Rules table. Every new rule recategorizes the whole sheet immediately.
3. If it is a one-off, type the category over the formula in that cell, and colour the cell so you know it is a manual override.
4. For payment apps and cash, add a Note column and record who or what the payment was for.

After two or three months the Uncategorized share usually drops to a handful of rows, because most spending comes from the same merchants each month.

How do you check that the categories add up?

Build a pivot table with Category in rows and Amount in values. Then check three things:

• The grand total, transfers included, equals the closing balance minus the opening balance printed on the statement. If it does not, a row is missing or duplicated. When the sheet came from a converter, this is the quickest proof that nothing was dropped; QuickBankConvert shows both balances above its preview for exactly this comparison.
• Transfer nets to roughly zero across all your accounts when you combine them. A large one-sided total means one account's statement is missing.
• Refunds sit inside the categories they reverse, so Shopping is net of returns.

For a ready-made layout that rolls these totals into a monthly budget, see building a budget spreadsheet from bank statements.

When is accounting software the better tool?

A spreadsheet is ideal for personal budgets, one-off tax prep, and reviewing a period before you hand it to an accountant. Once you need invoices, bills, payroll or reconciled books, the categorization belongs inside accounting software, where rules work the same way but the results flow into reports. QuickBooks Online's version is covered in how to categorize bank transactions in QuickBooks Online. Your rules table is not wasted either way: the keywords you have collected are exactly what you type into the software's rule screen.

Related guides

• Track business expenses from bank statements
• Bank statement deduction categories
• Pivot tables for bank statement data
• Credit card statement spending analysis