How to remove duplicates in Excel

Quick answer: To remove duplicates in Excel, copy your data to another sheet as a backup, select the range, then go to Data > Data Tools > Remove Duplicates. Choose the columns that must match for a row to count as a duplicate and select OK. Excel keeps the first occurrence, deletes the later ones and tells you how many it removed. To keep the original list untouched, use Data > Advanced with Unique records only, or the UNIQUE function in Excel for Microsoft 365, Excel 2024 or Excel 2021.

Applies to: Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019 and Excel 2016 on Windows, which Microsoft’s article on unique and duplicate values covers along with Excel for the web. The UNIQUE function in Method 4 needs Excel for Microsoft 365, Excel 2024 or Excel 2021. Mac and web get a short section near the end.

Before you start: make a copy

Filtering for unique values and removing duplicates give you the same list, but they aren’t the same operation. A filter only hides the repeated rows for now. Remove Duplicates deletes them, and Microsoft describes that deletion as permanent. Microsoft’s advice is to copy the original data to another worksheet or workbook before you start.

Microsoft also says you can select Undo or press Ctrl+Z right after the result message to undo the change. Treat that as a safety net for the current session only: once the file is saved and closed, the undo history is gone, so the copy is what protects you.

It’s also worth knowing what Excel compares before you delete anything. According to Microsoft, the check uses what the cell shows, not the value stored behind it. Two cells holding the same date, one formatted as 3/8/2006 and the other as Mar 8, 2006, are treated as different values. That single rule explains some of the cases in the last section.

Method 1: Find duplicates first with conditional formatting

Microsoft recommends checking your results before you remove anything, and highlighting is the easiest way to do that. Nothing is deleted, so you can see exactly which cells Excel considers repeats.

  1. Select the cells you want to check, for example one column of email addresses.
  2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Conditional Formatting sits in the Styles group.
  3. Leave Duplicate selected in the first box, pick a highlight color and select OK.

Scroll through the highlighted cells. If a value you expected to be marked isn’t, jump to Duplicates that won’t go away before you go further. To change or remove the rule later, use Conditional Formatting > Manage Rules, where Microsoft lists it as a rule of type Format only unique or duplicate values.

Two limits from Microsoft’s article: you can’t apply this rule to the Values area of a PivotTable, and in double-byte languages such as Japanese the rule treats halfwidth and fullwidth characters as the same. Microsoft suggests a COUNTIF formula rule when you need to tell those apart.

Method 2: Delete duplicate rows with Remove Duplicates

This is the built-in tool for deleting repeats. It works on the range or table you select and leaves everything outside it alone, as Microsoft points out.

  1. Select the range, including the header row if there is one. If the data is an Excel table, click any cell in it.
  2. Go to Data > Data Tools > Remove Duplicates.
  3. If your first row holds column names and the dialog shows a My data has headers box, make sure it’s selected so the header row isn’t compared as data. Microsoft’s article doesn’t mention this box, so check the dialog on your screen.
  4. Under Columns, choose which columns must match. Use Select All to treat only fully identical rows as duplicates, or Unselect All and then check the columns that matter, for example just Email.
  5. Select OK. Microsoft says a message then shows how many duplicate values were removed, or how many unique values remain.

Read this before step 4: Microsoft warns that when a match is found in the columns you picked, Excel removes the whole row, including the columns you didn’t pick. If you check only Email, two rows with the same address but different phone numbers count as duplicates, and the second row’s phone number goes with it. Excel keeps the first occurrence and deletes the later ones, so sort the data first if it matters which row survives, for example newest date at the top.

For data in an Excel table, the same command should also be available on the Table Design tab, in the Tools group. Either route gives the same dialog.

Method 3: Copy unique records with Advanced Filter

If you’d rather keep the original list as it is, Advanced Filter can write a list of unique rows somewhere else. It’s a good choice when the raw data is a record you might need again, such as an export from another system.

  1. Select the range, or click a cell inside the table.
  2. Go to Data > Advanced. The button is in the Sort & Filter group.
  3. Choose Copy to another location.
  4. In Copy to, enter or click the cell where the list should start, on an empty part of the sheet.
  5. Check Unique records only, then select OK.

The dialog also offers Filter the list, in-place. That hides the repeated rows instead of copying, which is handy for a quick look; clear the filter to see everything again. If the filter buttons behave oddly once you clear it, see Excel filter not working. The copied list is a one-time snapshot: it doesn’t update when the source data changes, which is where Method 4 helps.

Method 4: List unique values with the UNIQUE function

