How to find, highlight and remove duplicates in Excel

Spreadsheet tools · Published September 27, 2026

Highlight duplicate values, count how often each one appears, list unique values, and remove duplicate rows safely without losing data you need.

The short answer

To see duplicates, select the column and choose Home › Conditional Formatting › Highlight Cells Rules › Duplicate Values. To count them, use =COUNTIF($A$2:$A$7,A2) next to each value. To delete them, use Data › Remove Duplicates, but only on a copy, because it deletes rows for good.

Highlighting and counting change nothing, so start there and decide what to remove once you can see the duplicates.

Highlight duplicates

1. Select the cells to check, such as A2:A7.
2. Choose Home › Conditional Formatting › Highlight Cells Rules › Duplicate Values.
3. Keep Duplicate in the first box, pick a color and select OK. Choose Unique instead to highlight values that appear only once.

Every copy is highlighted, including the first one. To remove the highlighting later, use Home › Conditional Formatting › Clear Rules.

Count how often each value appears

COUNTIF counts the cells in a range that match a value:

=COUNTIF($A$2:$A$7,A2)

The first argument is the whole list. The dollar signs lock it, so it doesn't move when you copy the formula down. The second argument, A2, is the value to count, and it does move: row 3 counts A3, and so on. Any result above 1 is a duplicate.

Flag only the second and later copies

Often you want to keep the first occurrence and mark only the repeats. Lock just the start of the range:

=IF(COUNTIF($A$2:A2,A2)>1,"Repeat","")

In row 2 the range is only A2; in row 7 it is A2:A7. So each row counts how many times its value has appeared so far. The first copy counts 1 and stays blank; later copies count 2 or more and show Repeat. The IF function turns that into a label, and you can filter on it.

COUNTIF ignores case, so ANA@example.com and ana@example.com count as the same value. It does notice extra spaces, so "ana@example.com " with a trailing space counts as different.

Duplicates across several columns

Sometimes a row is only a duplicate when several columns match, such as first and last name. You have two options.

COUNTIFS

COUNTIFS takes pairs of range and value, and counts rows where all of them match:

=COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,B2)

By name, both Marias Lopez look like duplicates, but they live in different cities. Only James Chen in Austin repeats across all three columns. The running version works here too: =COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1 flags only the later copies.

A helper column

Join the columns into one key with =A2&"|"&B2, which gives Maria|Lopez. The | keeps values apart: without it, order 12 with line 3 and order 1 with line 23 would both become 123. Then use COUNTIF or the Duplicate Values highlight on the key column. See how to combine text for more on joining cells.

List each value once with UNIQUE

In Microsoft 365, Excel 2021, Excel 2024 and Excel for the web, UNIQUE builds a list without duplicates and leaves the original untouched:

=UNIQUE(A2:A7)

With the email list above, it returns ana@example.com, ben@example.com and cara@example.com, spilling down into the cells below the formula. Keep those cells empty, or you'll see #SPILL!. The optional arguments are by_col (TRUE compares columns instead of rows) and exactly_once: =UNIQUE(A2:A7,,TRUE) returns only values that appear once, here just cara@example.com. Give UNIQUE several columns, such as A2:C6, and it compares whole rows.

Older versions show #NAME?. Use the Advanced Filter below instead.

Remove duplicate rows

1. Click any cell in your data. Remove any subtotals or outlines first.
2. Choose Data › Remove Duplicates (in the Data Tools group).
3. Tick My data has headers if row 1 holds headings. Then tick only the columns that must match for two rows to count as duplicates. With all columns ticked, only identical rows are removed.
4. Select OK. Excel says how many duplicates it removed and how many unique values remain.

Excel keeps the first occurrence and deletes the later ones, so sort the data first if you want to keep, say, the newest row. It compares values as they are displayed: Microsoft's example is that 3/8/2006 and Mar 8, 2006 are treated as different. Extra spaces also make values look different, so clean up the text first.

Older alternative: Advanced Filter

1. Click a cell in the data.
2. Choose Data › Sort & Filter › Advanced.
3. Select Copy to another location, enter an empty cell in Copy to, and tick Unique records only.
4. Select OK. A list without duplicates appears, and the original stays as it was.

Checking a CSV file before you open it in Excel

If your list is a CSV export, Filecon's CSV viewer is a quick way to look for repeats without Excel changing values such as IDs with leading zeros. Sort a column so identical values sit next to each other, or type a value in the column filter to see every row that contains it. It doesn't count or remove duplicates, and the file isn't uploaded.

Troubleshooting

• Values look identical but aren't flagged: one probably has a trailing space, a non-breaking space or a number stored as text. Compare them with =LEN(A2).
• COUNTIF counts more than you expect: it ignores case, and the range may include the header or blank rows. Check the locked range.
• Remove Duplicates deleted too much: press Ctrl+Z straight away, then run it again with more columns ticked.
• Comparing two separate lists: see how to compare two lists.

In short

Highlight duplicates to see them, use COUNTIF (or COUNTIFS for several columns) to count and flag them, and UNIQUE or the Advanced Filter to list each value once. Use Remove Duplicates last, on a copy, knowing it keeps the first occurrence.