How to Remove Duplicates in Excel (4 Ways)

To remove duplicates in Excel, select your data and click Data → Remove Duplicates — choose which columns define a “duplicate” and Excel deletes the extra rows. To just highlight duplicates without deleting them, use conditional formatting or a COUNTIF formula. Here are all four methods.

Method 1: Remove Duplicates tool (delete them)

1. Select the range (or click any cell in the table).
2. Click Data → Remove Duplicates.
3. Tick the columns that should define a duplicate — e.g. tick only Email to delete rows with the same email.
4. Click OK. Excel deletes the extra rows and tells you how many were removed.

Important: “Remove Duplicates” deletes the later duplicate, keeping the first. Make sure your data is ordered the way you want before removing.

Method 2: Highlight duplicates (don’t delete)

To see duplicates without removing them:

1. Select the column.
2. Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
3. Pick a fill color — duplicates now light up in that color.

This is safer when you want to review before deleting. For the full guide, see how to use conditional formatting.

Method 3: COUNTIF formula (flag them)

Add a helper column to flag duplicates:

1. In a new column next to your data, type: =COUNTIF($A$2:$A$100, A2) (replace A with your data column).
2. Drag the formula down.
3. Any result greater than 1 means that value appears more than once.

You can then filter for >1 to see all duplicates, or combine it with a formula to flag just the repeats.

Method 4: UNIQUE function (extract a clean list)

In newer Excel (2021 / Microsoft 365), the UNIQUE function returns a list with duplicates removed:

=UNIQUE(A2:A100)

This extracts a de-duplicated list into a new column without touching the original data — handy when you want a clean list rather than to edit the original.

Which method should you use?

Duplicate by multiple columns

A “duplicate” can be one column or several. For example, a customer might appear twice only if both First Name and Last Name match:

• In Remove Duplicates, tick both columns.
• In COUNTIF, use =COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2).

Remove duplicates in Google Sheets

Google Sheets has the same tools, with slightly different names:

1. Select your range.
2. Data → Data cleanup → Remove duplicates.
3. Tick whether your data has a header row, and which columns to check.
4. Click Remove duplicates.

For highlighting (not deleting), use Format → Conditional formatting → Custom formula with =COUNTIF($A$2:$A$100, A2)>1. For the fundamentals, see Google Sheets formulas for beginners.

Common mistakes

• Removing before checking — highlight duplicates first (Method 2) so you see exactly what will be deleted.
• Checking the wrong columns — ticking too many columns means only exact full-row matches get removed, leaving partial duplicates behind.
• Not backing up — duplicate the sheet before mass-deleting, in case you need to undo.
• Forgetting the data is filtered — Remove Duplicates ignores filters and deletes duplicates in hidden rows too. Clear filters first (or copy just the visible rows elsewhere) if you only want to dedupe what’s shown.

FAQ

How do I remove duplicate rows in Excel?

Select the data → Data → Remove Duplicates, tick the columns that define a duplicate, and click OK. It keeps the first occurrence and deletes the rest.

How do I highlight duplicates in Excel?

Select the column → Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. See how to use conditional formatting.

How do I find duplicates based on one column?

Use Remove Duplicates and tick only that one column, or use =COUNTIF($A$2:$A$100, A2) and filter for results greater than 1.

Can I remove duplicates in Google Sheets the same way?

Yes — Google Sheets has Data → Data cleanup → Remove duplicates, and the same UNIQUE function. See Google Sheets formulas for beginners.

Is Remove Duplicates case-sensitive?

No - “Apple” and “apple” are treated as the same value (it’s case-insensitive). It ignores things like bold and colour, so a bold “Apple” and a plain “Apple” count as duplicates - but number formats do matter: 1.00 and 1 are treated as different values.

Does Remove Duplicates keep the first or last row?

It keeps the first occurrence and removes the ones below it. Order your data first if it matters — see how to sort in Excel.

Can I undo Remove Duplicates?

Not with a simple Undo after you’ve saved and closed. Before removing, duplicate the sheet (right-click the tab → Move or Copy → Create a copy) so you always have a backup.

How do I remove duplicates but keep the last row?

Sort the data in reverse order first (so the “last” row becomes first), then run Remove Duplicates — it keeps the new first (originally last) row.

How do I find duplicates across two columns?

Use Remove Duplicates and tick both columns, or a COUNTIFS formula that checks both: =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2).

Does Remove Duplicates work on the whole sheet?

Yes — select the entire table (or click any cell in it) and Excel detects the full range. Just make sure the columns you want it to check are ticked in the dialog.

Does Remove Duplicates delete the whole row?

Yes — it deletes entire duplicate rows, not just the duplicate cell. That’s why it’s important to choose which columns define a duplicate carefully.

How do I count how many duplicates there are?

Use =COUNTIF($A$2:$A$100, A2) in a helper column — every result greater than 1 marks a duplicate. Subtract 1 from each count to get the number of extra occurrences.

Can I use Remove Duplicates on a pivot table?

No — Remove Duplicates works on regular data ranges, not pivot tables. Deduplicate the source data first, then refresh the pivot table. See how to use a pivot table in Excel.