Conditional Formatting for Bank Transactions in Excel

TL;DR Conditional formatting colours cells automatically based on rules, which makes it ideal for scanning bank transactions. The most useful rules are: highlight amounts above a threshold, shade the whole row for large payments using a formula rule such as =ABS($C2)>=1000, flag the running balance when it drops below a minimum, mark possible duplicates with COUNTIFS, highlight descriptions containing a keyword with ISNUMBER(SEARCH(...)), and colour rows with an empty Category so nothing is left uncategorised. Use formula rules with a dollar sign before the column letter to colour full rows, keep thresholds in input cells so you can change them without editing rules, and keep the number of rules small so the sheet stays readable and fast.

When you convert a bank statement into a spreadsheet, you get hundreds of rows that all look the same. The interesting ones, such as a payment much larger than usual, a duplicate charge, a balance that dipped too low or a transaction you forgot to categorise, are buried among them.

Conditional formatting makes those rows stand out without filtering or sorting. Unlike a filter, it leaves all your data visible and in order, and it updates automatically when you paste in new months. This guide covers the rules that are genuinely useful for bank data, how to write formula rules that colour entire rows, a worked example, managing rules as your workbook grows, and the differences in Google Sheets.

Set up your data first

Conditional formatting works best on tidy data: one transaction per row, real dates, real numbers. A layout like this works well:

Money in is positive and money out is negative in a single Amount column. If your data has separate debit and credit columns, combine them first; our guide to separating debit and credit columns shows the formula. If dates or amounts are text, the rules below won't work properly; see our guides to fixing bank statement dates and cleaning bank statement data.

If you're starting from a PDF, SheetStatement converts it into rows in this shape with a balance check. The free plan covers 3 pages a month.

It also helps to turn the range into an Excel table (Insert, then Table). Tables extend formatting rules automatically when you add rows, though you should still check the rule's range after large pastes.

Built-in rules vs formula rules

Excel offers two kinds of conditional formatting.

Built-in rules are quick to set up: "Greater than", "Less than", "Between", "Text that contains", "Duplicate values", "Top 10 items", data bars and colour scales. They apply to the cells you selected and colour those cells only.

Formula rules ("Use a formula to determine which cells to format") let you write any condition that returns TRUE or FALSE. They're more flexible and they can colour an entire row based on one column's value, which is what you usually want with transactions.

The rules in this guide are mostly formula rules, because for bank data, seeing the whole row in colour is far more useful than a single coloured amount.

How formula rules work for whole rows

The key idea is relative and absolute references.

1. Select the whole data range, for example A2:E500, starting from the first data row.
2. Create a new rule using a formula.
3. Write the formula as if it applies to the first row of your selection, row 2.
4. Put a dollar sign before the column letter but not the row number, such as $C2.

Excel then evaluates the formula for every cell in the range, shifting the row number but keeping the column fixed. Every cell in row 7 checks $C7, so the whole row lights up together.

If you forget the dollar sign before the column, each column checks a different column and the colouring looks random. That's the most common conditional formatting mistake.

Rule 1: highlight large transactions

Put your threshold in a cell, say H1, with a label in G1 such as "Large transaction". Then add a formula rule to A2:E500:

=ABS($C2)>=$H$1

ABS makes the rule catch both large payments and large deposits. If you only care about outgoing money:

=$C2<=-$H$1

Keeping the threshold in a cell means you can change it from 1,000 to 500 without editing the rule. That's especially handy when you share the workbook with someone who isn't comfortable editing rules.

Choose a light fill colour with dark text. Strong fills with dark text are hard to read, and hard-to-read sheets defeat the purpose.

Rule 2: flag a low running balance

If your data includes the balance after each transaction, a rule on the balance column shows when the account ran close to empty. Put the minimum in H2:

=$D2<$H$2

Apply it to the row or just the Balance column. This is useful for budgeting, for spotting months where an overdraft was close, and for preparing for a loan or proof of funds review where the lowest balance matters. For an average-based view, see our guide to the average daily balance.

Rule 3: mark possible duplicates

The built-in "Duplicate values" rule looks at one column only, so it will flag every pair of $9.99 payments even if they're for different things on different dates. A formula rule using several columns is more accurate:

