Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
Two lists such as bank UTRs and a payment register must be compared, and the unmatched entries are hard to see by eye.
Apply a conditional-formatting rule with COUNTIF that returns zero when a value is missing from the other list, plus rules for amount mismatches on the same key and for old open items, then filter by colour into an exception list.
Both lists on separate sheets or columns, formatted as Excel Tables, with four formula-based conditional-formatting rules and a separate exception sheet.
Every unmatched or mismatched item is coloured and copied into an exception list with reason, owner and resolution date.
Select the first column. Add a conditional-formatting rule that uses COUNTIF to test whether each value exists in the other column. Colour the cells where the count is zero. Those coloured cells are the entries missing from the other list.
The rules below work in Excel 2010 and later, including Microsoft 365. The FILTER function near the end needs Excel 2021 or Microsoft 365.
How do I set up the first rule?
Say the bank extract is on a sheet called Bank, with UTRs in B2:B341 and amounts in D2:D341. The payment register is on a sheet called Register, with UTRs in B and amounts in D.
- On the Bank sheet, select B2:B341.
- Go to Home, Conditional Formatting, New Rule.
- Choose “Use a formula to determine which cells to format”.
- Enter:
=COUNTIF(Register!$B:$B,$B2)=0 - Click Format, choose a light red fill, and click OK.
Note the dollar signs. Register!$B:$B is fixed so every row looks at the whole register. $B2 has no dollar before the 2, so each row tests its own UTR.
Which four rules are most useful?
| Rule | Formula (applied from row 2) | Suggested colour |
|---|---|---|
| In bank, missing from register | =COUNTIF(Register!$B:$B,$B2)=0 | Light red |
| In register, missing from bank (set on Register sheet) | =COUNTIF(Bank!$B:$B,$B2)=0 | Light orange |
| Same UTR, different amount | =AND(COUNTIF(Register!$B:$B,$B2)>0, SUMIF(Register!$B:$B,$B2,Register!$D:$D)<>$D2) | Yellow |
| Unmatched and older than 30 days | =AND(COUNTIF(Register!$B:$B,$B2)=0, TODAY()-$A2>30) | Dark red, bold text |
Rule 3 assumes each UTR appears once in the register. If one UTR covers several register lines, SUMIF adds them, which is usually what you want.
Rule 4 needs real dates in column A. If the dates are text, clean them first.
Worked example: 12 missing UTRs out of 340
A finance executive has September’s bank extract: 340 debit lines. She applies rule 1 against the payment register. Twelve cells turn red.
She sorts by amount to see the biggest first:
| Date | UTR | Amount | Likely reason |
|---|---|---|---|
| 3 Sep | HDFCR52026090300811 | ₹2,40,000 | Vendor advance not entered in register |
| 11 Sep | 425319876543 | ₹1,18,000 | IMPS payment entered with wrong UTR |
| 18 Sep | SBIN426261234567 | ₹96,000 | Payroll top-up made outside the register |
| 9 other lines | ₹1,14,200 | Bank charges, small refunds and one duplicate |
Total: ₹2,40,000 plus ₹1,18,000 is ₹3,58,000. Add ₹96,000 for ₹4,54,000. Add ₹1,14,200 for ₹5,68,200 across the 12 lines.
So 328 of 340 lines match on UTR. The month’s debits total ₹90,00,000; the matched lines account for ₹84,31,800 and ₹5,68,200 needs explaining. Without the rule she would have scanned 340 lines by eye.
How do I colour the whole row and keep colours after sorting or new rows?
Whole row: select A2:F341 instead of one column, and keep the same formula with $B2. The dollar before B makes every cell in the row test column B.
After sorting: the rule recalculates for each row, so colours follow the data.
New rows next month: turn each list into an Excel Table (select the data, Ctrl+T). Conditional formatting applied to the whole Table column extends to rows you add or paste below it. Check Home, Conditional Formatting, Manage Rules to confirm the “Applies to” range grew.
How do I turn coloured cells into an exception list an auditor can read?
Colour is a view, not a record. Turn it into a list:
- Click the filter arrow on the UTR column, choose Filter by Colour, and pick red.
- Copy the visible rows to a new sheet called Exceptions.
- Add columns: reason, owner, action taken, date resolved, reviewed by.
In Excel 2021 or Microsoft 365, you can build the list with one formula instead:
=FILTER(Bank!A2:F341, COUNTIF(Register!B2:B500, Bank!B2:B341)=0, "No exceptions")COUNTIF with a range as its second argument returns one count per bank line, and FILTER keeps the rows where the count is zero.
Save the Exceptions sheet with the month’s reconciliation. That is the record an auditor will ask for.
What to read next
Next, read how to use a pivot table to find the difference between two ledgers. Colour is detection, not a record of who resolved what; for that, see the reconciliation control plan template and manual detection techniques for reconciliation.
TransactIG matches bank statement lines to your books and keeps every unmatched line in an exception queue with audit-ready exports; see bank reconciliation software for India.