Expert guide
How to Categorize Bank Transactions in Excel with AI
A rules table does most of the work, AI handles the leftovers, and every review makes next month faster
Call us at +1 (385) 386-3860
Short answer: import the bank CSV into an Excel Table, apply a keyword-to-category rules table with a formula, and send only the unmatched descriptions to an AI model with a fixed category list. Review low-confidence answers, then add accepted ones to the rules table. After two or three months, rules cover most transactions and AI handles a small remainder. We build this as a one-click process in our AI workflow automation projects.
Bookkeepers, finance managers and business owners spend hours every month tagging transactions: fuel, software, meals, contractor payments, owner drawings. Accounting software helps, but many teams still end up in Excel because they consolidate several bank accounts, need management categories that differ from the chart of accounts, or prepare data for a budget, a lender or a tax adviser.
Throwing every row at AI is tempting. It is also the slowest and least consistent way to do it. The approach below combines deterministic rules with AI, and it gets better every month.
Step 1: Get Every Bank Into One Table
Each bank exports a different layout. Use Power Query (Data → Get Data → From File → From Folder) to load every CSV in a folder, map their columns to one standard shape, and append them. The standard shape should be: Date, Account, Description, Amount (negative for money out), and a unique Row ID.
If several people or entities send files in different formats, this is exactly what Excel data consolidation automates.
Step 2: Build a Rules Table
Create a second table named tblRules with three columns: Keyword, Category and Priority. Keywords are fragments that appear in descriptions.
| Keyword | Category | Priority |
|---|---|---|
| SHELL | Fuel | 10 |
| MICROSOFT* | Software | 10 |
| ADOBE | Software | 10 |
| UBER EATS | Meals | 5 |
| UBER | Travel | 10 |
| TRANSFER TO SAV | Internal transfer | 1 |
Priority resolves conflicts. "UBER EATS" must win over "UBER", so it gets a lower number and is checked first. Keep the table sorted by Priority.
Step 3: Apply the Rules With a Formula
In the transactions table add a Rule Category column. This Microsoft 365 formula returns the category of the first rule whose keyword appears in the description:
=LET(
hits, FILTER(tblRules[Category],
ISNUMBER(SEARCH(tblRules[Keyword], [@Description])), ""),
IF(INDEX(hits, 1) = "", "", INDEX(hits, 1)))SEARCH is case-insensitive and treats * and ? as wildcards, so a keyword like MICROSOFT* still works. On older versions of Excel, use a helper column with INDEX/MATCH over an array or move this step into Power Query.
Step 4: Send Only Unmatched Descriptions to AI
Filter Rule Category for blanks, copy the distinct descriptions (Data → Remove Duplicates on a copy), and send them with a prompt that constrains the answer.
You categorise business bank transactions. Allowed categories (use exactly one): Fuel, Travel, Meals, Software, Office supplies, Contractors, Payroll, Rent, Utilities, Insurance, Bank fees, Marketing, Internal transfer, Owner drawings, Sales income, Refund, Other. For each description return: description | category | suggested keyword | confidence (high/medium/low) "suggested keyword" is the shortest fragment that identifies the merchant, for use in a rules table. Use "Other" with low confidence if unsure. POS 4471 STAPLES #0233 ACH GUSTO PAYROLL 1182 SQ *BLUE BOTTLE COFFEE ZELLE TO M RIVERA
Paste the answers into a staging table. For high volumes, run the same prompt row by row from VBA; the code is in calling the OpenAI API from Excel VBA.
Step 5: Review, Then Teach the Rules Table
Accept high-confidence answers in bulk
Spot-check ten of them, then copy their suggested keyword and category into tblRules.
Review medium and low confidence by hand
These are often personal payments, transfers, or merchants with misleading names.
Never auto-add rules for generic text
Keywords like POS, ACH or CARD would match everything. Reject suggestions shorter than four characters.
Final category column
Use =IF([@[Rule Category]]<>"", [@[Rule Category]], [@[AI Category]]) and a Manual Override column that wins over both.
The first month, rules might match half the rows. By the third month, the rules table usually covers the large majority of recurring merchants, and AI only sees genuinely new descriptions.
Rules, AI, or Your Accounting Software?
| Rule | Excel rules | AI model | Accounting software bank rules |
|---|---|---|---|
| Consistency | Perfect | High with fixed categories | Perfect |
| Handles new merchants | No | Yes | Suggestions only |
| Multi-bank, multi-entity | Yes | Yes | Depends on plan |
| Custom management categories | Yes | Yes | Limited by chart of accounts |
| Cost per month | None | Small API cost | Included in subscription |
Guardrails for Financial Data
Send descriptions only, never account numbers, balances or full statements.
Use an AI service under a business agreement that excludes your data from training.
Keep the original description, the rule or AI source, and any manual override in separate columns so every category is traceable.
Lock the categorised month once it is reconciled.
From Monthly Chore to One Click
The full workflow, from importing bank files, applying rules and calling AI to writing results and building the summary, can run from one button or on a schedule. If the destination is QuickBooks, the categorised result can be posted directly; see our QuickBooks integration work. For month-end beyond transactions, see our month-end close checklist in Excel.
FAQ
Transaction categorization FAQ
For bookkeepers, finance teams and business owners