How to Detect Duplicate Invoices in Excel
AccountingTable of Contents
ToggleHow to Detect Duplicate Invoices in Excel
To detect duplicate invoices in Excel, compare several fields together, preserve the original invoice number, and use a second check for formatting differences or repeated amounts. Review the flagged records against documents and payment history before deciding what to do.
By Taylor Frizzell. Updated November 4, 2026. All examples below are fictional.
Download the duplicate invoice checker
Download the free Excel duplicate invoice checker (.xlsx). The workbook supports 100 invoice rows and uses Excel 2016 or later, including Microsoft 365. It has no macros or external data connections. English formulas use commas; regional separators may differ.
1. Prepare an invoice-level export
Keep an untouched source export in approved restricted storage, then work on a copy. Use one row per invoice, rather than one row per invoice line, allocation, or payment. Otherwise, ordinary line items can look like duplicates. Include paid and unpaid invoices across an appropriate history window so an old payment is not overlooked.
| Column | Required input | Practical rule |
|---|---|---|
| A | Record ID | Stable reference to the original transaction |
| B | Vendor ID | Use the accounting system ID, not a loosely typed name |
| C | Original invoice number | Store as Text before importing to preserve leading zeros |
| D | Invoice date | Real Excel date without a time component |
| E | Signed amount | Numeric total in USD; credit notes remain negative |
| F | Currency | This edition accepts USD only |
For multiple legal entities, use an entity-specific vendor key or review each entity separately. The same supplier invoice may legitimately appear in different entities. Do not combine different currencies or substitute payment dates for invoice dates.
2. Replace the fictional inputs safely
- Read the Instructions sheet and first test the supplied examples.
- Clear the sample inputs in A7:F106 and sample review entries in K7:L106. Preserve formulas in G:J and M:O.
- Paste up to 100 invoice records into A:F. Do not paste across calculated columns.
- Fix every “Fix required fields” result in H before interpreting the flags.
- Filter I:J and investigate related records. Keep review status in K and evidence notes in L.
Input cells are amber. Use the table filters to sort whole records so review notes stay attached to their record IDs. Do not sort a single column. Rows beyond 106 require extending every comparison range, formula, validation and table boundary; simply adding a new row does not expand the fixed comparison ranges.
3. Normalize invoice numbers without changing the original
Column G provides a comparison version. It uppercases text, replaces nonbreaking spaces, removes basic control characters, then removes ordinary spaces, hyphens and slashes. The prefix I: keeps the comparison key visibly textual. It does not belong to the vendor's original invoice number.
=IF(C7="","",UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
TRIM(CLEAN(SUBSTITUTE("I:"&C7,CHAR(160)," "))),
" ",""),"-",""),"/","")))AB-002 and ab 002 both become I:AB002. This is deliberately a broad comparison. Removing separators can also collapse different valid invoice numbers. It does not remove every Unicode character, repair OCR errors, or restore leading zeros lost during import.
Keep wildcard characters literal
The workbook uses M:O as comparison helpers. They add the equality operator and escape Excel's tilde, asterisk and question mark. Without escaping, an invoice such as A*1 can match unintended text. For example, M7 is:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("="&B7,
"~","~~"),"*","~*"),"?","~?")N7 uses C7 instead of B7; O7 uses G7. Keep these helpers. Microsoft's COUNTIFS documentation explains multiple criteria and wildcard behavior. These text comparisons are case-insensitive.
4. Flag exact duplicate records
Column I flags all members of a group with the same vendor, original invoice number, invoice date, signed amount and currency. “Exact” describes this field combination, not a conclusion about the underlying transaction. H must show Ready first.
=IF(H7<>"Ready","",IF(COUNTIFS(
$B$7:$B$106,M7,$C$7:$C$106,N7,
$D$7:$D$106,D7,$E$7:$E$106,E7,
$F$7:$F$106,F7)>1,"Exact duplicate flag",""))In the sample, R001 and R002 both show an exact flag: vendor V001, invoice INV-1001, October 1, 2026 and $1,250. The flagged total is $2,500, but the possible excess payment is only $1,250 if one invoice is valid and both were paid. Do not describe the sum of flagged rows as money lost or recovered.
5. Review potential duplicates separately
Column J first points to any exact flag. Otherwise, it flags either the same vendor and normalized invoice number, regardless of amount or date, or the same vendor and nonzero signed amount within seven calendar days on either side of the current invoice date. Currency must also match.
=IF(H7<>"Ready","",IF(I7<>"","Review exact flag",
IF(OR(COUNTIFS($B$7:$B$106,M7,$G$7:$G$106,O7,
$F$7:$F$106,F7)>1,
AND(E7<>0,COUNTIFS($B$7:$B$106,M7,
$E$7:$E$106,E7,$D$7:$D$106,">="&D7-7,
$D$7:$D$106,"<="&D7+7,$F$7:$F$106,F7)>1)),
"Potential duplicate flag","")))| Sample records | Expected result | Review question |
|---|---|---|
| R003 and R004 | Normalized-number potential flag | Is the second document a resubmission of AB-002? |
| R005 and R006 | Amount/date potential flag | Are these separate rent periods? A valid recurring charge is a possible false positive. |
| R007 and R008 | No broad amount/date flag | Zero is a valid input, but excluded from that broad test |
| R009 and R010 | No match | The asterisk in A*1 is treated literally |
| R011 | Fix required fields | Obtain the missing invoice number |
| R012 | No match | Retain the negative sign on the credit |
6. Investigate and document the decision
- Locate the original invoice, purchase order or contract, receiving evidence and approval record.
- Compare service dates, invoice totals, tax, credit notes and installment terms. Confirm whether the export has repeated line-level records.
- Search the vendor ledger and bank/payment record for actual settlement. A duplicate bill is different from a duplicate payment.
- Ask an authorized independent reviewer to resolve uncertainty. Use established vendor contact details if clarification is needed.
- Record the related record IDs, evidence location, reviewer and disposition in the notes. Choose Investigating, Confirmed duplicate, Valid transaction or Correct input.
- Make corrections or recovery requests through the approved accounting process. Preserve the original evidence and audit trail.
For example, R006 might be marked Valid transaction after an independent reviewer checks November's lease obligation. R002 might be a confirmed duplicate entry but still unpaid. Those outcomes require different actions.
Limitations and safe data handling
This checker will miss some cross-vendor duplicates, split invoices, altered amounts, typos, out-of-window dates and patterns absent from the export. Shared vendor IDs can create false positives. Approval fraud, fictitious suppliers and payment redirection can occur without any duplicate. Compare exports with source-system record counts and totals, and investigate missing or invalid inputs rather than treating blank flags as a pass.
Keep real exports in approved access-controlled storage. Exclude bank account details, tax IDs and personal information that this check does not need. Do not upload sensitive invoice data to public tools or unapproved AI services. Share only the minimum information required for an authorized review.
Frequently asked questions
Should I use Remove Duplicates?
Use flags first. Removing rows can destroy review evidence and accidentally remove valid line items or recurring invoices. Keep the source intact and resolve exceptions through an approved process.
Can two invoices have the same number?
Different vendors can use the same number. A vendor may also reuse numbering across accounts or years. Check the actual business context and expand the key when needed.
Does a duplicate mean fraud?
No. Fraud involves intentional deception. This workbook identifies patterns for review and cannot determine intent.
Can this check mixed currencies?
This edition requires USD. Review other currencies separately or redesign and test the input validation and comparison logic before use.
Continue learning
Use the accounts payable aging guide to reconcile vendor balances and plan payments. Learn the broader formula approach in SUMIFS, COUNTIFS and XLOOKUP.
Use duplicate checks within a wider control process
Continue with the accounts payable fraud detection guide for red flags, investigation steps and payment controls. Strengthen supplier identities with vendor master data management and document corrections using financial data quality checks. A duplicate candidate remains an exception to investigate, not proof of fraud.