How-to · Spreadsheet problems
How to categorize bank transactions in Excel
Turn a flat statement export into spend-by-type: add categories, map merchants, and summarize with PivotTables or SUMIF.
Why categorize bank transactions in Excel?
Dates and amounts alone do not answer “where did the money go?” Categories turn each line into something you can budget, report, and discuss.
Doing it in Excel keeps the logic visible and reusable—especially when you pair a lookup table with monthly statement exports.
- Spend visibility by type
- Cleaner budgets and reports
- Faster month-end review
- Reusable rules each period
- Better PivotTables and charts
- Clearer handoff to accountants
Step-by-step: categorize bank transactions in Excel
Start with structured transactions
Export from a bank-statement converter so each row has date, description, and amount columns you can trust.
Add a Category column
Insert a column header named Category (or Type). Keep names short and consistent across months.
Define your category list
Write a small set of allowed categories on a second sheet so everyone uses the same labels.
Map merchants to categories
Fill by hand for a sample month, or build a lookup table and use XLOOKUP/VLOOKUP from description or merchant.
Handle leftovers
Filter blank categories and assign the rest. Use a default like Uncategorized until you refine rules.
Summarize by category
PivotTable or SUMIF to see totals. Reuse the lookup table when next month’s statement arrives.
Ways to assign categories
Manual tagging
Best for a first pass or unusual lines. Sort by description, fill Category down for similar merchants.
Lookup table
Merchant (or keyword) → Category on a side sheet. XLOOKUP scales well as the list grows.
Keyword formulas
IFS or nested IF with SEARCH/ISNUMBER for patterns like *UBER* → Travel. Flexible but harder to maintain.
Data validation
Dropdown on the Category column so labels stay consistent and typos drop.
Related spreadsheet how-tos
Filter by category
Once categories exist, AutoFilter shows one type at a time. Filter bank statement transactions →
Pivot by category
Prepare the table, then summarize Category × Amount. Prepare data for PivotTables →
FAQ: categorize bank transactions in Excel
How do I categorize bank transactions in Excel?
Add a Category column next to your transactions. Fill categories manually, with a VLOOKUP/XLOOKUP against a merchant-to-category table, or with nested IF/IFS rules based on keywords in the description.
What categories should I use for bank transactions?
Keep a short, consistent list—e.g. Rent, Payroll, Software, Travel, Transfers, Income, Fees. Too many one-off labels make PivotTables and filters harder to use.
Can I auto-categorize by merchant name?
Yes. Build a small lookup table of merchant keywords or exact names mapped to categories, then use XLOOKUP or VLOOKUP. New months reuse the same map.
How do I see totals by category?
Once Category is filled, use a PivotTable with Category in Rows and Amount in Values, or SUMIF for each category. Filtering by category also works for row-level review.
Should I categorize before or after converting the PDF?
Convert and clean first so descriptions and amounts are reliable. Categorization on messy or incomplete rows wastes time.
Is there a faster way than Excel formulas?
Some converters and tools suggest or apply categories during export. You can still refine the Category column in Excel afterward.
Categorize from clean statement data
Convert the PDF first, then add categories you can pivot and filter every month.