How to Categorize Bank Transactions in Excel

Once your transactions are in a spreadsheet, the next job is sorting them into categories like groceries, rent, fuel, and dining. Categorizing by hand works for a handful of rows, but it gets tedious fast. This guide walks through how to categorize bank transactions in Excel — starting with a category list, then automating the sorting with keyword matching and formulas.

If you do not yet have your data in a spreadsheet, start by getting the transactions out of your PDF — see how to convert a bank statement to Excel or use the free bank statement converter.

Step 1: Set up your transaction sheet

A clean sheet makes everything downstream easier. At a minimum you want one row per transaction and columns for:

• Date — the posting or transaction date
• Description — the raw merchant text from the bank
• Amount — the debit or credit value
• Category — the column you are about to fill

Keep the raw description exactly as the bank wrote it. Merchant text such as SQ *BLUE BOTTLE or AMZN MKTP US*1A2B3 is messy, but it is also what your formulas will read to decide the category, so do not clean it up yet.

Step 2: Build a category list

Decide on a fixed set of categories before you start sorting. A short, consistent list is easier to total later than a sprawling one. A common starting set:

• Groceries
• Dining
• Transport
• Utilities
• Rent or Mortgage
• Shopping
• Subscriptions
• Income
• Other

Put this list on a separate worksheet — call it something like Categories. Keeping it on its own sheet lets you reference it from a drop-down and reuse it across formulas without cluttering your main data.

To turn a column into a drop-down, select the Category cells and use Excel's Data Validation feature with the list set to your category range. Now every category is picked from the same controlled list, which prevents typos like "Groceries" versus "Grocery" from splitting your totals.

Step 3: Map merchant text to categories

Auto-categorization works by matching words in the messy description to a category. The cleanest way to do this is a lookup table: a two-column list where the first column holds a keyword and the second holds the category it maps to.

Keyword	Category
BLUE BOTTLE	Dining
WHOLEFDS	Groceries
SHELL	Transport
NETFLIX	Subscriptions
AMZN	Shopping

Build this table on your Categories sheet. Start small — add a few rules, run them, then add more for whatever lands in "Other." Over a month or two the table grows to cover most of your recurring merchants.

Step 4: Auto-categorize with a formula

There are two common formula approaches. Both read the raw description and return a category.

Keyword search with IF and SEARCH

For a small rule set, nested logic is readable. The idea: check whether a keyword appears anywhere in the description, and if so, return its category. Excel's SEARCH function finds a substring inside a cell and ignores case. Wrap it in ISNUMBER so each test returns a clean TRUE/FALSE: SEARCH returns a number (the match position) when the keyword is present and an error when it is absent, and ISNUMBER turns that into TRUE only on a real match. That lets each keyword be tested independently inside nested IFs.

Assuming the description is in B2, the structure looks like:

=IF(ISNUMBER(SEARCH("WHOLEFDS",B2)),"Groceries",IF(ISNUMBER(SEARCH("SHELL",B2)),"Fuel","Other"))

Each IF is tried in order: if "WHOLEFDS" is found the cell is tagged "Groceries"; otherwise Excel moves on to test "SHELL" and tags "Fuel"; if neither matches it falls through to "Other". Because ISNUMBER(SEARCH(...)) evaluates to TRUE/FALSE rather than erroring out, a missing first keyword no longer short-circuits the remaining tests. The same logic reads more cleanly with IFS:

=IFS(ISNUMBER(SEARCH("WHOLEFDS",B2)),"Groceries",ISNUMBER(SEARCH("SHELL",B2)),"Fuel",TRUE,"Other")

This is fine for five or ten rules. Past that, nested IFs become hard to read and edit.

Lookup table with VLOOKUP

For a longer rule set, drive the categories from your keyword table instead of hard-coding them in the formula. VLOOKUP searches the first column of a table and returns a value from another column in the same row. Paired with a function that pulls the keyword out of the description, it lets you add new rules just by adding rows to the table — no formula edits.

A maintainable pattern is to keep one helper column that extracts the matching keyword, then a VLOOKUP against your two-column table to return the category. Because the rules live in the table, anyone can extend the system without touching formulas. (Newer Excel versions also offer XLOOKUP, which works the same way and does not require the lookup column to be on the left.)

Step 5: Validate the results

No keyword system catches everything, so review before you trust the totals.

• Sort or filter by category and scan each group. A single grocery run filed under "Transport" stands out quickly when similar rows sit together.
• Check the "Other" bucket. Anything left uncategorized is a candidate for a new keyword rule. Add it to the table and the formula picks it up on the next refresh.
• Total each category with SUMIF, which adds up amounts that match a given category. The category totals should add up to the grand total of all transactions — if they do not, a row was missed or double-counted.
• Watch for partial-match collisions. A keyword like PARK could match both a parking garage and a grocery store named "Park Market." Make keywords specific enough to avoid catching the wrong merchant.

Keep it repeatable

The real payoff comes month to month. Once your keyword table covers your regular merchants, a new statement only needs a few new rules at most. Paste in the next month's rows, drag the formula down, and the bulk of the work is done.

For more on structuring the wider workflow — monthly tabs, summaries, and trend tracking — see our guide to expense tracking in Excel.