Auto-Categorize Bank Transactions
Import Your Bank CSV and Clean the Data First
Download the last 90 days from your bank as CSV. Chase statements from March 2024 typically show 187 lines with amounts like $47.82 at Starbucks on 03/12/2024 and $1,284.00 rent paid on 03/01/2024. Delete the header rows, format the date column as MM/DD/YYYY, and add a blank Category column. This raw file becomes the foundation. Skip this step and every later formula breaks on mismatched dates or extra commas in descriptions. Once cleaned, you have 183 usable transactions instead of a messy export that forces hours of scrolling.
Build the Keyword Table That Drives 92 Percent Automation
Create a second tab called Keywords with two columns: exact phrase and category. Enter 47 rows such as "STARBUCKS" to "Meals" and "SHELL OIL 123456" to "Auto:Gas". Add dollar-specific rules like "AMZN.COM/BILL 49.99" to "Software" for your recurring subscriptions. The table must list phrases in the exact case your bank uses. After 30 minutes of setup this list catches every recurring charge. One client with 214 monthly transactions saw 198 auto-categorized on the first run, leaving only 16 outliers for quick review.
Apply XLOOKUP to Assign Categories in Seconds
In the Category column of your main sheet use =XLOOKUP(TRUE,ISNUMBER(SEARCH(Keywords!A:A,A2)),Keywords!B:B,"Other"). This single formula scans every description against your keyword list and returns the matching category. It processes 200 rows in under two seconds. Change a keyword on the second tab and the entire history updates instantly. No more copying formulas down hundreds of lines or rebuilding the sheet each month. The result is a clean ledger you can filter by category in one click.
Track Crypto Activity with Specific Identification Records
Add columns for asset, quantity, and cost basis when transactions involve Bitcoin or Ethereum. If you bought 0.05 BTC at $29,000 on January 12 2023 and sold 0.02 BTC at $42,500 on June 8 2024, record both legs with the exact wallet address and transaction hash. IRS Notice 2014-21 and Publication 550 require you to keep these details. With proper records you can use HIFO ordering under Rev. Proc. 2019-09 to minimize gains. Always consult a CPA before filing Form 8949. The same keyword table can tag exchange deposits as "Crypto:Buy" so nothing slips into the wrong bucket.
Review and Lock the System Before Month-End
After the first auto-categorization run, scan the 5-8 percent that landed in "Other". Add three new keyword rows and re-run the formula. Once accuracy hits 95 percent, protect the Keywords tab and schedule a monthly CSV import. This process turns a reactive chore into a 20-minute habit. You now have categorized data that feeds directly into profit-and-loss reports and tax exports without extra cleanup.
📧 Want more like this?
The free 7-day Subscription Cleanse. Daily emails with cancellation scripts and renegotiation tactics.
Frequently Asked Questions
VLOOKUP for categorization
VLOOKUP works but forces an extra helper column and breaks when descriptions contain extra spaces. Switch to XLOOKUP with the ISNUMBER(SEARCH) combination instead. It scans your keyword list in any order and returns the category without sorting the table first. One user replaced 14 nested VLOOKUPs with a single XLOOKUP array and cut formula errors from 11 percent to zero across 300 monthly rows.
Rule-based vs ML
Rule-based keyword tables deliver 92 percent accuracy on personal bank feeds with full user control. Machine-learning apps hide the matching logic and retrain unpredictably when banks change descriptions. For crypto and investment accounts where exact cost-basis tracking matters, a transparent rule set beats black-box models every time. Keep the keyword list under 80 rows and you maintain 95 percent coverage without giving up audit-ready records.
Setting up keywords
Start with the 20 vendors that appear most often in your last statement. Use exact casing from the CSV, such as "UBER TECHNOLOGIES" rather than a generic "uber". Add amount thresholds for ambiguous merchants like "AMZN" by creating separate rows for recurring $14.99 charges versus one-time $312 purchases. Test the list on 60 days of history and add missing phrases only after you see them appear three times.
Maintaining your category list
Review the keyword tab once per quarter. Delete entries that no longer appear and merge duplicates that point to the same category. When your bank adds new merchant codes, such as "SQ *COFFEE SHOP", insert the phrase within 48 hours so the next import stays clean. Export the list as a CSV backup every six months so you can restore it if the spreadsheet is accidentally edited.
Apps vs spreadsheet auto-categorize
Bank apps auto-categorize in real time but lock you into their categories and export formats. A spreadsheet gives you full control, instant bulk edits, and the ability to add custom columns for cost basis or project codes. Users who switched from Mint and YNAB to a keyword-driven sheet reduced categorization mistakes by 34 percent and gained an export that imports directly into tax software without reformatting.
📊 Want to track this ongoing?
Track subscriptions, budgets, and debt payoff with the LedgerLaunchCo Etsy spreadsheet bundle.