How to Use Excel Formulas to Categorize Bank Statement Transactions Effortlessly

Why Automate Transaction Categorization?

Manually sorting through a bank statement to assign categories like "Groceries," "Utilities," or "Dining Out" is tedious and error-prone. Automating this task with Excel formulas not only saves time, but also ensures consistency in your financial tracking.

Let's break down how to transform a plain CSV bank statement into a categorized, insightful dataset—using simple but powerful Excel techniques.

Step 1: Prepare Your Data

After downloading your bank statement (usually as a CSV), open it in Excel. It's important to start with a clean dataset; you can learn how to clean and organize your bank statement data here. Typical columns include:

• Date
• Description
• Amount
• Balance

For this tutorial, focus on the Description and Amount columns. Insert a new column titled Category.

Step 2: Create a Category Mapping Table

To automate categorization, first define your rules. Set up a lookup table on a new sheet or off to the side. For example:

The more keywords you add, the more transactions will be automatically categorized. This is a great feature to add to a reusable bank statement template.

Step 3: Use the VLOOKUP Formula

Excel's VLOOKUP is perfect for matching keywords in your transaction descriptions to categories.

First, make sure your mapping table is in a range, say Sheet2!A2:B20. In your Category column (let's assume it's column D, and descriptions are in column B), enter this formula in cell D2:

=VLOOKUP(TRUE, 
   SEARCH(Sheet2!$A$2:$A$20, B2)>0, 
   Sheet2!$B$2:$B$20, 
   0)

But here's the trick: VLOOKUP doesn't natively support arrays for searching substrings. Instead, use an array formula or helper columns. Here's a practical workaround:

Helper Column Method

1. Add a Helper Column in your transactions sheet (say, column E titled "Keyword Match").

2. In E2, enter:

   =INDEX(Sheet2!$A$2:$A$20, 
     MATCH(TRUE, ISNUMBER(SEARCH(Sheet2!$A$2:$A$20, B2)), 0))
   

   After typing the formula, press Ctrl+Shift+Enter (for older Excel versions) to make it an array formula.

3. In D2 (Category), use:

   =IFERROR(VLOOKUP(E2, Sheet2!$A$2:$B$20, 2, FALSE), "Uncategorized")
   

Copy these formulas down your list.

How This Works

• The helper formula finds the first keyword that appears in the transaction description.
• The VLOOKUP fetches the corresponding category from your mapping table.
• If no keyword is found, the formula returns "Uncategorized."

Step 4: Handle Multiple Matches and Edge Cases

Sometimes, a transaction could match several keywords. The above method only picks the first match. If you want to prioritize, order your mapping table so the most specific or important keywords come first.

If a transaction doesn't match any keyword, it's labeled "Uncategorized." Periodically review these entries and update your mapping table with new keywords.

Step 5: Level Up With XLOOKUP (Excel 365/2021)

If you have Excel 365 or 2021, you can streamline this with the more powerful XLOOKUP and dynamic arrays:

=LET(
  desc, B2,
  keywords, Sheet2!$A$2:$A$20,
  categories, Sheet2!$B$2:$B$20,
  idx, XMATCH(TRUE, ISNUMBER(SEARCH(keywords, desc)), 0),
  IF(ISNUMBER(idx), INDEX(categories, idx), "Uncategorized")
)

Paste this formula in your Category column and copy down.

Step 6: Fine-Tune and Visualize

• Review "Uncategorized" entries regularly and expand your mapping table.
• Summarize Categories using PivotTables or SUMIFS to analyze spending by category.
• Visualize with charts to spot trends.

Practical Tips

• Use UPPER() or LOWER() in your formulas if your bank descriptions vary in case.
• Regularly update your mapping table as you encounter new vendors.
• Back up your original data before experimenting with formulas.
• For more automation, explore how to use Excel macros.

Sample Formula Recap

Helper Column:

=INDEX(Sheet2!$A$2:$A$20, MATCH(TRUE, ISNUMBER(SEARCH(Sheet2!$A$2:$A$20, B2)), 0))

Category Column:

=IFERROR(VLOOKUP(E2, Sheet2!$A$2:$B$20, 2, FALSE), "Uncategorized")

Or, with Excel 365/2021:

=LET(
  desc, B2,
  keywords, Sheet2!$A$2:$A$20,
  categories, Sheet2!$B$2:$B$20,
  idx, XMATCH(TRUE, ISNUMBER(SEARCH(keywords, desc)), 0),
  IF(ISNUMBER(idx), INDEX(categories, idx), "Uncategorized")
)

Take Control of Your Finances

Once your transaction categorization is automated, you'll wonder how you ever managed without it. With a robust Excel setup, you gain clarity into your spending—without the manual drudgery. Keep refining your category mapping, and Excel will do the rest.