Categorize bank transactions in Excel without hiding conflicting rules

Keep the description you started with

Replacing a bank description with “Groceries” makes the sheet shorter but removes the thing you need when the label looks wrong. Keep the original description, date and signed amount in their own columns. Give each transaction a stable ID, and keep its source file and account alongside it when you use your own data.

Save the untouched export separately. If you haven't checked the input yet, start with the statement CSV checks. Categorization should not repair amounts, remove transactions or decide which account movements belong in your spending total.

In Excel, save your working file as .xlsx, not back over the CSV. A CSV won't preserve this two-sheet setup: Microsoft's file-format guide says it saves only the displayed text and values on the active sheet. Keep the CSV as the source, not as your reusable workbook.

Create sheets named Transactions and Rules. Import or paste this fictional CSV into Transactions!A1, splitting on commas so it occupies columns A through D. All amounts are USD; negatives are money out and the positive $5 is a fictional grocery refund. Use English function names and comma argument separators for the formulas shown here.

Row ID,Date,Original description,Amount
T001,2026-09-02,GREEN MARKET 014,-42.30
T002,2026-09-03,PAYPAL *GREEN MARKET,-18.70
T003,2026-09-04,GREEN ENERGY,-65.00
T004,2026-09-05,MYSTERY SHOP 882,-23.50
T005,2026-09-06,CITY TRANSIT,-12.00
T006,2026-09-07,green market 014,5.00

Before adding rules, =COUNTA(A2:A7) and =COUNT(D2:D7) should both return 6. The second check catches amounts imported as text. =SUM(D2:D7) should return -156.50. Write those results somewhere outside the transaction range; you'll compare them again after labeling. If your import looks different, resolve the date and number formats before continuing.

Use a merchant phrase, not a hopeful keyword

A rule containing just GREEN would catch both the food shop and the energy bill in this example. Use the more specific GREEN MARKET instead. A “Contains” rule finds that phrase anywhere in the description, while an “Exact” rule requires the whole description to match, apart from letter case in this workbook.

Paste this second fictional CSV into Rules!A1. The empty phrase in R4 is deliberate: it tests whether the workbook rejects an unfinished rule. “Review receipt” is a warning label, not an expense category to approve automatically.

Rule ID,Mode,Phrase,Suggested category
R1,Contains,GREEN MARKET,Groceries
R2,Contains,PAYPAL,Review receipt
R3,Exact,GREEN ENERGY,Utilities
R4,Contains,,Groceries
R5,Exact,CITY TRANSIT,Transport

Put Rule check in E1, then enter this in Rules!E2 and fill down through E6. It rejects an unknown mode, an empty or ordinary-space-only phrase, and a missing category.

=IF(AND(OR(B2="Exact",B2="Contains"),LEN(TRIM(C2))>0,LEN(TRIM(D2))>0),"Valid","Invalid")

R4 must say Invalid; the other four should say Valid. Fix invalid rules before using them on real records. The matching formula will also ignore them, so an unfinished row cannot become a catch-all. If a rule looks blank but passes, clear its phrase cell and retype it. As Microsoft explains, TRIM removes ordinary spaces, not nonbreaking spaces copied from a web page.

This workbook deliberately has no “first rule wins” priority. Every valid rule gets checked, and two matches mean review, even if both rules suggest the same category. Sorting the rules should not change what a payment means.

Make the matches visible before adding the lookup

Five extra columns may seem excessive for a category formula. They let you see exactly why a row matched, which is useful when you change a phrase next month. Columns E through I on Transactions will each show one rule's result: 1 for a match, 0 for no match or an invalid rule.

The formula uses LET to name its intermediate values. Microsoft's LET documentation, updated June 25, 2026 lists Microsoft 365, Excel 2024 and Excel 2021, including their Mac editions. That is the Excel version requirement for this recipe. Although Microsoft's INDEX/MATCH guide, also updated June 25, 2026 includes Excel 2016 and 2019, the LET formula here is not a recipe for those editions. Google Sheets also documents LET and INDEX with the syntax used below.

In Transactions!E1, enter the following and fill right through I1. These headers follow the rule IDs if you reorder the complete rows on the Rules sheet.

=INDEX(Rules!$A$2:$A$6,COLUMN(A1))

In Transactions!E2, enter this formula. Fill right through I2, then fill that five-cell row down through row 7. Keep the dollar signs: the description column stays fixed while the rule number advances across the sheet.

=LET(
  k,COLUMN(A1),
  mode,INDEX(Rules!$B$2:$B$6,k),
  phrase,INDEX(Rules!$C$2:$C$6,k),
  valid,INDEX(Rules!$E$2:$E$6,k),
  IF(valid<>"Valid",0,
    IF(mode="Exact",
      --(UPPER($C2)=UPPER(phrase)),
      --ISNUMBER(FIND(UPPER(phrase),UPPER($C2)))
    )
  )
)

Both sides are converted to uppercase for the comparison, without changing the saved description. Google's FIND reference explains that FIND itself is case-sensitive and returns an error when the phrase is absent; ISNUMBER turns a found position into a match. The double minus turns true or false into 1 or 0.

