How to Categorize Bank Transactions in Excel After Converting (Complete System)

Once you've converted your bank statement PDF to CSV or Excel, the next step is categorization — assigning each transaction to a meaningful category like "Office Supplies," "Payroll," "Meals," or "Revenue." Without categorization, a spreadsheet of transactions is just data. With it, you have a cash flow picture, a tax deduction ledger, and a budget tracking system.

This guide builds a complete categorization system in Excel — from the initial workbook setup to a VLOOKUP formula that automatically categorizes known merchants, to a pivot table that shows spending by category instantly.

Step 1: Set Up Your Workbook Structure

A well-organized workbook makes categorization and reporting significantly easier. Use this two-sheet structure:

• Sheet 1: "Transactions" — Your converted bank statement data. Columns: A=Date, B=Description, C=Amount, D=Category (new column you'll add), E=Notes (optional)
• Sheet 2: "Categories" — Your lookup table. Column A=Keyword, Column B=Category name. This is what your auto-categorization formula references.

After importing your CSV, add "Category" as a header in cell D1. Leave the column blank for now — you'll populate it with a formula in Step 4.

Step 2: Build Your Category List

On Sheet 2 ("Categories"), build two tables: a keyword table for auto-matching and a master category list for your dropdown.

Keyword Table (Columns A and B)

Column A contains keywords that appear in bank transaction descriptions. Column B contains the category those keywords should map to. Examples:

Add as many keyword rows as you need. The more keywords you define, the higher the auto-categorization rate. Name this range: select all data in columns A and B, then use the Name Box (top-left) to name it KeywordTable.

Step 3: Add Dropdown Validation to the Category Column

Before writing the formula, set up dropdown validation on column D so that manually entered or formula-assigned categories are always consistent spelling — inconsistent names ("Office supplies" vs "Office Supplies") break pivot table totals.

1. In Sheet 2, create your master category list in column D, starting at D1. List every category name you want to use — one per row.
2. Name this list. Select all category names in column D, then type CategoryList in the Name Box and press Enter.
3. Go back to Sheet 1. Select the entire Category column (D2:D5000 or however far your data goes).
4. Go to Data → Data Validation. Under Allow, choose "List." In the Source field, enter =CategoryList. Click OK.

Now every cell in column D has a dropdown. Manually entered categories will be validated against your list, preventing typos.

Step 4: Auto-Categorize Transactions with SEARCH + INDEX/MATCH

The key formula uses SEARCH() (partial text match) rather than VLOOKUP() with exact match, because bank transaction descriptions contain merchant names alongside reference codes and timestamps. You need to find "AMAZON" within "AMAZON.COM*AB12345 SEATTLE WA."

The Formula

=IFERROR(
  INDEX(Categories!$B$2:$B$100,
    MATCH(TRUE,
      ISNUMBER(SEARCH(Categories!$A$2:$A$100, B2)),
      0)),
  "Uncategorized")

How it works: SEARCH(Categories!$A:$A, B2) checks whether each keyword in your keyword table appears anywhere in the transaction description. ISNUMBER() converts the result to TRUE/FALSE. MATCH(TRUE, ..., 0) finds the first TRUE (first matching keyword). INDEX() returns the corresponding category from column B. IFERROR() returns "Uncategorized" if no keyword matches.

Simpler Alternative: IF + SEARCH Chain

If you have fewer than 10 keywords, a nested IF approach is easier to read:

=IF(ISNUMBER(SEARCH("PAYROLL",B2)), "Revenue - Payroll",
IF(ISNUMBER(SEARCH("AMAZON",B2)), "Office Supplies",
IF(ISNUMBER(SEARCH("STRIPE",B2)), "Revenue - Sales",
"Uncategorized")))

Step 5: Highlight Uncategorized Transactions

Use conditional formatting to make uncategorized rows visually obvious so you don't miss them:

1. Select your entire data range (A2 through E1000).
2. Go to Home → Conditional Formatting → New Rule.
3. Choose "Use a formula to determine which cells to format."
4. Enter the formula: =$D2="Uncategorized" — note the $ before D but not before 2, so it applies the condition per row.
5. Set the format to a light orange or yellow fill. Click OK. Uncategorized rows now stand out immediately.

Step 6: Build a Category Summary Pivot Table

Once your transactions are categorized, a pivot table turns the data into an actionable summary in seconds:

1. Click anywhere in your transaction data.
2. Go to Insert → PivotTable. Choose "New Worksheet."
3. In the PivotTable Fields panel: Drag "Category" to Rows. Drag "Amount" to Values (it will default to Sum). Drag "Date" to Filters (optional — lets you filter by month).
4. Format the Amount column as Currency: right-click on any value in the pivot > Number Format > Currency.
5. Sort by sum descending to see your largest expense categories at the top: right-click on any amount > Sort > Largest to Smallest.

You now have a live spending-by-category summary. When you update your transaction data, right-click the pivot table and choose "Refresh" to update the totals.

IRS Business Expense Categories to Use

If you're categorizing for tax purposes, align your categories with IRS Schedule C line items so your accountant can use your spreadsheet directly:

Related Guides