How to categorize bank transactions in Excel — the formulas, and where they break

A bank statement tells you what moved and when. What you usually need is what for — how much went on rent, salaries, software, travel; how much came in from which customer. Excel can do this well with a rules table and one formula. Here's the setup, and an honest note on where it stops scaling.

The examples assume this layout on a sheet called Statement:

Step 0 — Get a clean sheet

Formulas only work on clean data, and statements copied out of a PDF are rarely clean. Check for:

• Amounts stored as text — they sit left-aligned and SUM ignores them. Fix with =VALUE(SUBSTITUTE(C2, ",", "")), or Data → Text to Columns → Finish.
• Dates stored as text — Text to Columns with the date format set to DMY converts them properly.
• One amount column with Dr/Cr — split it: debit =IF(D2="Dr", C2, 0), credit =IF(D2="Cr", C2, 0).
• Narrations split across two rows — the second row has text but no date or amount. These must be merged back into the row above, or they'll be categorised as empty transactions.
• Page headers repeated mid-sheet — "Date Narration Chq No…" rows every 30 lines. Filter and delete.

This step is usually most of the work. If your source is a PDF, a converter that outputs clean columns saves it entirely — BankXL's Excel output already has numeric debit and credit columns and real dates.

Step 1 — Build a rules table

On a new sheet called Rules, make two columns: a keyword to look for in the narration, and the category it belongs to. Then select it and press Ctrl+T to make it an Excel Table named Rules — this matters for the formula below.

Keyword          Category
SALARY           Salary
RENT             Rent
SWIGGY           Staff Welfare
ZOMATO           Staff Welfare
BESCOM           Electricity
AIRTEL           Telephone & Internet
GST              GST Payment
INSURANCE        Insurance
INT.PD           Interest Received
CHRG             Bank Charges

Build it from real narrations, not guesses: sort the statement by narration, and the same payees repeat in clusters. Twenty to thirty rules typically cover most of a business statement.

Step 2 — The categorising formula

In Statement!F2, then fill down:

=IFERROR(INDEX(Rules[Category], MATCH(TRUE, ISNUMBER(SEARCH(Rules[Keyword], B2)), 0)), "Uncategorised")

How it works, inside out:

• SEARCH(Rules[Keyword], B2) looks for every keyword in the narration at once. It's not case-sensitive.
• ISNUMBER(…) turns that into TRUE where a keyword was found.
• MATCH(TRUE, …, 0) finds the first TRUE — the first matching rule.
• INDEX(Rules[Category], …) returns that rule's category; IFERROR labels anything unmatched.

Now filter column F for "Uncategorised", add rules for what you see, and repeat. Each pass shrinks the list.

Step 3 — Payment type (UPI, NEFT…)

Separately from category, it's useful to know the channel. Indian bank narrations nearly always contain it, though not always at the start, so search rather than compare the first characters:

=IFS(ISNUMBER(SEARCH("UPI", B2)),  "UPI",
     ISNUMBER(SEARCH("NEFT", B2)), "NEFT",
     ISNUMBER(SEARCH("IMPS", B2)), "IMPS",
     ISNUMBER(SEARCH("RTGS", B2)), "RTGS",
     ISNUMBER(SEARCH("NACH", B2)), "NACH/ECS",
     ISNUMBER(SEARCH("ATM", B2)),  "ATM/Cash",
     TRUE, "Other")

IFS needs Excel 2019 or Microsoft 365; in older versions, nest IFs the same way.

Step 4 — Totals by category

For a fixed summary table, with categories listed in column A of a Summary sheet:

Total debit   =SUMIFS(Statement!C:C, Statement!F:F, A2)
Total credit  =SUMIFS(Statement!D:D, Statement!F:F, A2)
Count         =COUNTIFS(Statement!F:F, A2)

Or select the data and use Insert → PivotTable: Category in Rows, Debit and Credit in Values. Add Date to Columns and group it by month for a month-by-category view in about ten seconds.

Step 5 — Check nothing went missing

Categorising a statement with a missing row gives totals that look completely plausible and are wrong. A running balance check catches it. In H3 (the second transaction row), fill down:

=IF(ABS(E2 + D3 - C3 - E3) < 0.01, "", "BREAK")

Each row's balance should equal the previous balance plus its credit minus its debit. Any "BREAK" marks exactly where a row is missing, duplicated or mis-read. Filter column H — you want it empty. If your statement is a PDF, our free checker runs the same test before you've copied anything.

Where this approach breaks

A rules table is a good system. It has three real limits:

• Every client needs their own rules. "SHARMA" is a customer for one client and the landlord for another.
• Narrations are inconsistent. The same payee appears as "AMAZON PAY", "AMZN" and "AMAZON SELLER SERVICES". Each needs its own rule.
• The cleaning in Step 0 repeats every month. The formulas are reusable; the PDF-to-clean-sheet work isn't.

This is the problem BankXL's ledger mapping is built for: it converts the PDF, assigns a ledger to each transaction from a built-in rule pack, puts your firm's own mapping ahead of it on paid plans, and checks every running balance so the totals can be trusted. The Excel file comes with a Ledger column already filled — and the Tally XML export uses the same ledgers.