Excel Guides

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.

Practice download: Get the overlapping CSV workbook. The worked example below separates repeated copies, conflicting versions and missing IDs across accounts.

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 IDLine numberAmount
INV-1011100
INV-1011100
INV-1021200
INV-1021250
INV-103180
INV-103240

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 IDAmountMatching rows
INV-1011002
INV-1011002
INV-1022001
INV-1022501
INV-103801
INV-103401

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.

Practice with overlapping CSV exports

Download the overlapping CSV practice workbook. This is a separate synthetic example from the invoice table above. Its Raw Imports sheet preserves eight source rows from three fictional exports, totaling $780.

In this fictional system, IDs are local to each account. Use account + transaction ID + currency as the key, then compare amount and state. Source filename and export date describe each copy; they do not identify the underlying transaction. Confirm your own system’s key rules before applying this.

CaseWhat to keep or reviewSource cells
Matching copiesAccount A TX-100 is $100 Settled in both files. Keep one copy.‘Raw Imports’!A2:G2 and A5:G5
Conflicting versionsAccount A TX-102 is $80 Pending, then $95 Settled. Keep both versions open for review.‘Raw Imports’!A4:G4 and A6:G6
Same ID, different accountAccount B TX-100 for $60 is separate under this sample’s rules. Keep it.‘Raw Imports’!A8:G8
Missing IDAccount A has $25 with a blank transaction ID. Leave it unresolved.‘Raw Imports’!A9:G9

The four uncontested transactions are $100 + $200 + $120 + $60 = $480, at 'Raw Imports'!F2:F3, F7 and F8. This is a subtotal, not a final reconciled total. One repeated $100 copy, both conflicting versions and the missing-ID row stay outside it.

The Import review sheet reports one repeated copy, two conflicting rows, one missing key and four kept rows. Its COUNTIFS formulas compare the scoped key and then amount/state. They cover only the supplied rows. Extend ranges and include other material fields when adapting them. Text keys are standardized in this example; check whitespace and case rules in the real source.

A later export date alone does not prove that $95 replaces $80. Ask for the source system’s revision rule or authoritative transaction detail. Selecting all columns for duplicate removal would also keep the repeated $100 copy because its filename and export date differ.

Power Query has a further distinction from Excel’s Remove Duplicates button: Microsoft documents that Table.Distinct does not generally guarantee which matching row is retained. Establish the version decision before removing records.

Try Raw Imports as the source in Jolene.pro:

IDs are local to each account. Use account, transaction ID and currency as the key. Compare amount and state. Identify repeated copies, conflicting versions and missing keys with source rows. Keep one matching copy, set unresolved records aside and calculate the uncontested subtotal. Is Exported on sufficient to choose the authoritative conflicting version?

Expected subtotal: $480, with Account B’s TX-100 retained and both Account A TX-102 versions unresolved. The figures and cell references were independently checked against the synthetic workbook. A live Jolene.pro result has not been verified.

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.

2 comments

Leave a comment