How to Categorize Bank Transactions in Excel

Last updated July 2026.

To categorize bank transactions in Excel, put each transaction in its own row, add a Category column next to the description, then auto-fill it with a keyword lookup that reads each description and returns the matching category. Finish by totaling each category with a PivotTable or a SUMIF. The whole thing runs off one small rules table you build once and reuse every month.

Done by hand, categorizing a statement is slow and inconsistent. Done with a keyword rule, Excel tags hundreds of lines in a second and tags them the same way every time. Here is the exact setup, with the formulas.

Step 1: Get every transaction into its own row

You cannot categorize what is not in a clean grid. Each row should hold at least a date, a description, and an amount, with debits and credits in a single signed Amount column (negatives for money out) or split into two columns. If your data came from a PDF statement, get it into that shape first: you can categorize transactions from a bank statement automatically, or convert the PDF to rows with a bank statement converter and paste them into Excel. Keep the account and month on the sheet so you never mix two statements together.

Step 2: Add a Category column

Insert a column to the right of the description and name it Category. Decide your categories before you start typing, because a consistent list is what makes the totals meaningful. Most US small businesses map their categories to the IRS Schedule C expense lines (advertising, contract labor, supplies, meals, utilities, and so on), which keeps your spreadsheet and your tax return speaking the same language. The full list is in small business expense categories for Schedule C. Type the category for a handful of rows by hand first so you can see the pattern before you automate it.

Step 3: Auto-assign categories with a keyword lookup

The trick is to match a word in the transaction description to a category. Build a small two-column table on a second sheet: one column of keywords (uber, aws, payroll, starbucks, verizon) and one column of the category each keyword belongs to. Then, in your Category column, use a formula that finds the first keyword that appears in the description and returns its category. With a rules table named Rules, this formula does it:

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

SEARCH looks for each keyword inside the description, ISNUMBER turns a hit into TRUE, MATCH finds the first hit, and INDEX returns that keyword's category. Anything with no match falls through to Uncategorized so nothing is silently dropped. In Excel 2021 and Microsoft 365 this works as a normal formula; in older versions, enter it with Ctrl+Shift+Enter. If you prefer something simpler for a short list, a nested IF works too:

=IF(ISNUMBER(SEARCH("uber",[@Description])),"Travel",
 IF(ISNUMBER(SEARCH("payroll",[@Description])),"Payroll",
 "Other"))

The rules table is better than nested IFs once you pass a dozen categories, because you add a new rule by typing one row instead of editing a formula.

Step 4: Total each category

Once every row has a category, summarize it. The cleanest way is a PivotTable: select your data, insert a PivotTable, drag Category to Rows and Amount to Values, and you get a one-line total per category that refreshes when you add data. If you want a total in a cell instead, use SUMIF:

=SUMIF(Category_column, "Meals", Amount_column)

That gives you the monthly spend per category, which is what feeds a profit and loss view, a budget, or your tax prep. Sort the Uncategorized rows to the top each month and add a keyword rule for anything that shows up more than once, so your rules table gets smarter over time.

Common categories to start with

For a small business, a workable starter list is: income, cost of goods sold, advertising, software and subscriptions, contract labor, meals, travel, office supplies, rent, utilities, bank and merchant fees, taxes, owner's draw, and transfers. Keep transfers and owner's draws separate from real expenses so they do not inflate your spending. If you are splitting a mixed account, the method for pulling out personal charges is in how to separate personal and business expenses.

When to stop doing it in Excel

The keyword-rule sheet is a great free option, and it is worth building once. But it breaks down when you have several accounts, credit cards, and a few hundred transactions a month, because you are still pasting, checking, and maintaining formulas. At that point most businesses move the tagging into their books. The same categorization happens automatically inside QuickBooks Online or Xero using bank rules, or you can hand it to dedicated expense management software that reads each transaction and assigns the category for you. The Excel method is best when you want full control and a paper trail you own.

Frequently asked questions

How do I categorize bank transactions in Excel automatically?

Build a two-column rules table of keywords and their categories, then in your Category column use an INDEX and MATCH formula wrapped around ISNUMBER(SEARCH(...)) to find the first keyword that appears in each description and return its category. It tags every row in one pass, and you add new rules by typing a row rather than editing a formula.

What categories should I use for bank transactions?

Use a consistent list that matches how you report. For US businesses, the IRS Schedule C expense lines (advertising, supplies, meals, utilities, contract labor, and so on) are a solid default because your spreadsheet then lines up with your tax return. Keep transfers and owner's draws in their own categories so they do not count as expenses.

Can Excel categorize transactions by description?

Yes. Excel cannot guess categories on its own, but a keyword lookup does it reliably: you list the words that appear in your descriptions and the category each maps to, and a SEARCH-based formula matches them. It is rule-based rather than AI, so it is predictable and easy to audit, which is exactly what you want for bookkeeping.

Is it better to categorize in Excel or in accounting software?

Excel is best when you want full control, a one-off cleanup, or a records you own outright, and it costs nothing. Accounting software is better once you have multiple accounts and hundreds of monthly transactions, because bank rules apply automatically and the categorized data flows straight into your reports and tax filing without copy and paste.