Categorize Bank Transactions from a Statement to Excel, CSV, and QuickBooks

How to Categorize Bank Transactions in Excel

To categorize bank transactions in Excel, get the statement into rows first, then add a category column and tag each line. The fastest path is to convert the PDF with the PDF bank statement to Excel converter, which turns the document into date, description, debit, credit, and balance columns. From there you label each transaction by merchant. Here is the workflow most bookkeepers use:

1. 1Convert the statement PDF so every transaction is a clean row with the amount in its own column. Use bank statement converter for one upload that handles any bank or card.
2. 2Select your data and use Format as Table so you can sort and filter. Add a column named Category to the right of the amounts.
3. 3Sort by Description, then tag repeat merchants in one pass. A VLOOKUP or XLOOKUP against a small keyword-to-category list can auto-fill the common ones.
4. 4Build a PivotTable to total spend by category. Those totals map straight onto a profit and loss report or, for a sole proprietor, a Schedule C tax categorization.

The Excel formula that auto-categorizes by merchant keyword

Manual tagging only stays manageable for a month or two. The scalable version is a two column lookup table on a second sheet, one row per keyword, and a single formula that scans the description for the first keyword it matches. Build the lookup table like this:

With the keywords in Rules!A:A and the categories in Rules!B:B, put this in the Category column next to your first transaction, where C2 is the description cell:

=IFERROR(INDEX(Rules!B:B,MATCH(TRUE,ISNUMBER(SEARCH(Rules!A:A,C2)),0)),"Uncategorized")

In Microsoft 365 and Excel 2021 that formula spills on its own. In older versions it is an array formula, so confirm it with Ctrl, Shift and Enter rather than Enter alone. SEARCH is deliberately case insensitive, which matters because bank descriptions arrive in a mix of upper and lower case. Anything the lookup misses lands as Uncategorized, so you filter that column, add the new merchant to the rules sheet, and the count falls every month instead of the work repeating.

Two habits keep the totals honest. Put the more specific keyword above the general one, because MATCH stops at the first hit, and give transfers between your own accounts their own category so they never inflate income or expenses. That single mistake is the most common reason a categorized sheet shows revenue the business never earned, and it is worth reading up on how to categorize transfers between bank accounts before you close a year.

If you would rather skip the manual tagging, the categorization tool above assigns categories during conversion, so the sheet arrives already labeled. You can still edit any category in Excel afterward. Working in Google Workspace instead? The bank statement to Google Sheets converter produces the same clean, taggable rows.

How to Categorize Bank Transactions in QuickBooks Online

To categorize bank transactions in QuickBooks Online, you first need the transactions inside QuickBooks, then you assign each one to an account and let bank rules handle the repeats. When the bank feed does not reach the account, or you are working from a PDF statement for a closed or historical period, convert the statement to a QuickBooks-ready CSV first, upload it, and categorize from there. The steps most bookkeepers follow:

1. 1Convert the statement PDF to a clean CSV with date, description, and amount columns using the tool above, or send it straight to a QuickBooks file with a bank statement to QuickBooks converter.
2. 2In QuickBooks Online go to Transactions, then Bank transactions, choose the account, and use Upload from file to bring the CSV in. Map the Date, Description, and Amount columns when prompted.
3. 3On the For review tab, open each transaction and pick the category, which in QuickBooks means the account from your chart of accounts, then confirm it to post to the register.
4. 4Create bank rules under the Rules tab that match a keyword in the description to a fixed category and payee, so every future transaction from that vendor is categorized automatically.

The category you assign in QuickBooks is really an account on your chart of accounts, so align those account names to the way you file taxes and the year-end reports fall out cleanly. For a walkthrough of the upload itself, see how to import bank transactions into QuickBooks Online. If you would rather have software read the receipts behind those charges and code them for you before they ever reach the books, a dedicated expense management platform automates that side of the workflow.

How to Categorize Transactions Across Multiple Accounts and Months

To categorize transactions across several accounts and months, use one consistent category list for all of them and keep each statement's account and period labeled so nothing gets mixed together. This is the situation most bookkeepers hit during a catch-up: a checking account, a savings account, and two credit cards, each with a year of PDFs. The workflow that scales is to convert every statement the same way, stack the categorized rows into one sheet with an Account column and a Month column, then total by category across the whole set.

1. 1Convert each statement with the tool above so every account produces the same date, description, amount, and category columns. Add an Account column and a Month column to each export before you combine them.
2. 2Apply the same category names everywhere. A charge coded Software on the checking account and Subscriptions on the card will not total together, so pick one label and reuse it.
3. 3Tag transfers between your own accounts on both sides so a move from checking to savings is not counted as an expense on one and income on the other. This is the single most common error in a multi-account catch-up.
4. 4Build one PivotTable over the combined sheet with Category in rows and Month in columns to see spend by category over time, ready for a profit and loss or a tax return.

Remember that a bank statement only ever shows cash as it moves, so these totals are a cash-basis picture of the business: see cash basis versus accrual accounting for what that means at tax time. If you are reconstructing a full year from scratch, the catch-up bookkeeping converter walks through the same multi-statement process end to end.