A highlighted row gets looked at. A flag in a helper column at the far right often doesn't.
Select the data range, add a conditional formatting rule that uses a formula, and use COUNTIFS to test whether another row has the same supplier and amount, or the same supplier and invoice number. Rows that match are coloured, so the reviewer sees possible duplicates without scrolling to a helper column.
Put one invoice or payment per row, with a header row in row 1:
Convert the range to an Excel Table (Ctrl+T) so new rows pick up the formatting automatically.
Select A2:D5000, then Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter:
=COUNTIFS($A$2:$A$5000,$A2,$B$2:$B$5000,$B2)>1
Choose a strong fill colour. The dollar signs matter: the ranges are fixed, but the row in $A2 and $B2 moves, and the column is locked so the whole row colours, not just one cell.
Duplicates entered under different invoice numbers often share supplier and amount, a few days apart. This rule flags another row with the same supplier and amount within 14 days either side:
=COUNTIFS($A$2:$A$5000,$A2,$D$2:$D$5000,$D2,$C$2:$C$5000,">="&$C2-14,$C$2:$C$5000,"<="&$C2+14)>1
Give it a lighter colour than rule 1, because it produces more false matches: rent, subscriptions and regular deliveries are often the same amount every month. Adjust the 14-day window to suit your payment patterns.
A payment that appears under two different supplier names can be a sign of a duplicate supplier record, or of someone redirecting a payment. This rule needs care, because many unrelated suppliers bill round amounts:
=AND($D2>=1000,COUNTIFS($D$2:$D$5000,$D2,$B$2:$B$5000,$B2)>COUNTIFS($A$2:$A$5000,$A2,$D$2:$D$5000,$D2,$B$2:$B$5000,$B2))
It colours rows where the same amount and invoice number appear under more than one supplier, and ignores small amounts below the threshold in the formula.
Colour is a prompt, not a record. Add a column for the reviewer's decision (duplicate, not a duplicate, query raised), a column for their name and the date, and keep the file with the payment run. Never delete a highlighted row to make the colour disappear; mark it instead.
COUNTIFS across thousands of rows in several rules can slow a workbook. If it becomes sluggish, move the tests into helper columns, format based on the helper result, and limit the ranges to the rows you actually use.
The formulas are identical in UK and US Excel. Dates follow the computer's regional setting, so check that invoice dates are real dates, not text, before relying on the date-window rule.
Supplier statement to AP ledger. Finds invoices you haven't posted, credit notes still to come, price differences, duplicate postings and payments in transit, and builds the supplier reconciliation. Runs in your browser on your own computer: nothing is uploaded.
Paste your invoice list and see likely duplicates in minutes. Free Excel file: enter your work email to download it.
The Accounts Payable Control System runs four duplicate-invoice tests, supplier master fraud checks and a three-way match on every invoice, with a payment run release gate that blocks release while exceptions are open. Excel, no macros, one-time price.
Because the column letters in the formula are locked with dollar signs, every cell in the row is tested against the same columns and gets the same result.
It only compares one column at a time, so it flags every repeated supplier or every repeated amount. A formula rule lets you combine supplier, number, amount and date.
Narrow the date window, exclude known recurring suppliers with an extra condition, or mark them as reviewed in a decision column and add that column to the rule.
Last updated 04 November 2026. General guidance for finance teams; check it against your own policies, systems and local rules.