Excel Remove Duplicates: 3 Methods and What Gets Deleted

Highlight, filter, delete: three tools with different risk

Microsoft's page names three places. Conditional formatting is on the Home tab, Advanced filter is under Data > Sort & Filter, and Remove Duplicates is under Data > Data Tools. Filtering for unique values hides duplicates temporarily, and removing them deletes the rows for good.

Microsoft recommends that you filter or conditionally format first, to confirm the result, and only then remove. It also says to copy the original data to another sheet before deleting.

Method 1: highlight with Duplicate Values

Select the cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Pick a format and select OK. Nothing is deleted, so you can review each match.

This does not work in the Values area of a PivotTable. For Japanese and other double-byte text, the rule treats halfwidth and fullwidth characters as the same; Microsoft's workaround is a formula rule such as =COUNTIF($A$2:$A$400,A2)>1 for the range A2:A400.

A rule that shows only duplicates or only unique cells

For more control, choose Conditional Formatting > Manage Rules > New Rule and pick Format only unique or duplicate values. In the Format all list, choose unique or duplicate, then select Format to set a fill or font. The Preview panel shows the look before you confirm. On Mac the equivalent is New Rule with Style set to Classic.

Method 2: Advanced Filter with Unique records only

Select the range, then Data > Advanced. Choose Filter the list, in-place, or Copy to another location and type a cell in the Copy to box. Tick Unique records only and select OK.

With the copy option, the original data is not affected, so this is the safe way to get a clean list without a formula.

Method 3: Data > Remove Duplicates

Select the range, or make sure the active cell is in a table. On the Data tab choose Remove Duplicates. Under Columns, tick the columns that form the key. Select OK, and a message tells you how many duplicates were removed or how many unique values remain. Undo with Ctrl+Z if you act right away.

A worked example: the same 6 rows, three different results

These are sample values in A2:C7, with columns Name, City and Price.

Row 2 Anna, Oslo, 10. Row 3 Ben, Rome, 12. Row 4 Anna, Oslo, 15. Row 5 Anna, Rome, 10. Row 6 Ben, Rome, 12. Row 7 Cara, Oslo, 10.

• All 3 columns ticked: only row 6 repeats row 3 exactly. 1 row is removed and 5 remain.
• Name and City ticked: rows 4 and 6 repeat earlier Name and City pairs. 2 rows are removed, 4 remain. Row 2 stays with Price 10, and the 15 in row 4 is deleted with its row.
• Name only ticked: rows 4, 5 and 6 repeat a name. 3 rows are removed, 3 remain, one each for Anna, Ben and Cara.

The first occurrence stays, and unticked columns go too

Microsoft states that the first occurrence of each value is kept and later identical ones are deleted. It also says that data is removed from all columns, even those you left unticked. The key is the combination of the ticked columns, and when it matches, the entire row is removed.

That is why the Price of 15 disappears in the example. If the price matters, sort the rows first so the one you want to keep sits on top. Microsoft's own example for keeping January price data is to untick the January column.

Excel compares what you see, not what is stored

Duplicates are judged by the displayed value. "3/8/2006" and "Mar 8, 2006" count as unique, and so do 1.00 and 1 when the number formats differ. Cells with different formulas count as duplicates if they show the same value in the same format.

Microsoft notes that the duplicate and unique counts after removal might include empty cells and cells with spaces. Google's UNIQUE page gives a related warning: rows that look identical can differ by hidden text such as trailing spaces. Clean those first with a TRIM column if look-alike rows survive.

When Remove Duplicates refuses to run

You cannot remove duplicates from data that is outlined or has subtotals. Remove both first. Only the selected range or table is changed. Values outside it are not altered or moved.

If an unexpected message appears and you cannot tell what it asks, Crade can read the screen and explain it.

Excel for the web and the UNIQUE function

On Excel for the web, select the range, open the Data tab, choose Remove Duplicates and clear any column you do not want in the key. The message reports only the number removed.

Excel 2021, 2024 and Microsoft 365 also have UNIQUE, a formula that lists distinct rows without touching the source. Excel 2019 and 2016 do not, so the three methods above are the options there.

Which method suits which job

Highlighting suits a check before any change. Advanced filter with the copy option suits a clean list while keeping the original. Remove Duplicates suits a final clean-up of a copy. When rows must be compared on 2 or 3 columns, the key choice in Remove Duplicates already handles it.