Categorizing is the part of the books where the time really goes. Once a month of transactions is sitting in Excel, every line still needs a category before it means anything: which payments are software, which are payroll, which are owner draws. Done by hand, that is a long scroll and a lot of clicking. Done with a small lookup table and one formula, Excel can code most of a statement for you and leave only the oddities to sort out.
The catch is that you cannot categorize what is not in the spreadsheet yet. If the statement is a PDF, the transactions have to land in real columns first, with the dates, descriptions, and amounts each in their own cell and the amounts as true numbers. The cleanest way to get there is to convert the bank statement PDF to Excel with a structure-aware converter, or for a whole month of client files, the workflow built for PDF to Excel for bookkeepers. Once the rows are clean, everything below works.
How do I categorize bank transactions in Excel?
Add a Category column next to your transactions, build a small lookup table that maps keywords from the Description field to your categories, then use a formula to fill the Category column automatically. After that, sort or filter the few rows the formula left blank and assign them by hand. It is four steps: get clean rows, define your categories, map keywords, and let a formula do the bulk. Most months end up 80 to 90 percent auto-coded, with a short tail to finish manually.
Start by deciding the category list itself. For a real set of books, that list should match the chart of accounts you post to in QuickBooks or Xero, so the work transfers cleanly later. Keep it to the categories you actually use, not every account that exists.
Can Excel automatically categorize bank transactions?
Yes. Excel has no built-in transaction categorizer, but a keyword lookup table plus a lookup formula gets you most of the way automatically. The idea is simple: bank descriptions repeat. The same vendors, the same ACH labels, the same card processors show up month after month. If you tell Excel that any description containing \"Stripe\" is Income and anything containing \"Staples\" is Office Supplies, a single formula can read each Description and stamp the right category. You are teaching it the patterns once and reusing them every month.
This is the same logic accounting software uses for its bank rules, just kept in a spreadsheet you control. The more you maintain the keyword list, the more of each statement codes itself.
What formula categorizes transactions by description in Excel?
The most reliable approach matches a list of keywords against each description and returns the first category that hits. In Excel 365 or 2021, this formula does it, assuming your keyword and category columns live in a table named CategoryMap and the description is in column B:
=IFERROR(XLOOKUP(TRUE, ISNUMBER(SEARCH(CategoryMap[Keyword], B2)), CategoryMap[Category]), \"Uncategorized\")
SEARCH looks for each keyword inside the description, ISNUMBER turns a hit into TRUE, and XLOOKUP returns the category for the first TRUE. On older Excel, an INDEX and MATCH version with wildcards handles partial vendor names: =IFERROR(INDEX(CategoryMap[Category], MATCH(\"*\"&[keyword]&\"*\", B2, 0)), \"Uncategorized\"), or for exact labels a plain VLOOKUP with IFERROR works. Wrapping it in IFERROR is what keeps a blank or unmatched row from throwing an error, marking it Uncategorized instead so you can spot it.
How do I build a keyword to category lookup table?
On a separate sheet, make a two column table: Keyword and Category. In the Keyword column put a distinctive fragment of the description, not the whole thing, so \"AMZN\" catches every Amazon variant and \"Gusto\" catches the payroll run whatever reference number follows it. In the Category column put the matching account. Format it as an Excel table (Ctrl+T) and name it, so your formula keeps working as the list grows. A typical starter table maps card processors and payout services to Income, software vendors to Software, fuel and airlines to Travel, and ACH labels for your payroll provider to Payroll. Order matters slightly: put more specific keywords above broad ones, since the formula returns the first match.
How do I total spending by category in Excel?
Once the Category column is filled, a PivotTable totals it in seconds. Select your transaction range, insert a PivotTable, drag Category to Rows and Amount to Values, and you have spend and income by category for the month with no extra formulas. If you would rather keep it inline, SUMIF does the same one category at a time: =SUMIF(CategoryColumn, \"Software\", AmountColumn). The PivotTable is faster for a full picture and refreshes when you add rows, which is why it is the standard way bookkeepers check a month before posting it. This same total is also your reconciliation check against the statement, the way the reconcile a bank statement in Excel guide lays out.
How do I handle uncategorized transactions?
Filter the Category column for Uncategorized and work down the short list. Most will be a new vendor the lookup table has not learned yet. When you see the same payee twice, do not just type the category, add it to the keyword table so it codes itself next month. That habit is the whole trick: the table you maintain a little each month is what turns categorizing from an hour of clicking into a few minutes of review. Genuinely one-off items you can code directly in the cell without touching the table.
Should I categorize in Excel or in QuickBooks?
It depends on where the books live. If you post to QuickBooks or Xero, doing the heavy categorizing in Excel first and importing a clean, pre-coded file is often faster than clicking through hundreds of rows in the bank feed, especially for catch-up work or an account with no live feed. Save the categorized sheet as CSV and, for QuickBooks, turn it into a Web Connect file with a CSV to QBO converter so it imports with categories intact. The same applies to expense work: if you are coding card spend, digitize the paper first with a receipt data extraction tool so the line items arrive as data, not images. For a one-off statement you just need to read, categorizing in Excel and stopping there is perfectly fine. The full QuickBooks path is covered in converting PDF to Excel for QuickBooks.
However you finish, the work only flows if the transactions start as clean rows. Convert the statement with the accurate PDF to Excel converter at the top of this page, check the total foots, then categorize with confidence that the numbers underneath are right.