Remove duplicates in Excel, or find and highlight them first

In short: Data > Remove Duplicates deletes duplicate rows for good. Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values only colors them. =COUNTIF(A$2:A2,A2)>1 flags every repeat, and =UNIQUE(A2:B9) builds a duplicate free copy while leaving the original untouched.

Try it now

Some customers appear more than once in this order list. Next to it, build a list that shows every customer and city combination only once. The result spills into the cells below by itself.

Task In E2, build a list in which every combination of customer and city appears only once. Kaya Ltd in Berlin and Kaya Ltd in Leipzig are different branches, so both must be listed.

	A	B	C	D	E	F
1	Customer	City	Order $		Customer	City
2	Kaya Ltd	Berlin	1200
3	Nordwerk	Hamburg	800
4	Kaya Ltd	Berlin	450
5	Kaya Ltd	Leipzig	990
6	Elbe Valley Inc	Dresden	300
7	Nordwerk	Hamburg	1500
8	Spree Media	Berlin	700
9	Elbe Valley Inc	Dresden	610

UNIQUE needs Excel 2021 or later, or Microsoft 365. It has two close relatives, FILTER and SORT, covered on FILTER, SORT and UNIQUE.

Remove duplicates from the ribbon

When you want to clean a list once and do not need the original any more, the built in command is the fastest route:

1. Click a cell inside the list or select the range.
2. On the Data tab, in the Data Tools group, click Remove Duplicates.
3. Under Columns, tick the columns that should be compared.
4. Click OK. A message tells you how many duplicates were removed.

Excel keeps the first occurrence and deletes the later rows. The column choice decides what counts as a duplicate. Tick only "Customer" and Kaya Ltd in Leipzig is treated as a copy of Kaya Ltd in Berlin, so the whole row goes, including the columns you did not tick. Tick "Customer" and "City" and both branches survive, just like UNIQUE in the exercise above.

Microsoft is clear that this deletes the duplicates permanently. Ctrl+Z right afterwards still brings them back. To be safe, copy the data to another sheet first.

Highlight duplicates instead of deleting them

Looking before you delete is usually the smarter move. Select the column, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Every value that occurs more than once gets colored, the first occurrence included.

That rule only colors the cells you selected. To light up the entire row, you need a formula rule instead. Select A2:C9 and choose Conditional Formatting > New Rule > Use a formula to determine which cells to format.

Task This customer list was merged from two systems. Use conditional formatting to highlight the whole rows A2:C9 whose customer number appears more than once. Write the rule formula as it applies to the first cell A2.

	A	B	C
1	Customer no.	Customer	City
2	10482	Kaya Ltd	Berlin
3	10517	Nordwerk	Hamburg
4	10233	Saxon Build	Leipzig
5	10482	Kaya Ltd	Berlin
6	10690	Elbe Valley Inc	Dresden
7	10517	Nordwerk	Hamburg
8	10345	Weser Print	Bremen
9	10771	Dom Tech	Köln

The dollar signs do the heavy lifting. $A$2:$A$9 is the fixed search range, while $A2 locks only the column, so cells in B and C check the same customer number as column A. The general idea behind rules like this is explained in conditional formatting.

Flag repeats with COUNTIF

Both highlighting methods above also mark the first occurrence. If you only want the second, third and later copies, count with a range that grows as you fill down:

Task Some invoices were entered twice. In C2, write a formula that returns TRUE if the invoice number already appeared further up. The first occurrence must stay FALSE. The formula is filled down to C9.

	A	B	C
1	Invoice no.	Amount $	Repeat?
2	RE-1041	480
3	RE-1042	1250
4	RE-1043	96
5	RE-1042	1250
6	RE-1044	310
7	RE-1045	2200
8	RE-1041	480
9	RE-1046	745

This helper column pays off: filter it for TRUE and you can delete exactly the repeats while every first occurrence stays put. Drop the >1 and =COUNTIF(A$2:A2,A2) shows the running count, 1, 2, 3. And =COUNTIF($A$2:$A$9,A2) tells you how often a value appears in the whole list. The basics of the function are on the COUNTIF page.

Why Excel misses some duplicates

Trailing spaces

"Boston" and "Boston " look identical on screen but are different values to Excel, and none of the methods above will match them. Clean the column first with =TRIM(A2). More in SUBSTITUTE and TRIM.

Upper and lower case

COUNTIF and UNIQUE treat "KAYA LTD" and "Kaya Ltd" as the same value. Most of the time that is what you want. When case matters, compare with EXACT: =SUMPRODUCT(--EXACT($A$2:$A$9,A2))>1.

Same date, different format

According to Microsoft, Remove Duplicates compares what the cell displays, not the stored value. A date shown once as 1/5/2026 and once as January 5, 2026 counts as two different entries. Give the column a single format before you start.

Comparing the wrong columns

If you tick only one column in Remove Duplicates, Excel may delete rows that differ elsewhere. Decide up front which columns together make an entry unique.

Remove duplicates in German Excel

A German Excel uses its own names for commands and functions, and arguments are separated by semicolons:

English	German
Data > Remove Duplicates	Daten > Duplikate entfernen
Highlight Cells Rules > Duplicate Values	Regeln zum Hervorheben von Zellen > Doppelte Werte
=COUNTIF(A$2:A2,A2)>1	=ZÄHLENWENN(A$2:A2;A2)>1
UNIQUE, EXACT	EINDEUTIG, IDENTISCH

Which Excel versions support this?

Remove Duplicates, the Duplicate Values rule and COUNTIF are in every current version, including Excel for the web. UNIQUE is newer: Microsoft lists it for Excel 2021, Excel 2024 and Microsoft 365 on Windows and Mac, plus Excel for the web and the iPad, iPhone and Android apps. Excel 2019 and earlier do not have it, so use the ribbon command or the COUNTIF helper column there.

FAQ