How to Remove Duplicates in Excel: 3 Fast Methods

Quick answer: The fastest way to remove duplicates in Excel is Data > Remove Duplicates: select the range, tick the columns that define a duplicate, and confirm. To find duplicates without deleting anything, use a helper column with =COUNTIF($A$2:$A$100, A2)>1 or a conditional formatting rule. Remove Duplicates keeps the first occurrence and deletes the rest permanently, so work on a copy or use a helper column when the data matters.

Part of our Excel series. For the lookup formulas that often follow a cleanup, see XLOOKUP, IF and SUMIF.

Method 1: Remove Duplicates

This is a destructive, one-click cleanup. It is the right tool when you have a fresh export and simply want the distinct rows.

1. Select the whole data range (or one cell inside it)
2. Data > Remove Duplicates
3. Tick the columns that must match for a row to count as a duplicate
4. OK — Excel reports how many duplicates were removed

The column checkboxes are the important part. Ticking only Email removes rows that share an email even when every other field differs; ticking Email and Date only removes rows that match on both. There is no undo once you confirm, so press Ctrl+Z immediately if the count looks wrong, or run it against a copy of the sheet.

Method 2: a helper column that flags duplicates

When you need to review before deleting, a formula column is safer. It leaves the original rows untouched and shows exactly which ones repeat.

=COUNTIF($A$2:$A$100, A2)          // 1 = unique, 2+ = duplicated
=COUNTIF($A$2:A2, A2)>1            // TRUE from the second occurrence on
=SUMPRODUCT(($A$2:$A$100=A2)*($B$2:$B$100=B2))>1  // duplicates on two columns

The second formula is the most useful: anchored at the top ($A$2) but open at the current row (A2), it returns FALSE for the first time a value appears and TRUE for every repeat. Filter on TRUE, delete those rows, then remove the helper column. Filtering for TRUE keeps the first occurrence, which matches how Remove Duplicates behaves.

Helper formula	Returns	Use it to
COUNTIF($A$2:$A$100, A2)	A count per value	Count how many times each entry appears
COUNTIF($A$2:A2, A2)>1	TRUE for repeats	Filter and delete all but the first occurrence
SUMPRODUCT over two columns	TRUE for matching pairs	Find rows duplicated on two fields
TRIM(A2)	Cleaned text	Strip stray spaces that hide duplicates

Method 3: highlight, then decide

Conditional formatting makes duplicates visible without changing a single value — often all you need to spot a data-entry problem before it reaches a report.

Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values
— or a formula rule:  =COUNTIF($A$2:$A$100, A2)>1

Method	Removes rows?	Best for
Remove Duplicates	Yes, immediately	One-off cleanup of an export
Helper column + filter	Only when you delete	Reviewing before removing
Conditional formatting	No	Spotting problems while data is live
UNIQUE function	No, returns a list	Building a distinct dropdown or summary
Power Query	On refresh	Recurring imports you clean the same way every time

The UNIQUE function and Power Query

On modern Excel, =UNIQUE(A2:A100) spills a de-duplicated list into the cells below, updating automatically when the source changes. It is the cleanest way to feed a distinct list into a data validation dropdown. When the cleanup must repeat every week on a new file, Power Query is the professional answer: import, remove duplicates once, and every future refresh repeats the same steps without you touching a menu.

=UNIQUE(A2:A100)                       // distinct values, spills down
=UNIQUE(A2:B100)                       // distinct rows across two columns
=SORT(UNIQUE(A2:A100))                 // distinct, alphabetised

Once duplicates are gone, pivots and lookups behave predictably — the summaries in Excel pivot tables double-count when the same key appears twice, and the transformation steps in Excel charts and Power Query are exactly where a de-duplication step belongs.

Common mistakes

• Ticking the wrong columns. The definition of “duplicate” is whatever you tick, so choose deliberately.
• Running it on the original. Remove Duplicates is destructive with no reliable undo for large deletions.
• Ignoring hidden spaces. "Ana" and "Ana " are different values; TRIM the column first.
• Case expectations. Excel’s duplicate logic ignores case in most counts, so “abc” and “ABC” may collapse.
• Removing rows from a table with references. Do it on a copied range when other sheets point at the rows.

Cleaning a messy export and want to do it faster next time? Ampersand Academy teaches Excel one-to-one, using your own workbook as the curriculum.

Frequently asked questions