Categorize Bank Transactions in Excel with Pivot Tables

A pivot table can turn a transaction list into a useful monthly spending view, but the pivot table is the last step—not the categorization engine. First you need clean rows, a consistent category list, and a reviewable rule table.

This tutorial shows a controlled Excel workflow for categorizing bank transactions. It works best when you use a native CSV export from your bank. If the bank only gives you a statement PDF or image, convert it to structured rows, then compare the result with the original before you categorize anything.

What this workflow produces

Your workbook will contain three parts:

1. A Transactions table with one row per bank movement.
2. A Rules table that maps description keywords to categories.
3. A PivotTable that summarizes outflows by category and month.

The rules make repeated descriptions easier to handle, while an Exception column keeps unmatched or ambiguous rows visible. Excel is applying rules you define; it is not deciding the accounting or tax treatment for you.

Step 1: start with the best available source

Use the bank’s native CSV or spreadsheet export when it covers the period and accounts you need. It avoids an extra extraction step and usually preserves transaction identifiers and columns that a statement may not display.

When only a PDF, JPEG, PNG, or WebP statement is available, convert it into CSV or XLSX. Keep the original statement as evidence and perform these checks before continuing:

• opening balance plus net movements agrees with the closing balance;
• the first and last transactions are present;
• dates, signs, decimal separators, and currencies are correct;
• multi-line descriptions remain attached to the right amounts;
• no headers, footers, or carried balances became transactions.

For a deeper extraction checklist, see how to verify bank statement data after conversion.

Step 2: build a proper Transactions table

Keep one header row and one transaction per row. A practical structure is:

Date	Description	Money In	Money Out	Account	Currency	Category	Review
2026-01-05	ACME CLOUD HOSTING		89.00	Operating	EUR	Software
2026-01-07	CITY RAIL		42.50	Operating	EUR	Travel	Check purpose
2026-01-10	CLIENT ALPHA	2,400.00		Operating	EUR	Client receipts

Select the range and choose Insert → Table. Name it Transactions under Table Design. An Excel Table expands formulas and gives the PivotTable a stable source.

Do not merge cells, insert subtotal rows, or mix currencies without a separate Currency column. If several accounts are combined, add an Account column so transfers can be identified instead of counted as income or expense.

Step 3: define categories for your reporting purpose

There is no universal category list. Categories depend on the business, chart of accounts, jurisdiction, accounting method, and the question you want the pivot to answer.

A small operating view might use:

• Client receipts
• Rent and premises
• Payroll and contractors
• Software
• Marketing
• Travel
• Bank and payment charges
• Taxes paid
• Owner movements
• Loan movements
• Transfers
• Needs review

Treat this list as an internal analysis structure, not tax advice. Your accountant may need different accounts or separate treatment for capital purchases, sales taxes, loan principal, owner drawings, and mixed-use costs.

To reduce inconsistent spelling, place the category list on a separate sheet and use a named range for Excel data validation. Microsoft documents how to apply data validation to cells.

Step 4: create an auditable Rules table

On a Rules sheet, create an Excel Table named Rules:

Priority	Keyword	Category	Note
10	ACME CLOUD	Software	Monthly hosting
20	CITY RAIL	Travel	Confirm business purpose
30	BANK SERVICE	Bank and payment charges

Use the most specific keywords first because the formula below returns the first match. Avoid a generic keyword such as “PAYMENT” that could match unrelated rows.

In the Transactions table’s Category column, Excel 365 users can use:

=IFERROR(XLOOKUP(TRUE,ISNUMBER(SEARCH(Rules[Keyword],[@Description])),Rules[Category],""),"")

This searches each description for the rule keywords and returns the category from the first matching row. Microsoft explains that XLOOKUP returns the first match it finds. Availability varies by Excel version, so use a version-appropriate formula or Power Query merge if XLOOKUP is unavailable.

When VLOOKUP is appropriate

