AP controls
How to Find Duplicate Invoices in Excel Before You Pay Twice
A practical, explainable workflow for finding exact duplicates, formatting variants, and conflicting invoice records before an AP payment run.
Key takeaways
- Normalize invoice references before comparing them.
- Separate exact duplicates from conflicts that require judgment.
- Keep source-row evidence so reviewers can resolve findings quickly.
- Run the check before every payment proposal, not only after an incident.
01
Why simple duplicate highlighting misses real problems
A spreadsheet's built-in duplicate rule only answers a narrow question: does this cell contain the same visible value as another cell? Accounts payable teams need a stronger question: do two rows represent the same commercial obligation? The difference matters because invoice references are frequently entered with spaces, punctuation, prefixes, inconsistent case, or leading zeros.
A supplier might submit invoice INV-1042 while another source records inv 1042. A second row may repeat the vendor and invoice reference but carry a different amount. That is not an exact duplicate; it is a conflict that deserves immediate review. Treating both cases as one red warning creates noise and makes the most important exceptions harder to see.
02
Prepare a clean comparison key
Start with the fields that define an invoice in your process: vendor identifier, invoice number, currency, amount, and invoice date. Vendor name alone is often unreliable because abbreviations and legal suffixes vary. Prefer a stable vendor code when it is available.
Normalize the invoice reference for comparison, but never overwrite the original value. Keep both. A useful normalization removes surrounding whitespace, converts text to one case, and treats common separators consistently. Your audit output should always show the original entry beside the normalized key.
- Trim leading and trailing spaces.
- Compare references without case sensitivity.
- Handle hyphens and spaces consistently when policy allows it.
- Parse amounts as numeric minor units rather than formatted text.
- Retain the source sheet and row number for every candidate.
03
Use three layers of duplicate review
A layered review produces better results than a single formula because each layer has a different meaning and response. Begin with the highest-confidence cases, then move toward exceptions that require investigation.
- 1
Exact duplicate
The same vendor, normalized invoice reference, currency, and amount appear more than once. Confirm whether one row is a reversal, credit, or legitimate installment before blocking it.
- 2
Conflicting duplicate
The vendor and normalized invoice reference match, but amount, currency, or date differs. This can indicate a correction, entry error, altered invoice, or duplicate with changed details.
- 3
Near duplicate
The vendor and amount match within a short date window while invoice references are similar but not identical. Use this as a review queue, not an automatic rejection rule.
04
Turn findings into a controlled review
A useful result is not a count of red cells. It is a review list with severity, rule name, source rows, original values, and a clear explanation. Assign each finding an owner and outcome such as confirmed duplicate, legitimate repeat, credit note, corrected entry, or unresolved.
Keep the payment decision outside the detection rule. Deterministic checks should surface evidence consistently; an authorized reviewer should decide whether to hold, release, or correct the item. This separation makes the control easier to explain and repeat.
- Run the review before payment approval.
- Record who resolved each high-risk finding.
- Do not delete source rows to make a report clean.
- Track recurring causes such as vendor resubmission or manual entry duplication.
05
A faster way to review the register
AuditExport applies deterministic duplicate and conflict checks inside your browser. The workbook is not uploaded to AuditExport, and each finding points back to the source row. That makes it useful as a pre-payment review layer when an ERP report or exported AP register needs an independent check.
Start with a representative export, confirm the invoice, vendor, amount, and date columns, and review the highest-severity findings first. The goal is not to replace approval controls; it is to give reviewers a cleaner, explainable exception list before money moves.
This article provides general operational guidance, not accounting, audit, tax, or legal advice. Apply your organization's policies and professional judgment.