“Exact” still takes spaces and punctuation seriously. “Contains” is not a whole-word search, so GREEN MARKET would also match GREEN MARKETPLACE. If that would mix two merchants in your records, use exact rules for their full descriptions rather than hoping a shorter phrase knows the difference.

Return a category only when one rule matches

The lookup is the short part. Put Match count in J1 and Suggestion in K1. Enter these formulas in J2 and K2 respectively, then fill both down through row 7.

=SUM(E2:I2)

=IF(J2=0,"Unclassified",IF(J2>1,"Conflict",INDEX(Rules!$D$2:$D$6,MATCH(1,E2:I2,0))))

A zero leaves the row unclassified. Two or more matches produce Conflict, not a category. Only a count of one reaches the lookup. The final 0 in MATCH requests an exact match, as documented by Microsoft and Google; it looks for the single numeric 1, not an approximate merchant name.

In the original rule order, the PayPal row has 1, 1, 0, 0, 0 across its match columns. The invalid blank rule contributes nothing. All six transactions remain, including the unknown merchant and the refund, and the signed total is still -$156.50.

A suggestion still needs someone to recognize the purchase

The two matches on PAYPAL *GREEN MARKET are useful information. One rule sees the processor word; the other sees a merchant phrase. Neither tells you what was bought. Check the receipt or your purchase history before deciding where the $18.70 belongs. Don't solve the conflict by moving the grocery rule higher in the table.

Add Reviewed category in L1 and Review note in M1. Keep these separate from the formula result, so your decision doesn't disappear when the rules change. For a one-off correction, enter the confirmed category in L and the reason in M. For a repeated pattern, revise the rule and inspect every row it now affects before accepting its suggestions.

The unknown merchant stays unclassified until you have enough information. A single match deserves a look too: a shop can sell things in more than one category. If a receipt needs splitting, don't force the whole payment into one label just to clear the sheet. Leave it unresolved here until you have a separate allocation that adds back to the original payment.

In this fictional review, suppose you confirm the two grocery rows, the utility bill and the transit purchase. Enter Groceries in L2 and L7, Utilities in L4, and Transport in L6. Leave L3 and L5 genuinely empty. The grocery credit remains positive because it reduces net grocery outflow; the rule has no reason to reverse its sign.

Show the unresolved amount next to the totals

Your summary should use the reviewed column, not the suggestions. Otherwise a formula output can quietly become a decision nobody made. These formulas go in spare cells on the Transactions sheet; the last one keeps the unreviewed money visible.

=SUMIF($L$2:$L$7,"Groceries",$D$2:$D$7)
=SUMIF($L$2:$L$7,"Utilities",$D$2:$D$7)
=SUMIF($L$2:$L$7,"Transport",$D$2:$D$7)
=SUMIF($L$2:$L$7,"",$D$2:$D$7)

Both Microsoft's SUMIF reference and Google's SUMIF reference describe using one range for the condition and another for the amounts. Here the condition is the reviewed label. Keep those labels consistent with the summary, and add a summary row whenever you approve a new category. Avoid * and ? in these category labels because SUMIF treats them as wildcards, not ordinary characters.

The four reviewed rows total -$114.30; the two unresolved rows total -$42.20. Together they account for -$156.50. Recheck the six IDs and six numeric amounts, too: equal totals alone would not catch two missing entries that offset each other. This is a partial category report, not a finished spending total with the awkward rows left out.

Before you use the rules next month

Keep a dated copy of the rules with the report, because changing a phrase recalculates the old suggestions as well as the new ones. Sort complete rows, never just the phrase or category column. For a new month, start with fresh transaction rows and empty review columns. Pasting over A:D in the old sheet would leave last month's approvals next to this month's purchases.

Fill E:K through your last transaction, and extend the input-check and SUMIF ranges to that row; the example stops at row 7. There are also exactly five rule slots. Adding a sixth means filling down Rule check, expanding all Rules ranges, adding a match column before Match count, and extending the count and lookup ranges across that column. Otherwise the new rule is not being checked.

For US business records, a merchant label is not evidence of deductibility. The IRS recordkeeping guidance says expense documents should identify the payee, amount, proof of payment, date, and the item or service showing the business expense; a combination of documents may be needed. Keep that evidence and ask your tax preparer about tax treatment rather than asking a merchant rule to decide it. Our statement tax-record guide covers that boundary in more detail.

If you'd rather work without formulas, try the merchant spending summary. It uses a different policy: the first matching rule supplies the label, with overlaps shown for review. Don't expect its suggestions to be identical to this workbook's.

If your bank offers a native CSV for the period, use it as the starting table. For PDF-only history, you can transcribe a few rows or use AnyStatement to extract a larger table, then check it against the PDFs. Extraction supplies the descriptions and amounts; it does not infer business purpose or deductions.

We checked the linked Microsoft, Google and IRS pages on October 17, 2026, including the helper functions in the Excel function list and Sheets function list. We also tested the extracted formulas with equivalent logic and Decimal arithmetic in Python: all six rows, all 120 orderings of the five rules, invalid and blank rules, exact-match boundaries, and the totals above. We did not run the workbook in Excel or Google Sheets. Try the fictional rows in your spreadsheet first; the expected results give you something concrete to compare before you trust it with your own records.