VLOOKUP can map an exact normalized merchant key to a category. For that design, add a NormalizedMerchant column to Transactions and create a separate two-column table named ExactRules, with Normalized Merchant first and Category second:

=IFERROR(VLOOKUP([@NormalizedMerchant],ExactRules[[Normalized Merchant]:[Category]],2,FALSE),"")

The lookup column must be the first column of that range, and the normalized merchant value must match a rule key. A formula that looks up "*"&[@Description]&"*" does not test whether each rule keyword appears inside a longer description; it searches in the opposite direction. Use the SEARCH/XLOOKUP approach above for keyword-within-description matching. Microsoft documents VLOOKUP’s lookup-column behavior in its VLOOKUP reference.

Add a Review formula so blanks cannot disappear silently:

=IF([@Category]="","Needs review","")

Then filter Review = Needs review and assign those rows manually. Add a new keyword only when it will reliably describe future transactions. Merchant descriptions can change, and the same merchant can support both business and personal purchases.

Step 5: review transfers, credits, and mixed-purpose rows

Keyword rules are useful for repetition, but these rows need deliberate treatment:

• Transfers between your own accounts: tag both sides consistently and exclude them from operating income and expense totals.
• Loan proceeds and principal payments: separate financing movements from operating activity.
• Merchant credits and reversals: link them to the original category instead of treating them as new revenue.
• Payroll: confirm whether the bank amount is a net payroll settlement while taxes and other liabilities sit elsewhere.
• Mixed-purpose purchases: retain the supporting document and record only the supported business portion.
• Unknown deposits: use invoices, settlement reports, or contracts to identify the source; the bank description alone may not prove it.

The IRS notes that supporting documents may need to be combined to establish the payee, amount, date, and business purpose. Its business recordkeeping guidance is a useful US reference; follow the rules for your own jurisdiction.

Step 6: build the PivotTable

Click inside the Transactions table, then choose Insert → PivotTable. Excel’s official PivotTable overview explains the field model.

For a monthly outflow report:

• Rows: Category
• Columns: Date
• Values: Sum of Money Out
• Filters: Account, Currency, Review

Right-click a date in the PivotTable and group by Months and Years if Excel recognizes the source as real dates. Keep Years as well as Months when the data crosses a calendar year.

Useful companion pivots include:

• inflows by source and month;
• outflows by account and category;
• uncategorized transaction count;
• owner, loan, and transfer movements in a separate financing view.

A pivot summarizes the rows it receives. It does not detect missing statements, duplicated transactions, wrong signs, or a bad category rule.

Step 7: add control totals

Before relying on the chart or totals, add a small control sheet:

Control	Expected result
Transaction count	Agrees with reviewed source rows
Total money in	Agrees with statement credits for the period
Total money out	Agrees with statement debits for the period
Unmatched categories	Zero, or a documented exception list
Transfer pairs	Both sides identified
Currency and account	No unintended mixing

Refresh the PivotTable after any rule or source change. Preserve a copy of the original import and record who reviewed exceptions and when.

Updating the workbook next month

If each monthly export uses the same columns, Power Query can append files from a dedicated folder. Microsoft documents both importing data from a folder and the broader Power Query workflow.

Keep that automation narrow:

1. place only same-schema source files in the folder;
2. append and normalize columns;
3. load into the Transactions table;
4. refresh rules and pivots;
5. review exceptions and control totals.

Excel rules versus automated categorization

This article deliberately uses explicit Excel rules. That makes the logic visible, editable, and testable. A separate guide to automated transaction categorization covers other approaches.

BankStatementLab’s role in this workflow is statement extraction: it can turn supported statement PDFs and images into CSV, XLSX, or JSON when a native export is unavailable. It does not replace your category policy, supporting documents, reconciliation, or human review.

Related articles

• How to Track Cash Flow from Your Bank Statements
• How to Separate Personal and Business Expenses
• How to Prepare Bank Statements for Your Accountant
• Why Bank Statement Layouts Break Generic Parsers

Convert a supported bank statement into reviewable Excel rows with BankStatementLab