exceltestpracticeStart free
Practice testsLearnFunctionsShortcutsPricingStart free

How-to guides

How to remove duplicates in Excel

Delete repeated rows with the Remove Duplicates command, choose which columns decide what counts as a repeat, and check the result before you trust it.

Duplicate rows creep in whenever data is pasted from two sources or an export is run twice. They inflate counts and totals, and a lookup that finds the first copy can hide the second.

Excel has a one-click command for the job, but it deletes rows for good. The skill is less the clicking than deciding which columns make two rows “the same” and confirming the result afterward.

Steps in Excel for Windows

Shortcut: Alt, A, M

  1. Click inside the data. Select any one cell in the table, or select the exact range if the sheet holds other things beside it. Excel will outline the whole block it plans to use.
  2. Open the command. On the Data tab, in the Data Tools group, click Remove Duplicates. The keyboard route is Alt, then A, then M.
  3. Tell Excel about the header. Keep "My data has headers" ticked when row 1 holds column names. If it is unticked, Excel treats the header as a data row and can delete it as a duplicate.
  4. Pick the columns that define a duplicate. Tick every column to remove only rows that match in all of them. Tick just one, such as Email, to treat rows with the same email as duplicates even when other columns differ.
  5. Click OK and read the message. Excel reports how many duplicate values it found and removed and how many unique values remain. Compare those numbers with what you expected. If they are wrong, press Ctrl+Z straight away.

Menu names are from Microsoft 365 on Windows. Excel for Mac and Excel for the web put the same commands in slightly different places.

Worked example

1. All columns compared

Before
ABC
1NameEmailDept
2Ana Ruizana@acme.comSales
3Kim Leekim@acme.comOps
4Ana Ruizana@acme.comSales
5Raj Patelraj@acme.comIT
6Kim Leekim@acme.comOps
After
ABC
1NameEmailDept
2Ana Ruizana@acme.comSales
3Kim Leekim@acme.comOps
4Raj Patelraj@acme.comIT

Row 4 repeats row 2 in every column, and row 6 repeats row 3, so both later copies are deleted. Excel reports 2 duplicate values found and removed and 3 unique values remaining. The first copy of each row stays where it was.

2. Only the Name column compared

Before
AB
1NameDept
2AnaSales
3AnaOps
4KimOps
After
AB
1NameDept
2AnaSales
3KimOps

With just Name ticked, Ana in row 3 counts as a repeat of Ana in row 2 even though the departments differ. Row 3 is deleted and its department is lost. Choose the key columns on purpose.

3. Flag duplicates with a formula before deleting

Flag duplicates with a formula before deleting. The formula =IF(COUNTIF($B$2:B5,B5)>1,"Duplicate","Keep") in cell C5 returns Duplicate.
ABC
1NameEmailStatus
2Anaana@acme.comKeep
3Kimkim@acme.comKeep
4Rajraj@acme.comKeep
5Anaana@acme.comDuplicate

The range grows as the formula is filled down, so each row counts how many times its email has appeared from row 2 down to itself. A count above 1 in C5 marks a later copy. Here B5 is the second ana@acme.com, so the formula returns Duplicate.

Common mistakes

  • Deleting without a backup. Remove Duplicates changes the sheet in place and cannot be redone from the dialog. Copy the data to another sheet first, or use Ctrl+Z immediately if the count looks wrong.
  • Leaving the header box unticked. Excel then compares the header row too. If a data row happens to match the header text, the header can go, and sorting or filtering later gets confusing.
  • Comparing too few columns. Two customers can share a first name or even a full name. Tick the columns that together identify one real record, such as ID, or email plus order date, rather than the first column by default.
  • Missing near-duplicates. Remove Duplicates ignores capital letters but not spaces, so "Kim Lee" and "Kim Lee " with a trailing space both survive. Clean the column with TRIM first, then remove duplicates.
  • Forgetting what depends on the data. Rows are deleted outright, and the cells below move up. Recheck any total, lookup or chart that read from the range afterward.

On Excel job tests

A prompt may read "remove the duplicate customer records" or "list each customer once". Those can look alike but ask for different things. Remove Duplicates changes the original data, while the UNIQUE function leaves the data alone and builds a separate list. Read the instruction for words such as "keep the original" or "in a new location" before you choose.

After the command, check the count in the message against a quick COUNTA of the column. If the task names the key, for example "customers with the same email are duplicates", tick only that column.

  • UNIQUEReturns the distinct values in a range, removing duplicates.
  • COUNTIFCounts the cells in a range that meet one condition.
  • TRIMRemoves extra spaces from text, leaving single spaces between words.
  • IFReturns one value if a condition is true and another if it is false.
  • filter data by a condition in ExcelShow only the rows that meet a condition, such as region equals East or amount over 500, with the Filter button or the FILTER function.
  • sort by multiple columns in ExcelOrder a table by one column and then break ties with a second, or a third, using the Sort dialog and Add Level.

Questions

Does Remove Duplicates keep the first or the last copy?
It keeps the first one it meets, reading from the top of the selection, and deletes the later ones.
Is Remove Duplicates case sensitive?
No. "KIM" and "kim" count as the same value. Spaces do matter, so trailing spaces keep two rows apart.
How can I find duplicates without deleting them?
Flag them with a COUNTIF formula, or use Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values. Both leave every row in place.
Can I remove duplicates with a formula instead?
In Microsoft 365 and Excel 2021, =UNIQUE(range) returns the list without repeats in a new spot and leaves the source untouched.

Practice this

No free lesson covers Remove Duplicates yet. The closest practice is the free 5-item sampler, which uses the same kind of tasks in a working grid.

Try a 5-item Excel test sample