=COUNTIFS($A$2:$A$500,$A2,$B$2:$B$500,$B2,$C$2:$C$500,$C2)>1

This colours a row only if another row has the same date, description and amount. Those rows deserve a look. Some are true duplicates from overlapping imports; others are genuine, such as two identical coffees on the same day. Our guide to removing duplicate bank transactions in Excel explains how to decide and how to remove them safely.

Rule 4: highlight keywords

To make every row containing a certain word stand out, put the keyword in H3 and use:

=AND($H$3<>"", ISNUMBER(SEARCH($H$3, $B2)))

SEARCH is not case-sensitive, so "amazon" matches "AMAZON MKTPLACE". The AND($H$3<>"", ...) part stops the rule from highlighting every row when the keyword cell is empty.

This works as a quick visual search box: type a merchant name in H3 and its rows light up while everything else stays in view. If you want the matching rows extracted into a separate list instead, our guide to the FILTER function for bank transactions shows how.

To highlight several fixed keywords, such as known subscriptions, put them in a range like J2:J10 and use:

=SUMPRODUCT(--ISNUMBER(SEARCH($J$2:$J$10, $B2)))>0

If the keyword range might contain blank cells, those blanks match everything, so either keep the range tight or add a condition that skips blanks: =SUMPRODUCT(($J$2:$J$10<>"")*ISNUMBER(SEARCH($J$2:$J$10,$B2)))>0.

Rule 5: show uncategorised rows

If you categorise transactions, this rule is a great safety net:

=AND($C2<>"", $E2="")

Any row with an amount but no category is coloured, so you can't miss it when scrolling. Once everything is categorised, the colour disappears. Our guide to Excel formulas for categorising transactions helps fill most categories automatically, leaving this rule to catch the rest.

Rule 6: flag dates outside the period

When combining several statements, it's easy to paste rows from the wrong month. Put the period start in H4 and end in H5:

=OR($A2<$H$4, $A2>$H$5)

Any row outside the period is coloured. This also catches dates that were misread, such as day and month swapped, because they often land outside the expected range.

Rule 7: highlight transfers and card payments

Transfers between your own accounts and credit card payments aren't income or spending, so many people like them visually muted. If they have the category Transfer:

=$E2="Transfer"

Use a grey font rather than a fill. They stay visible for reconciliation but don't distract when you're reviewing spending.

Rule 8: unmatched rows during reconciliation

If you reconcile your bank data against another list, such as your accounting records, invoices, or a payment platform's payouts, you'll usually have a helper column that shows whether each row found a match. Suppose column F holds the result of an XLOOKUP that returns "Not found" when there's no match:

=$F2="Not found"

Every unmatched row stands out, and the colour disappears as you resolve each one. This turns a long reconciliation into a visible to-do list. Our guide to matching bank transactions with XLOOKUP shows how to build the match column, and our guide to reconciling a bank statement in Excel shows the full process.

Rule 9: round amounts and weekend activity

For a review of unusual activity, two extra rules are handy. They don't prove anything is wrong; they just point your eyes at rows worth a second look.

Round amounts, such as exact hundreds, can indicate cash withdrawals, transfers or manual payments rather than ordinary purchases:

=AND($C2<>0, MOD(ABS($C2),100)=0)

Transactions on weekends may matter for a business account where you don't expect weekend activity:

=WEEKDAY($A2,2)>5

WEEKDAY with a second argument of 2 numbers Monday as 1 and Sunday as 7, so values above 5 are Saturday and Sunday. Bear in mind that card transactions may be dated by when they posted rather than when they happened, so a weekend purchase might show a Monday date. These rules are a starting point for questions, not conclusions. For more structured techniques, see our guide to forensic accounting with bank statements.

Data bars and colour scales

Data bars draw a small bar inside each cell proportional to its value. On an Amount column with both positive and negative values, Excel can draw negative bars in a different direction and colour, which gives a quick visual of the size of each transaction. On a monthly summary, colour scales can show which months or categories were heaviest.

These are best on summary tables rather than raw transactions. On a few hundred rows of raw data, bars and scales add visual noise. On a twelve-row summary of monthly spending, they're very effective. Our guide to bank statement pivot tables shows how to build the summary first.

A worked example

You've converted six months of a business account into one table, A2:E640. You set these inputs:

And these rules, in this order:

