How to Remove Duplicates in Excel

How to Remove Duplicates in Excel

Three ways to delete duplicate rows in Excel: the Remove Duplicates button, the UNIQUE formula and Power Query.

Method 1: The Remove Duplicates button

This is the fastest way and it changes your data in place, so copy your sheet first if you need the original.

  1. Click any cell inside your data.
  2. Go to the Data tab and click Remove Duplicates.
  3. Tick the columns Excel should compare. To match whole rows, leave all columns ticked.
  4. Click OK. Excel tells you how many duplicates it removed and how many unique rows remain.

Excel keeps the first row it finds and deletes later matches. Matching is not case sensitive, so Apple and apple count as the same value.

Method 2: The UNIQUE function

In Excel 365 and Excel 2021 you can pull a list of unique values with a formula and leave the original data untouched.

=UNIQUE(A2:A100)

To get rows that appear exactly once, add TRUE as the third argument:

=UNIQUE(A2:A100,,TRUE)

For whole rows across several columns, use a multi-column range such as =UNIQUE(A2:C100).

Method 3: Power Query

  1. Select your data and choose Data > From Table/Range.
  2. Select the columns to compare in the Power Query editor.
  3. Right click a column header and choose Remove Duplicates.
  4. Click Close & Load. The cleaned table appears in a new sheet and refreshes when the source changes.

Highlight duplicates before deleting

Select the range, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This lets you review what would be removed.

Common problems

Extra spaces make identical looking values count as different. Clean them first with =TRIM(A2). Numbers stored as text can also cause misses, so convert them before removing duplicates.

Frequently asked questions

How do I remove duplicates in Excel but keep the first one?

Use Data > Remove Duplicates. Excel keeps the first occurrence of each value and deletes the later ones.

How do I remove duplicates without deleting the original data?

Use the UNIQUE function in a new column or sheet, or load the data through Power Query. Both leave the source untouched.

Why does Remove Duplicates not work on my data?

Hidden spaces, different capitalization in a case-sensitive check, or numbers stored as text usually cause misses. Use TRIM or convert text to numbers first.

Can Excel remove duplicates based on one column only?

Yes. In the Remove Duplicates dialog, tick only the column to compare.

Does the UNIQUE function work in older Excel?

UNIQUE is available in Excel 365 and Excel 2021 or later. Older versions can use Remove Duplicates or Power Query.

Need Excel work done for you?

Talk to us

More free tools: All free calculators · Excel automation services · Excel templates · Budget template