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. Typical columns include:
- Date
- Description
- Amount
- Balance
For this tutorial, focus on the Description and Amount columns. Insert a new column titled Category next to them.
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:
Keyword | Category |
---|---|
WALMART | Groceries |
COSTCO | Groceries |
SHELL | Gas |
CHEVRON | Gas |
NETFLIX | Subscriptions |
STARBUCKS | Coffee Shops |
SAFEWAY | Groceries |
AMAZON | Shopping |
COMCAST | Utilities |
... | ... |
The more keywords you add, the more transactions will be automatically categorized.
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
Add a Helper Column in your transactions sheet (say, column E titled “Keyword Match”).
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.
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.
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.