Microsoft lists the UNIQUE function for Excel for Microsoft 365, Excel 2024 and Excel 2021 (Windows and Mac) and for Excel for the web. It isn’t in that list for Excel 2019 or 2016, so use Method 3 there. UNIQUE returns a list of unique values from a range and leaves the source untouched. Its syntax is:

=UNIQUE(array,[by_col],[exactly_once])
  • array is the range to check, such as A2:A200, or A2:C200 to compare whole rows across three columns.
  • by_col is optional. Leave it out (or use FALSE) to compare rows; TRUE compares columns instead.
  • exactly_once is optional. Leave it out (or use FALSE) for one copy of every distinct value. Set it to TRUE to list only the values that appear once, which Microsoft calls the database concept of unique.

Type the formula in an empty cell and press Enter. The result spills into the cells below, and Excel sizes that range for you. To sort the list at the same time, wrap it as Microsoft shows:

=SORT(UNIQUE(A2:A200))

If the source is an Excel table and you refer to it with structured references, Microsoft says the result resizes as rows are added or removed. Two cautions: the spilled list needs empty cells to land in, and if something is in the way you’ll see an error, covered in #SPILL! error in Excel. And if the formula points at another workbook, Microsoft notes it only works while both are open; with the source closed, it returns #REF! on refresh.

UNIQUE gives you a clean list next to the original rather than deleting anything. If you need the cleaned list as plain values, copy the result and use Paste Special > Values somewhere else.

Excel for Mac and Excel for the web

Excel for the web: Microsoft’s unique values article has a separate tab for the web version, but its steps weren’t part of the text reviewed for this guide. In the browser, look for Remove Duplicates on the Data tab and for conditional formatting on Home; the layout and the options offered can differ from the desktop app. UNIQUE is listed for the web.

Excel for Mac: that article has no Mac tab. Excel for Mac should have Remove Duplicates on the Data tab too, but check the labels on your screen. The UNIQUE function page does list Excel for Microsoft 365, Excel 2024 and Excel 2021 for Mac.

Duplicates that won’t go away

If Remove Duplicates reports nothing found, or removes fewer rows than you expected, the cells may only look identical. Excel compares what each cell shows, so any difference counts. Check these points on a copy of the data:

  • Extra spaces. A trailing space after a name is invisible but makes the text different. As general guidance, a helper column with =TRIM(A2) strips leading, trailing and repeated spaces; paste the results back as values, then run Remove Duplicates again. Text copied from web pages can contain non-breaking spaces, which TRIM may not remove.
  • Different capitalization. Microsoft’s article doesn’t say whether Remove Duplicates treats Smith and SMITH as the same value. Don’t assume either way: highlight duplicates first (Method 1) and see whether both are marked. If you need one consistent form, a helper column with =PROPER(A2), =UPPER(A2) or =LOWER(A2) makes the case match.
  • Numbers stored as text. A number imported as text can look the same as a real number but may not match it. A small green triangle in the corner of a cell can be a sign of this. Convert the column to numbers, for example with Data > Text to Columns and Finish, then try again.
  • Formatting differences. Microsoft’s own example: the same date shown as 3/8/2006 in one cell and Mar 8, 2006 in another counts as two unique values. Apply one number format to the whole column first.
  • Hidden rows and filters. If a filter is on or rows are hidden, you can’t see every row that’s part of the selection. Clear filters with Data > Clear and unhide rows before you remove anything, so you can check what changed.
  • Outlines and subtotals. Microsoft says duplicates can’t be removed from outlined or subtotaled data. Remove the subtotals and the outline first, for example from Data > Subtotal and Remove All.
  • Wrong columns or selection. Only the selected range or table is checked, and only the columns checked in the dialog are compared. A partial selection or one extra column, such as an ID or a timestamp, can make every row unique.

Merged cells can also get in the way of selecting and sorting a clean range; Merge and Center not working in Excel explains how to unmerge them. And if duplicate keys are the reason a lookup returns the wrong row, Excel VLOOKUP not working covers that side of it.

What you seePossible causeWhat to try
Two names look identical but stayExtra or non-breaking spacesClean with TRIM in a helper column
Same date in two styles, both keptExcel compares the displayed valueApply one date format to the column
Numbers that match are both keptOne is stored as textConvert the column to numbers
Excel won’t remove anything from a reportOutline or subtotals in the dataRemove subtotals and the outline first
Every row reported as uniqueAn ID or date column is checked in the dialogClear columns that differ on every row
Fewer rows removed than highlightedDifferent selection or columns in each stepUse the same range and columns for both

Sources

How we write our guides: read our editorial policy.

Comments

Leave a Reply

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