How To Remove Duplicates In Excel (2026)

Duplicate rows in an Excel list throw off totals, mailing lists and counts.

Excel has a built-in Remove Duplicates command on the Data tab that deletes repeat rows in a few clicks.

The steps below follow Microsoft’s support pages, including how to preview duplicates first and how to hide them without deleting anything.

Quick answer: remove duplicates in Excel

What you wantWhere to go
Delete duplicate rowsData > Data Tools > Remove Duplicates
Highlight duplicates firstHome > Conditional Formatting > Highlight Cells Rules > Duplicate Values
Hide duplicates without deletingData > Sort & Filter > Advanced > Unique records only
Get a unique list by formula=UNIQUE(range)
Undo a removalCtrl+Z

Remove duplicate values step by step

  1. Select the range of cells, or click a cell inside your table.
  2. Go to the Data tab and select Remove Duplicates in the Data Tools group.
  3. Under Columns, tick the columns Excel should compare, or use Select All or Unselect All.
  4. Select OK.
  5. Read the message that tells you how many duplicates were removed and how many unique values remain, then select OK.

If the result is not what you expected, select Undo or press Ctrl+Z straight away.

Make a copy before you delete anything

Microsoft warns that removing duplicates permanently deletes data.

Its advice is to copy the original range or table to another worksheet or workbook first.

Only the range or table you selected is touched, so values outside it do not change or move.

How the column choice decides what is a duplicate

Excel matches rows on the combined values of the columns you tick.

When it finds a match, it removes the entire row, including the columns you left unticked.

Columns tickedA row counts as a duplicate when
Column1 and Column2 (Column3 unticked)Both Column1 and Column2 match another row
All columnsEvery value in the row matches another row
One column onlyThat single column matches another row

That last row follows from Microsoft’s rule that the ticked columns are what get compared, so a single ticked column means that column alone decides.

Which copy Excel keeps

Microsoft says the first occurrence of a value stays and later identical entries are deleted.

If the order matters, sort the list the way you want before you run the command.

Why some duplicates are not removed

Excel compares what is displayed in a cell, not the value stored underneath.

Cell showsStored valueTreated as
3/8/2006The same dateUnique, because the display differs
Mar 8, 2006The same dateUnique, because the display differs

This is Microsoft’s own example, and it means you should format matching dates and numbers the same way before removing duplicates.

Highlight duplicates first with conditional formatting

Microsoft recommends previewing duplicates before you remove them.

  1. Select the cells you want to check.
  2. On the Home tab, open Conditional Formatting in the Styles group.
  3. Select Highlight Cells Rules, then Duplicate Values.
  4. Pick the values and the format you want, then confirm.

For more control, choose Manage Rules, select New Rule, pick Format only unique or duplicate values, and set the Format all list to unique or duplicate.

Hide duplicates without deleting them

An Advanced Filter hides repeat rows temporarily and leaves your data intact.

  1. Select the range, or click a cell in your table.
  2. Go to Data > Advanced in the Sort & Filter group.
  3. Choose Filter the list, in-place, or Copy to another location and enter a cell in the Copy to box.
  4. Tick Unique records only and select OK.

The Copy to another location option gives you a clean unique list beside the original.

Use the UNIQUE function for a live unique list

On versions that support it, a formula can list unique values without touching the source data.

ArgumentWhat it does
arrayThe range to pull unique rows or columns from (required)
by_colTRUE compares columns, FALSE or omitted compares rows
exactly_onceTRUE returns only values that appear once, FALSE or omitted returns every distinct value

The syntax is =UNIQUE(array,[by_col],[exactly_once]).

Microsoft lists it for Excel for Microsoft 365, Excel 2024 and Excel 2021, so older versions need the Remove Duplicates command instead.

Wrap it in SORT, as in =SORT(UNIQUE(B2:B12&” “&A2:A12)), to return the results in order.

Which method to use

MethodDeletes dataBest when
Remove DuplicatesYes, permanentlyYou want the repeat rows gone and have a backup copy
Conditional FormattingNoYou want to see the duplicates before deciding
Advanced Filter, Unique records onlyNo, it hides them or copies unique rows elsewhereYou need a clean list but must keep the original
UNIQUE functionNoYou use a version that lists it and want a list that updates

When in doubt, start with the highlight, then filter, and only delete once you have confirmed the rows are true repeats.

If Remove Duplicates will not work

Microsoft says you cannot remove duplicates from outlined data or data with subtotals.

Remove the outline and the subtotals first, then run the command again.

Microsoft also notes that you cannot conditionally format fields in the Values area of a PivotTable by unique or duplicate values.

Tell half-width and full-width characters apart

Microsoft notes that if your authoring language uses double-byte character sets such as Japanese, half-width and full-width characters count as the same.

To tell them apart, use a conditional formatting rule built on COUNTIF, such as =COUNTIF($A$2:$A$400,A2)>1 for the range A2:A400.

Most English-language workbooks never need this.

Why the removal message can look wrong

Microsoft says the counts of duplicate and unique values that Excel reports may include empty cells and spaces.

So a number that looks too high or too low is not always a sign that the wrong rows were deleted.

Check the list after the command runs, and use Ctrl+Z if a row you wanted is gone.

Which Excel versions this covers

VersionRemove DuplicatesUNIQUE function
Excel for Microsoft 365Listed by MicrosoftListed by Microsoft
Excel 2024Listed by MicrosoftListed by Microsoft
Excel 2021Listed by MicrosoftListed by Microsoft
Excel 2019 and 2016Listed by MicrosoftNot listed

If you removed rows by mistake and saved, see how to recover an unsaved Excel workbook or how to restore a previous version of a file from Version History.

To keep your column labels in view while you scan a long list, see how to freeze rows and columns in Excel.

If your formulas stop updating after you clean the list, read fixing Excel formulas that are not calculating.

Sources

Microsoft Support: Filter for unique values or remove duplicate values.

Microsoft Support: Find and remove duplicates.

Microsoft Support: UNIQUE function.

Leave a Reply

Your email address will not be published. Required fields are marked *