Remove duplicates in Excel: highlight, delete, and check first

To remove duplicates in Excel: select the range, go to Data > Remove Duplicates, check the columns that should count toward a duplicate, and select OK. To find duplicates without deleting anything: select the cells and go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Microsoft’s guidance is to do those two in the opposite order, review first and delete second, because the delete step removes whole rows permanently and reports back only a count.

This page covers the whole job: how to highlight duplicates, what Excel actually counts as a duplicate, what the column checkboxes in the Remove Duplicates box really delete, the filter that produces a unique list without touching the data, and the formula that marks repeats.

How do I highlight duplicates in Excel?

Conditional formatting is the review step. It marks the duplicate data in place, so you can review the duplicates and decide whether any should go:

1. Select the cells you want to check for duplicates.
2. Select Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. In the box next to values with, pick the format the duplicates should get, then select OK.

The duplicate values now carry the format, and nothing has been removed. Duplicate Values is one entry under Highlight Cells Rules; the rest of conditional formatting, from data bars to formula rules, is in conditional formatting in Excel. One documented limit: Excel cannot highlight duplicates in the Values area of a PivotTable report.

What does Excel count as a duplicate?

Microsoft’s definition works row by row: a duplicate is a row whose every value is identical to the values of another row. Two rows that agree in three columns out of four are not duplicates; the match has to be complete across the columns being compared.

The subtler part is what “identical” means. Per Microsoft’s page on unique and duplicate values, “A comparison of duplicate values depends on what appears in the cell”, rather than on whatever value sits underneath the format. Its example is a date: the same date value in two cells, one displayed as 3/8/2006 and the other as Mar 8, 2006, counts as unique, not duplicate. The two cells store the same thing and show different things, and the comparison goes by what they show. If a deduplication pass leaves repeats behind, differently formatted dates are a prime suspect; how one serial number ends up displayed in several formats is covered in Excel date formats.

How do I remove duplicates in Excel?

Before running it, make a copy. Remove Duplicates deletes permanently, and Microsoft’s advice on both of its pages is to copy the original data somewhere safe first, a separate worksheet or workbook, so nothing is lost by accident.

Here is a five-row roster with one true duplicate in it:

To run it:

1. Select the range of cells, or make sure the active cell sits inside a table.
2. Select Data > Remove Duplicates. On the ribbon it sits in the Data Tools group.
3. Under Columns, check the columns that should count toward a duplicate. For the roster above, leave both Name and Dept checked.
4. Select OK.

Excel keeps the first occurrence of each duplicated value and deletes the later matches, then reports back with a message: either the number of duplicate values removed or the number of unique values that remain. Select OK to dismiss it. Two documented caveats about that message:

• The counts it gives might include empty cells, stray spaces, and the like, so treat them as a summary rather than an audit.
• Undo, or Ctrl+Z, reverses the removal if it went wrong.

The command touches only the selected range or table; cells outside the selection stay put. And it refuses outlined or subtotaled data outright: remove the outline and the subtotals first, then deduplicate.

What do the column checkboxes actually delete?

The Columns list in the Remove Duplicates box is the setting that decides how much gets deleted, so give it a careful look before you select OK. Select All and Unselect All flip every checkbox at once, and with a wide table it is easier to clear everything and then check the two or three columns that matter.

What the checkboxes control is the comparison, not the deletion. The checked columns form the key Excel uses to find duplicates, and Microsoft spells out the consequence: “If a duplicate is found in those columns, then the entire row will be removed, including other columns in the table or range.” Unchecking a column does not shield its data. It only removes that column from the key, which usually means more rows match and more rows go.

Run the same roster again with only Name checked:

Sam Ortiz worked in Finance on one row and Marketing on the other. With Dept out of the key, the second row became a duplicate of the first, and the whole row went, department included. Nothing in the message flags this: it gives a count and never lists the rows it removed.

How do I check for duplicates without deleting anything?

Microsoft’s guidance before any removal is to try filtering on unique values, or conditionally formatting them, first, to confirm the result is what you expect. The highlight rule above is one half of that; the Advanced filter is the other, and it doubles as the way to produce a clean unique list while leaving the source untouched.

1. Select the range of cells, or make sure the active cell sits inside a table.
2. Select Data > Advanced, in the Sort & Filter group.
3. In the Advanced Filter box, choose Filter the list, in-place to hide duplicate rows where they stand, or Copy to another location and put a cell reference in the Copy to box to write the unique rows somewhere else.
4. Check Unique records only, then select OK.

Filtering only hides the duplicates for the moment. Clear the filter and they are all still there, the opposite of Remove Duplicates and its permanent deletion.

Which formula finds duplicates?

A COUNTIF helper column marks every value that appears more than once, which gives you a flag you can sort, filter, or eyeball before deciding anything:

Fill the formula down and every repeated name shows TRUE. The shape is one Microsoft itself documents: its unique-values page gives =COUNTIF($A$2:$A$400,A2)>1 as a conditional formatting formula for identifying duplicate values in A2:A400. Note the difference in scope from the ribbon command, visible in the grid: COUNTIF here reads a single column, so both Sam Ortiz rows come up TRUE even though a whole-row comparison would keep them both. How COUNTIF and its criteria argument work, including the absolute reference that pins $A$2:$A$6 while A2 slides, is covered in COUNTIF and SUMIF.

Does Remove Duplicates work in Excel for the web?

Yes. Microsoft’s web instructions run the same way: select the range or a cell in the table, select Data > Remove Duplicates, unselect any columns you do not want to count toward a duplicate, and select OK; a message reports how many duplicate values were removed. The same whole-row warning applies, and the web page adds that Undo can always bring the data back afterward, while still advising the copy-to-another-worksheet step first. The highlight rule and the Advanced filter are documented under the Windows procedures.

The short version

• Review first: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values marks duplicates without deleting anything, and Microsoft’s own order is to look before removing.
• A duplicate is a whole-row match, judged by what appears in each cell, so the same date under two formats counts as unique.
• Data > Remove Duplicates keeps the first occurrence, permanently deletes the later matches, and reports a count. Copy the original data somewhere safe before running it; Undo works if you catch a mistake in time.
• The column checkboxes set only the comparison key: a row that matches on the checked columns is removed whole, unchecked columns included.
• Data > Advanced with Unique records only filters or copies a unique list and leaves the source intact, and a =COUNTIF($A$2:$A$6,A2)>1 helper column flags repeats in one column.
• Clearing empty rows is the neighboring cleanup: remove blank rows safely, without deleting rows that are only blank in one column.
• After a cleanup on a workbook other people rely on, compare the deduplicated file with the original: the comparison shows each removed row’s cells, so the count in the message becomes a list you can verify.