How to Find Duplicates in Excel Without Deleting the Wrong Record
Same invoice number. Different amount. Deleting one would certainly make the spreadsheet simpler.
It might also make it wrong.
To find duplicates in Excel, highlight repeated values with Conditional Formatting or count them with COUNTIF. Before removing anything, decide which fields identify a record. A repeated invoice number could be a copied row, a revision, or another line on the same invoice.
That distinction matters more than the button you click.
Start with what one row represents
Here is a made-up invoice export. Each row is supposed to represent one invoice line, and all amounts use the same currency.
| Invoice ID | Line number | Amount |
|---|---|---|
| INV-101 | 1 | 100 |
| INV-101 | 1 | 100 |
| INV-102 | 1 | 200 |
| INV-102 | 1 | 250 |
| INV-103 | 1 | 80 |
| INV-103 | 2 | 40 |
All three invoice IDs appear twice. But they need different treatment.
INV-101 matches across all three fields. It is a duplicate candidate. Confirm that the export repeated the same invoice line before removing a copy.
INV-102 has two amounts for the same line. One might be a correction. These columns do not tell us which amount is valid. We need the invoice, revision history, or confirmation from its owner.
INV-103 contains two different lines. Under this example’s rules, both belong in the report.
Remove duplicates using only Invoice ID, and you risk treating all three situations alike.
Find repeated values without changing the data
For a quick visual check, select the Invoice ID cells, then choose:
Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values
Choose a highlight color and click OK. This marks repeated values without deleting the records, as described in Microsoft’s duplicate-highlighting guide.
In our example, every invoice ID gets highlighted.
The spreadsheet has successfully identified repetition. The business decision remains yours.
Use COUNTIF to make the check filterable
Put the example in columns A through C, with headers in row 1 and data in rows 2 through 7.
In D1, enter ID occurrences. In D2, enter:
=COUNTIF($A$2:$A$7,A2)Fill the formula down to D7. Each row returns 2 because each invoice ID appears twice.
For a larger export, extend the range to your last data row. Filter the helper column for values greater than 1 to bring repeated IDs together.
COUNTIF counts matches to one condition. Here, it answers one question: how often does this invoice ID appear?
It does not tell you whether the whole record is repeated.
Compare the fields that matter
To check whether the invoice ID, line number, and amount all match another row, enter Matching rows in E1 and this formula in E2:
=COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,B2,$C$2:$C$7,C2)Fill it down to E7. COUNTIFS checks several conditions together.
The expected results are:
| Invoice ID | Amount | Matching rows |
|---|---|---|
| INV-101 | 100 | 2 |
| INV-101 | 100 | 2 |
| INV-102 | 200 | 1 |
| INV-102 | 250 | 1 |
| INV-103 | 80 | 1 |
| INV-103 | 40 | 1 |
Only the INV-101 pair matches across all three selected fields.
Both rows are flagged. A count of 2 means two matching rows, not two rows to delete.
Your real matching rule may need more fields. If different suppliers reuse invoice numbers, include Supplier ID. If one invoice contains several items, include the line number. Check missing IDs separately instead of treating blank identifiers as proof of a duplicate.
Decide which record to keep
For a confirmed copied row, retain one copy.
For conflicting versions, use a documented rule. Perhaps your source system marks one record as approved or superseded. A later timestamp can help only if the process establishes that later versions replace earlier ones.
For legitimate invoice lines, keep both.
In this example, removing one confirmed INV-101 copy reduces the row count from six to five and the amount total from 770 to 670.
That does not make 670 the approved invoice total. INV-102 still has an unresolved conflict. If 250 is confirmed as the replacement for 200, removing the superseded 200 brings the total to 470.
A tidier file can still contain an unanswered question.
Remove duplicates after the review
Work on a copy of the original data.
Select the full table, choose Data → Remove Duplicates, and check the columns that define a duplicate under your rule.
For the confirmed INV-101 copy in this example, comparing all three fields preserves the differing INV-102 amounts and both INV-103 lines.
Excel retains the first occurrence of a matching combination. The selected columns determine the match, but removal affects the entire record within the selected range. Microsoft explains this behavior in its guide to removing duplicate values.
Afterward, check the removed-row count and the amount change. Keep a record of what you removed and why.
Reviewing the same problem in Jolene.pro
When the relevant records sit across several tabs or separated tables, you can use Jolene.pro to inspect the workbook and check the source references behind its answers.
Start with a specific request:
Find repeated invoice IDs in the selected sheets. Separate matching invoice lines from records with different amounts or line numbers. Show the source rows for each group, flag missing IDs, and leave conflicting records unresolved.
Check the referenced rows before deciding what to remove. The request defines the review you want; it does not replace that review.
Try Jolene.pro with an export you want to inspect. Start by finding out why the records repeat.
The useful outcome is a file where every remaining row has a reason to be there.