How to Categorize Bank Statement Transactions — 4 Steps
Bank statement categorization starts with getting your transactions out of a PDF and into a structured format you can work with. Here is the complete workflow from PDF to categorized spending report:
2
Add Category Column
Insert a new column in Excel next to Description — label it "Category"
3
Apply VLOOKUP or IF Formula
Use keyword-matching formulas to auto-assign categories based on transaction description
4
Pivot Table by Category
Insert → PivotTable → sum amount by category to see your spending breakdown
Why convert to Excel first? Bank statement PDFs cannot be searched, filtered, or summed. Converting to Excel gives you a structured table where every transaction is one row — enabling formulas, filters, pivot tables, and import into accounting software such as QuickBooks, Xero, Tally, or YNAB.
Standard Bank Transaction Categories
These are the standard categories used in personal finance apps, accounting software, and tax returns worldwide. Use them as-is or adapt them for your specific needs:
How to Categorize Bank Transactions in Excel (Formula Method)
After converting your bank statement to Excel, your description column (typically column B) contains the transaction narration. Add a Category column (column F) and use the following formula approaches:
Simple IF Formula (Best for 3–5 Categories)
This formula checks the description column for keywords and assigns a category. Nest multiple IF statements for more categories:
=IF(ISNUMBER(SEARCH("SALARY",B2)),"Income",
IF(ISNUMBER(SEARCH("AMAZON",B2)),"Shopping",
IF(ISNUMBER(SEARCH("NETFLIX",B2)),"Entertainment",
IF(ISNUMBER(SEARCH("RENT",B2)),"Housing",
IF(ISNUMBER(SEARCH("UBER",B2)),"Transport",
"Uncategorized")))))
VLOOKUP from Keyword Mapping Table (Best for 10+ Categories)
Create a second sheet called "Category Map" with two columns: Keyword (A) and Category (B). Then use this formula to look up each transaction description against the keyword table:
=IFERROR(
INDEX(CategoryMap!B:B,
MATCH(1,
ISNUMBER(SEARCH(CategoryMap!A:A, B2)),
0)),
"Uncategorized")
Note: Enter this as an array formula with Ctrl+Shift+Enter in older versions of Excel, or use XLOOKUP in Excel 365.
Excel 365 XLOOKUP Approach
In Excel 365 or Google Sheets, use the newer XLOOKUP function which handles wildcards more cleanly:
=IFERROR(
XLOOKUP(TRUE,
ISNUMBER(SEARCH(CategoryMap!A$2:A$50, B2)),
CategoryMap!B$2:B$50,
"Uncategorized"),
"Uncategorized")
Pivot Table Analysis by Category
Once you have a Category column populated, create a pivot table to summarise spending by category:
- Click anywhere in your transaction table
- Go to Insert → PivotTable
- Drag Category to Rows
- Drag Amount to Values (set to Sum)
- Optionally drag Month (from transaction_date) to Columns for month-by-month view
The resulting pivot table shows total spending per category for the period, allowing instant budget analysis, spending trend identification, and comparison across months.
Use Cases for Categorized Bank Statements
🇮🇳 India — Bank Statement Categorization Use Cases
Indian businesses, freelancers, and individuals have specific categorization needs driven by the Indian tax system and accounting software landscape:
- ITR Schedule C Categorization for Freelancers: Indian freelancers filing ITR-4 (presumptive income under Section 44ADA) or ITR-3 need to separate professional fees received from personal credits. Categorize bank credits as Professional Income, Advance, Refund, or Personal Transfer to accurately declare gross receipts.
- GST Input Tax Credit (ITC) Tracking: Businesses claiming ITC under GSTR-2B need to match each bank payment to a purchase invoice. Categorize bank debits as B2B Purchase (eligible ITC), Exempt Purchase, Personal Expense, or Salary/Wages to calculate net ITC claimable in GSTR-3B.
- Tally Prime Journal Entry Preparation by Category: South Indian and North Indian businesses using Tally Prime can import categorized Excel statements directly. Each Category in the Excel maps to a Tally ledger account — Salary, Rent, Purchase, Sales, Misc Expense — enabling faster journal entry creation without manual narration matching.
- CA Audit Report Preparation: Chartered accountants across India prepare audit reports for companies under Section 44AB. A pre-categorized bank statement in Excel format dramatically reduces the time needed to prepare audit documentation, as all transactions are already sorted into income and expense categories matching the P&L account structure.
ITR Schedule C
GST ITC Tracking
Tally Prime
Section 44AB Audit
🇬🇧 UK — Bank Statement Categorization Use Cases
UK taxpayers and businesses use categorized bank statements for HMRC Self Assessment and VAT returns:
- HMRC Self Assessment (SA103 Self-Employment): Sole traders must declare total allowable business expenses in their SA103 return. Categorize business bank transactions into HMRC's expense categories: Office Costs, Travel, Clothing, Staff Costs, Advertising, Finance Charges, Professional Fees, and Other Expenses. The categorized Excel total for each category maps directly to the SA103 box numbers.
- MTD (Making Tax Digital) VAT Returns: Under MTD, VAT-registered businesses must maintain digital records of all sales and purchases. A categorized bank statement in Excel serves as the digital record for VAT input tax claims, particularly for businesses using the flat rate scheme where the categorized totals show the taxable purchase base.
- Construction Industry Scheme (CIS) Reconciliation: UK contractors and subcontractors using CIS must reconcile CIS deductions with bank receipts. Categorize bank credits as CIS Gross Payment, Net Payment (after deduction), or Refund to match against the monthly CIS300 return submitted to HMRC.
HMRC Self Assessment
MTD VAT Returns
CIS Reconciliation
🇺🇸 US — Bank Statement Categorization Use Cases
US taxpayers and small businesses use categorized bank statements for IRS tax filing and QuickBooks reconciliation:
- IRS Schedule C Business Expense Categorization: Self-employed individuals filing Schedule C must categorize business bank transactions into IRS expense lines: Advertising, Car & Truck, Contract Labor, Insurance, Interest, Rent, Repairs, Supplies, Taxes, Travel, Meals (50%), Utilities, and Other. Converting bank statements to Excel and pre-categorizing them significantly speeds up tax preparation with TurboTax, H&R Block, or your CPA.
- QuickBooks Online Chart of Accounts Mapping: When importing bank statements into QuickBooks Online, categorize the CSV before import so each transaction maps directly to a QuickBooks account name. This avoids the manual review queue in QuickBooks and produces a clean reconciliation on the first import pass.
- YNAB Envelope Budgeting: YNAB users import categorized bank statement CSVs to pre-populate their spending categories — Food & Dining, Housing, Transport, Personal Care, Entertainment — without needing to re-categorize every transaction manually inside YNAB.
IRS Schedule C
QuickBooks Mapping
YNAB Import
Supported Banks for PDF to Excel Conversion
Before you can categorize transactions, you need them in Excel. bankstatementengine.com converts PDF statements from banks worldwide — all auto-detected, no bank selection needed:
Frequently Asked Questions
What is bank statement categorization?
Bank statement categorization is the process of assigning each transaction to a spending or income category — Food, Rent, Salary, Transport, Utilities, and so on. It enables budget analysis, tax preparation, GST reconciliation, audit documentation, and import into budgeting apps like YNAB or QuickBooks. The first step is always converting your PDF bank statement to Excel so you have a structured table to work with.
How do I categorize bank statement transactions in Excel?
After converting your PDF to Excel with bankstatementengine.com, add a Category column next to Description. Use nested IF formulas with ISNUMBER(SEARCH()) to match keywords in the description: =IF(ISNUMBER(SEARCH("SALARY",B2)),"Income",IF(ISNUMBER(SEARCH("AMAZON",B2)),"Shopping","Other")). For 10+ categories, build a keyword mapping table and use INDEX/MATCH or XLOOKUP for faster batch categorization.
What are the standard bank statement transaction categories?
Standard categories include: Income (Salary, Freelance, Business Revenue, Interest), Housing (Rent/Mortgage, Utilities, Maintenance), Food & Dining (Restaurants, Groceries, Coffee), Transport (Fuel, Uber/Ola, Parking, Transit), Health (Pharmacy, Doctor, Insurance), Entertainment (Netflix, Spotify, Cinema), Shopping (Amazon, Flipkart, Clothing), Savings & Investments (SIP, EPF, FD, Brokerage), Financial (EMI, Insurance, Bank Charges, Tax), and Transfers (Own Account, Family).
Can the bankstatementengine.com tool auto-categorize transactions?
bankstatementengine.com produces a clean Excel file with date, description, amount, direction, and balance — the structured foundation you need for categorization. From there, apply Excel formulas or import the CSV into QuickBooks, Xero, Zoho Books, or YNAB, all of which have auto-categorization engines that assign categories based on merchant names and description keywords.
How do I categorize bank transactions for QuickBooks?
Convert your bank statement PDF to CSV using our tool. Import into QuickBooks Online via Transactions → Bank Transactions → Upload from File. QuickBooks auto-categorizes using its Rules engine based on description patterns. Set up bank rules to automatically assign expense accounts based on keywords. Unmatched transactions go to For Review for manual categorization.
How do I categorize bank transactions for YNAB?
Convert to CSV, then import in YNAB via your account's import button. YNAB matches imported transactions against payee history and auto-assigns remembered categories. New payees are categorized manually on first import; YNAB then remembers the category for future transactions from the same payee. YNAB's envelope categories (Food, Housing, Transport, etc.) align with the standard category structure outlined above.
How do I use bank statement categorization for ITR filing in India?
For ITR filing, categorize bank credits as Salary, Business Income, Professional Fees, Interest, and Refund — and debits as Business Expense, Investment, Personal, and Tax Payment. The categorized totals make it simple to calculate gross receipts for ITR-4, separate business from personal for ITR-3, and document deductions under Section 80C. Share the categorized Excel file with your CA to speed up return preparation.
How do I use bank statement categorization for HMRC Self Assessment in the UK?
For HMRC Self Assessment, categorize business bank transactions into SA103 expense lines: Office Costs, Travel, Clothing, Staff Costs, Advertising, Finance Charges, Professional Fees, and Other Expenses. The categorized total for each SA103 line maps directly to the corresponding box on your tax return. This eliminates the need to manually search your PDF for each expense category at tax time.
How do I categorize transactions for IRS Schedule C in the US?
Convert your bank statement to Excel and map business transactions to Schedule C expense lines: Advertising, Car & Truck, Contract Labor, Insurance, Interest, Rent, Repairs, Supplies, Taxes & Licenses, Travel, Meals (50% deductible), Utilities, and Other Expenses. The categorized Excel file gives your CPA exactly what they need for Schedule C preparation without sifting through raw PDF statements.
Which banks are supported for PDF to Excel conversion?
bankstatementengine.com supports hundreds of banks worldwide — India (HDFC, SBI, ICICI, Axis, Kotak, Federal Bank, TMB, KVB), UK (Barclays, HSBC, Lloyds, NatWest, Starling, Monzo), US (Chase, Wells Fargo, Bank of America, Navy Federal, Ally), Africa (Nedbank, UBA, Stanbic), and Asia (CIMB, UOB, BCA, Maybank, DBS). All banks are auto-detected — no manual selection required.
Can I categorize credit card statement transactions too?
Yes. Credit card statement categorization works exactly the same way. Convert your credit card statement PDF to Excel, add a Category column, and apply the same keyword-based formulas. Credit card statements are often more useful for spending analysis than bank statements because every transaction is a purchase — the categories (Food, Shopping, Entertainment, Travel, Health) map directly to budgeting app categories.
Is bank statement categorization free?
Converting your PDF to Excel is completely free at bankstatementengine.com with no signup required. Categorization using Excel formulas and pivot tables is also free — it requires only Microsoft Excel or Google Sheets. Importing into budgeting apps (YNAB has a subscription fee; QuickBooks Online has a paid plan; Zoho Books has a free tier) depends on which tool you choose.
Convert PDF to Excel First → Then Categorize
Free · No signup · All banks · Excel or CSV · Balance-verified · Instant
Upload Bank Statement PDF →