1. Dates outside period: red fill. Stop here if true.
2. Possible duplicates: orange fill.
3. Large transactions: light yellow fill.
4. Low balance: light red fill on the Balance column only.
5. Uncategorised: light blue fill.
6. Keyword search: bold text.
7. Transfers: grey font.

Scrolling through, you notice:

• Three yellow rows: a quarterly tax payment, an equipment purchase and a large client deposit. All expected.
• Two orange rows on the same date for the same software charge. Checking the original statement shows it was charged once; the duplicate came from overlapping months during the paste. You delete one.
• One red row dated 3 July 2026. It's from July, pasted in by mistake. Removed.
• Light red balance cells in late March, when the balance fell to $1,640 just before a client paid. Worth knowing for cash planning.
• A handful of blue rows still without categories. You fill them in and the colour disappears.

In a few minutes, you've found a duplicate, a stray row and a cash-flow pinch that would have been easy to miss in 640 plain rows.

A starter set for personal budgets

Not every workbook needs nine rules. For a personal budget built from a few months of statements, three are usually enough:

1. Uncategorised rows (Rule 5), so every transaction gets a category.
2. Large transactions (Rule 1) with a threshold that suits your spending, so big one-off costs stand out when you review the month.
3. Transfers in grey (Rule 7), so moves to savings and card payments don't distract from real spending.

Add the duplicate rule when you start combining statements, and the low-balance rule if you're managing tight cash flow. Starting small keeps the sheet calm and readable, and each rule you add later has a clear purpose.

Managing rules

As the workbook grows, rules can pile up. A few habits keep things tidy:

• Open the Rules Manager (Conditional Formatting, then Manage Rules) and choose "This Worksheet" to see every rule in one place.
• Check "Applies to" ranges. Copying and pasting can split one rule into many fragments with odd ranges. Delete the fragments and fix the original range.
• Order rules deliberately. Rules are evaluated top to bottom. Where two rules set the same property, such as fill colour, the higher one wins. "Stop If True" prevents lower rules from applying when a rule matches.
• Limit whole-column ranges. Applying complex formula rules to entire columns can slow a large workbook. Use a reasonable range or a table.
• Prefer readable colours. Light fills with dark text are easy to read in both normal and printed views.

Google Sheets differences

Google Sheets supports conditional formatting with similar ideas:

• Select the range, open conditional formatting, and choose "Custom formula is" for formula rules.
• The same $C2 pattern colours whole rows.
• Formulas referring to other sheets may need INDIRECT.
• Rules are applied in order, with the first matching rule's formatting used where they overlap.

Most formulas in this guide work in Sheets unchanged. For other Sheets techniques, see our guide to the QUERY function for bank transactions.

Common problems

The whole sheet lights up. The formula probably refers to the wrong row. It must be written for the first row of the selected range.

Only one column colours. The dollar sign before the column letter is missing, or the rule's range is a single column.

Amounts don't trigger rules. They're stored as text. Convert them to numbers.

Duplicate rule flags too much. Use the multi-column COUNTIFS rule rather than the built-in single-column one.

Rules stop working after pasting new data. The paste may have overwritten formats or the rule's range doesn't include the new rows. Paste values only, or extend the range.

Get clean transactions to format

Conditional formatting is only as good as the data underneath. Upload a statement PDF to SheetStatement, confirm the balances, and export to Excel with real dates and signed amounts. The free plan covers 3 pages a month; paid plans are $9, $19 and $39 a month.

FAQ

How do I highlight an entire row based on one cell?

Select the full range, create a formula rule, write the formula for the first row, and put a dollar sign before the column letter only, such as =$C2>1000.

How do I highlight transactions over a certain amount?

Use a formula rule like =ABS($C2)>=$H$1 with the threshold in H1, so you can change the amount without editing the rule.

Can conditional formatting find duplicate transactions?

Yes. A COUNTIFS rule across date, description and amount flags rows that match another row exactly, which is more reliable than the single-column duplicate rule.

Why is my conditional formatting not working on amounts?

The amounts are probably stored as text. Convert them to numbers and the rules will work.

Does this work in Google Sheets?

Yes. Use "Custom formula is" with the same formulas. Most patterns in this guide work unchanged.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Paid plans are Starter at $9 a month, Pro at $19 and Business at $39.