How to categorise bank transactions in Excel
Use a reviewed payee lookup, an exceptions queue and a category summary to organise converted bank transactions without changing the source evidence.
To categorise bank transactions in Excel, keep dates, descriptions and signed amounts intact, add a suggested category from a payee lookup, then review exceptions before summarising. NoRekey extracts transactions; the category decisions in this workflow happen in your spreadsheet.
Download the categorisation workbook (.xlsx). It contains a synthetic transaction list, editable exact-match rules, a reviewer-override column and a category summary. Formula cells are shaded differently from inputs.
1. Get consistent rows
Use your bank's CSV if it contains the period you need. Otherwise convert your statements to Excel. Check dates, decimal separators, signs and any flagged balance results before adding categories.
Use one currency per workbook. Keep the original export separately and preserve statement filenames or references so you can trace a row back to the PDF. The merging guide covers combining months without losing that trail.
2. Create a small category list
Start with categories that answer your question: Income, Rent, Software, Bank fees, Transfer, Personal and Review are enough for the example. Tax reporting may require different headings; agree those with your accountant instead of treating this list as a filing specification.
The workbook's Rules sheet maps exact bank descriptions to suggestions. Exact matches are intentionally conservative: “Cloud software” matches that description, but does not silently classify every payment containing “cloud”. Unrecognised descriptions go to Review.
3. Separate suggestion from decision
In the Transactions sheet, columns A–C hold Date, Description and Amount. D suggests a category, E lets you enter a reviewed override, F resolves the final category, and G holds notes or evidence.
The suggestion formula follows this pattern:
=IF(B2="","",IFERROR(VLOOKUP(B2,Rules!$A$2:$B$101,2,FALSE),"Review"))
The final category is:
=IF(B2="","",IF(E2<>"",E2,D2))
If your Excel locale uses semicolons as formula separators, adjust formulas you enter manually. The downloadable XLSX already contains them. Do not paste a new export over formula columns D or F.
4. Work the exceptions queue
Filter Final category to Review and find the invoice or receipt for each item. Mixed-use spending, unfamiliar deposits and equipment purchases often need more context than a bank description provides.
The sample contains a £1,800 customer receipt, £600 rent, £20 software, £300 transfer, £50 personal purchase and a £40 unknown payment. The signed movement is £790. Known operating payments total £620; the £40 remains unresolved, so the operating summary is provisional.
Change the unknown row's override only when you have evidence. Keep the original description unchanged. For mixed accounts, use the business/personal workbook, which has a separate allocation column.
5. Summarise only after review
The workbook provides SUMIF category totals. In Excel you can also select the complete table and insert a PivotTable: put Final category in Rows and Amount in Values, summarised by Sum. Refresh the pivot after changing classifications. For monthly analysis, add a month column derived from a real date value rather than text.
A negative category total represents net money out under this convention. Do not reverse refund signs just to make every expense row positive: a refund should reduce spending in its category.
6. Check the total has not moved
The sum of every category, including Transfer, Personal and Review, must equal the original signed transaction total. Categorisation should change the grouping, not the money.
Check for empty categories, duplicate rules and formulas overwritten by pasted values. The supplied working area covers 500 transactions and 100 rules; extend formulas and all summary ranges together if you need more. Adding rows below the range without updating formulas will understate totals.
What this does not decide
A spreadsheet category is not a tax judgment, and a cash receipt is not always income. Loans, owner funding, transfers, processor settlements and timing differences need appropriate treatment. Use the sole-trader workflow to organise the wider records and agree reporting decisions with your bookkeeper.
Convert the statements, use the lookup to reduce repetitive work, and spend the saved time reviewing the exceptions.
Statements in, clean books out.
NoRekey converts bank statement PDFs to CSV, Excel, OFX and QFX — every conversion balance-checked. Free to try.
Convert a statement free →How to separate business and personal transactions in Excel
Sort a mixed bank account into business, personal, transfer and review rows without losing the statement balance or guessing tax deductibility.
How to combine multiple bank statements into one Excel file
Merge monthly statement PDFs into one spreadsheet, retain the source of every row, and check accounts, currencies and overlaps before importing.
Sole trader bookkeeping: the minimal system that actually works
No software subscription, no daily discipline — a quarterly, statement-first bookkeeping system for sole traders that produces exactly what self-assessment needs.