How to categorize bank transactions in Excel

Categorizing turns a statement into answers: how much went to groceries, fuel, software or rent. Budgeting apps do it by connecting to your bank. If you would rather not connect an app, or you need months an app cannot reach, a keyword table in Excel does the same job, and you can read every rule it applies.

Step 1: get the transactions into Excel

  1. Open the StatementLocal converter and drop the statement PDF on the page. The file is read inside your browser and is never uploaded.
  2. Check that the verdict reads Reconciled, so no amount is missing before you start adding up.
  3. Download Excel (.xlsx). The sheet is named Transactions, with Description in column B and Amount in column C.

Step 2: build a keyword table

  1. Add a sheet named Rules. In column A, list words that appear in the descriptions; in column B, the category each word means.
  2. Use short, distinctive keywords: a store name, or a word your bank always prints for a kind of payment, such as PAYROLL or ATM.
  3. Keep the list to filled rows, starting in row 2. A blank keyword matches every description.

Step 3: one formula for every row

On the Transactions sheet, head column E Category, enter this formula in E2 and fill it down:

=IFERROR(LOOKUP(2^15,SEARCH(Rules!$A$2:$A$6,B2),Rules!$B$2:$B$6),"Uncategorized")

SEARCH looks for every keyword in the description, ignoring upper and lower case. LOOKUP returns the category of the last keyword in the list that matched, so put broad keywords near the top and specific ones below them. A description that matches no keyword reads Uncategorized. When you add keywords, extend both ranges to the new last row.

Step 4: total each category

On a summary sheet, list the categories in column A and enter =SUMIF(Transactions!E:E,A2,Transactions!C:C) in B2, then fill down. Money out is negative, so spending totals come out negative. Filter column E for Uncategorized, add keywords for what you find, and every row that contains a new keyword is categorized at once.

A worked example

The Rules sheet holds five keywords: MARKET for Groceries, FUEL for Fuel, PAYROLL for Income, WATER for Utilities and ATM for Cash. The statement has seven rows:

DescriptionAmountCategory
CORNER MARKET #12-86.40Groceries
QUICKFILL FUEL 0391-41.25Fuel
CORNER MARKET #12-23.10Groceries
ACME CO PAYROLL1850.00Income
CITY WATER AUTOPAY-58.00Utilities
ATM WITHDRAWAL-60.00Cash
BOOKSHOP ONLINE-19.99Uncategorized

The summary reads Groceries -109.50, Fuel -41.25, Income 1,850.00, Utilities -58.00 and Cash -60.00, with one row of -19.99 still Uncategorized. Add BOOKSHOP to row 7 of the Rules sheet with the category Books, extend both ranges to row 7, and that row is categorized too. All the category totals together come to 1,561.26, the same as the sum of column C, which is how you know no row was dropped or counted twice.

Limits of this method

Try it on one of your statements

Open the StatementLocal converter and drop one statement PDF on the page. Converting and previewing are always free, and you can download 3 statements a day free, as CSV, Excel or QuickBooks CSV. The file is read inside your browser and is never uploaded.

What has and has not been tested

We tested StatementLocal on 200 synthetic (made-up) statements across 10 common US statement layouts, built by our own generator. Every statement it could not verify was flagged with Off by or Cannot check, never shown as Reconciled. We have not tested it on every bank's real statements, and we quote no accuracy figure for them.

Related guides

StatementLocal is not affiliated with any bank, card issuer or software company named on this page. Names are used only to say what the steps are for.

Need more than 3 statements a day? Buy the 30-day pass for $9 or Buy Lifetime for $39. One-time payments, taken by Stripe. Sold to US customers only.

Use Chrome? The same converter is also a Chrome extension: Add to Chrome from the Chrome Web Store.