Back to Blog
Tips 5 min readJan 30, 2025

5 Ways to Automatically Categorize Bank Transactions in Excel

From VLOOKUP formulas to AI tools, these are the best methods for auto-categorizing transactions — ranked by effort and accuracy.

M
MyPDFtoExcel TeamContent Team

You finally got your bank statement into Excel. Great! But now you have 400 rows of transactions that all say things likeUBER *EATS 12345 CA or SQ *LOCAL COFFEE SHOP.

Manually typing "Meals & Entertainment" next to every single row is a massive waste of time. Here are the 5 best ways to automate the categorization of bank transactions in Excel.

1. The VLOOKUP Method (The Classic approach)

If you frequently shop at the same merchants, you can build a mapping table.

  • Create a new sheet called "Mapping". In Column A put the merchant name snippet (e.g., "UBER"), and in Column B put the category ("Travel").
  • In your main transaction sheet, use a wildcard VLOOKUP to search the mapping table.

Pros: Free and highly customizable.
Cons: Requires maintaining an ever-growing list of merchants.

2. Excel's Artificial Intelligence Ideas (Flash Fill)

Excel's "Flash Fill" feature is surprisingly smart. Create a new "Category" column next to your descriptions.

  1. For the first 3-4 rows, manually type the correct category.
  2. Click the next empty cell, go to the Data tab, and click Flash Fill (or press Ctrl+E).

Excel will look at the patterns in your descriptions and attempt to categorize the rest of the 400 rows automatically.

3. Advanced: Power Query Custom Functions

Power Query allows you to write custom "If Text contains X then Y" statements. While powerful, this requires a solid understanding of Power Query's M-language. It's excellent for scaling across thousands of rows but has a steep learning curve.

4. Dedicated Bookkeeping Software

If you are constantly battling Excel rules, it might be time to import your data into QuickBooks or Xero. These platforms have global, crowdsourced categorizations. When an Uber charge hits, it automatically knows it's travel.

Note: To get your PDF statements into QuickBooks quickly, convert them using MyPDFtoExcel and export as a CSV or QBO file.

5. AI Classification Tools (The Future)

New AI plugins for Excel (like the official integration with ChatGPT) allow you to write a prompt directly in the cell:=AI.CATEGORIZE(B2, "Is this software, travel, or meals?").

While this is magic, be careful about sending vast amounts of your financial descriptions to open AI models depending on the plugin's privacy policy.