How to Remove Duplicates in Excel (Step by Step)

By Dr. Zubair Khalid, DVM, MS, PhD ·

How to Remove Duplicates in Excel (Step by Step)

Removing duplicate rows in Excel takes about ten seconds once your data is selected. You select the range, go to Data > Remove Duplicates, choose which columns define a duplicate, and click OK. Excel deletes the repeated rows permanently, so the main thing to get right is choosing the right columns and keeping a backup.

Quick Answer

  • Select the range of cells that contains the duplicate values you want to remove [1].
  • Go to Data > Remove Duplicates on the ribbon [1].
  • In the dialog, check or uncheck the columns that define a duplicate [1].
  • Click OK. Excel reports how many duplicate values were found and removed and how many unique values remain [1].
  • The deletion is permanent, so copy the original data to another worksheet first if you might need it [1].

How to Remove Duplicates in Excel

To remove duplicates in Excel, select your data range, go to Data > Remove Duplicates, tick the columns that decide what counts as a duplicate, and click OK [1]. Excel then permanently deletes the repeated rows and reports how many duplicates it removed and how many unique values remain [1]. Because the deletion is permanent, copy the sheet first if you might need the original [1].

The column choice is the whole game. Excel treats two rows as duplicates only when every selected column matches. If you select all columns, a row with a different ID but the same person is not a duplicate. If you select just one column, such as City, Excel keeps the first row for each distinct city and deletes every later row, even when the names, emails, and IDs in those rows are completely different. That is exactly what happens when you select only one column such as City while using Remove Duplicates: you lose whole records, not just repeated cities. For a customer list, select the identifying column (Email or CustomerID) rather than a descriptive one.

The "My data has headers" option does one job: it tells Excel that row 1 holds column names, so the dialog shows those names instead of Column A, Column B, and so on, and row 1 is excluded from the duplicate check. It does not delete the first row, sort the headers, or highlight them. Leave it unticked and Excel may treat your header text as data and delete it as a duplicate.

If the tool refuses to run, or the button is grayed out, check for an outline or subtotals. You cannot remove duplicate values from data that is outlined or that has subtotals, and you must remove both the outline and the subtotals first [2]. To clear an outline, select the grouped rows or columns, open Data > Outline > Ungroup, and choose Rows (or Columns); repeat until the grouping is gone, then delete any Subtotal rows with Data > Subtotal > Remove All.

SituationWhat to selectWhat Excel does
Full duplicate recordsAll columnsDeletes rows that match everywhere
Same person, different IDEmail or CustomerID onlyKeeps first record per person
One column only, such as CityThat columnKeeps first row per city, deletes the rest
Outlined or subtotaled dataNothing works yetClear outline and subtotals first [2]

Before You Start

Two preparation steps save a lot of pain.

First, make a copy. When you use the Remove Duplicates feature, the duplicate data is permanently deleted, so Microsoft recommends moving or copying the original data to another worksheet before you start [1]. A quick way is to right-click the sheet tab, choose Move or Copy, tick Create a copy, and work on the copy.

Second, clean up structure. You cannot remove duplicate values from data that is outlined or that has subtotals. To remove duplicates, you must remove both the outline and the subtotals first [2]. If your sheet has grouped rows or a Subtotal row, clear those before you touch the tool.

It also helps to know what counts as a duplicate. Excel compares the values in the columns you select. Two rows are duplicates only if every selected column matches. If you select all columns, rows must match across the board. If you select one column, Excel keeps the first row for each distinct value in that column and deletes the rest, even when the other columns differ. That is why the column choice in the dialog is the most important decision in the whole process.

If you only want to see duplicates before deleting anything, use conditional formatting to highlight them first. Select Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, pick a format, and click OK [1]. That way you can review the duplicates and decide if you want to remove them [1]. There is a full walkthrough in How to Highlight Duplicates in Excel.

Step by Step

  1. Select the entire data range, including the header row. In the example below that is A1:D14.
  2. Go to the Data tab and click Remove Duplicates in the Data Tools group [2].
  3. In the Remove Duplicates dialog, check My data has headers if your first row holds column names. Excel then shows the column names instead of Column A, Column B, and so on.
  4. Select the columns that define a duplicate. Click Select All to use every column, or clear it and tick only the columns you want [2]. If the range contains many columns and you want to use only a few, clear Select All and select only those columns [2].
  5. Click OK. Excel removes the duplicate rows and shows a message with the number of duplicate values removed and the number of unique values remaining [1].
  6. Click OK on the confirmation dialog to close it.
  7. Check the result. Select the remaining data and look at the status bar, or use a count formula, to confirm the row count matches what Excel reported.

One detail about the report: the counts of duplicate and unique values given after removal might include empty cells, spaces, and similar near-empty values [1]. If your count looks higher than expected, check for stray spaces before assuming the tool misbehaved.

Worked Example

The sheet below holds 13 customer records in A1:D14, with three rows that repeat an earlier customer.

RowA (CustomerID)B (Name)C (Email)D (City)
1CustomerIDNameEmailCity
2101Alice Johnson[email protected]New York
3102Bob Smith[email protected]Los Angeles
4103Carol White[email protected]Chicago
5104David Brown[email protected]Houston
6105Eve Davis[email protected]Phoenix
7106Frank Miller[email protected]Philadelphia
8107Grace Wilson[email protected]San Antonio
9108Henry Moore[email protected]San Diego
10109Ivy Taylor[email protected]Dallas
11110Jack Anderson[email protected]San Jose
12111Alice Johnson[email protected]New York
13112Bob Smith[email protected]Los Angeles
14113Carol White[email protected]Chicago

Rows 12, 13, and 14 repeat the names, emails, and cities from rows 2, 3, and 4. The CustomerID values are different, so if you select all four columns, Excel sees no duplicates at all. The fix is to define a duplicate by the column that identifies a person, which here is Email.

Follow the interface steps:

  1. Select the entire data range A1:D14.
  2. Go to Data > Remove Duplicates.
  3. In the Remove Duplicates dialog, check My data has headers and select only the Email column.
  4. Click OK to remove duplicates.
  5. Click OK on the confirmation dialog.
  6. Check the row count. Excel reports 3 duplicate values removed and 10 unique values remaining, which is 13 original rows minus 3 duplicates.

After the removal, 10 unique records remain. Excel keeps the first occurrence of each email and deletes rows 12, 13, and 14. The result is 10 unique records from 13 original rows, with 3 duplicate rows removed.

If you want to confirm the count with a formula instead of the status bar, count the distinct emails in the cleaned range. With the remaining emails in C2:C11, the array formula

=SUM(1/COUNTIF(C2:C11,C2:C11))

returns 10. The COUNTIF part counts how many times each email appears, and dividing 1 by that count and summing gives the number of distinct values. The math behind it is

$$\sum_{i=1}^{n} \frac{1}{\text{count of value } i}$$

which equals the number of unique values when every value appears at least once. For more on this pattern, see How to Count Unique Values in Excel.

Other Ways to Do It

Advanced Filter with Unique records only. Select the range, then use the Advanced Filter option, choose Copy to another location, enter a cell reference in the Copy to box, tick Unique records only, and click OK [2]. This copies the unique values to the new location and leaves the original data untouched [2]. It is the safest option when you are not ready to delete anything.

Conditional formatting plus manual review. Highlight duplicates with Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, then delete the highlighted rows by hand [1]. Slower, but you decide row by row.

Filter first. If you want to inspect the duplicates before deleting them, filter the column and read the repeated values. The mechanics are covered in How to Filter in Excel.

Google Sheets. The same job exists there under Data > Data cleanup > Remove duplicates. The dialog asks you to select the data range and whether the data has a header row, then reports how many duplicates were removed. The logic is identical to Excel: only the selected columns decide what counts as a duplicate.

Troubleshooting

Excel says no duplicates were found, but you can see them. You probably selected too many columns. Two rows match only when every selected column matches. Clear Select All and tick just the identifying column, such as Email or CustomerID.

The wrong rows disappeared. Excel keeps the first occurrence and deletes later ones. If your data is sorted so that the "wrong" copy comes first, sort the data the way you want before removing duplicates, or use Advanced Filter to copy unique values to a new location and compare.

The button is grayed out or the tool refuses to run. Check for outlines or subtotals. You cannot remove duplicate values from data that is outlined or that has subtotals, and you must remove both the outline and the subtotals first [2].

The count looks too high. The counts reported after removal might include empty cells, spaces, and similar values [1]. Trim spaces with TRIM or check for blank rows before trusting the number. Blank rows are covered in How to Delete Blank Rows in Excel.

You deleted the wrong thing. Press Ctrl+Z straight away, because Undo reverses Remove Duplicates while the workbook is still open. Once the file is saved and closed the rows are gone, which is why the backup copy matters [1]. If you skipped the backup and cannot undo, close the file without saving and reopen it.

Common Mistakes

  • Selecting every column when only one identifies a record. Rows with different IDs but the same person are not duplicates across all columns. Fix: clear Select All and tick only the identifying column [2].
  • Forgetting the header row. If you do not check My data has headers, Excel treats the header as data and may delete it as a duplicate or mislabel the columns. Fix: tick the box whenever row 1 holds column names.
  • Working on the only copy. The duplicate data is permanently deleted [1]. Fix: copy the sheet or workbook first [1].
  • Leaving subtotals or outlines in place. The tool will not run on outlined or subtotaled data [2]. Fix: clear the outline and subtotals first [2].
  • Trusting the reported count blindly. The counts might include empty cells and spaces [1]. Fix: verify with a distinct count formula or by checking the status bar.
  • Assuming spacing does not matter. "[email protected]" and "[email protected] " are treated as different values because of the trailing space, even though Remove Duplicates ignores case. Fix: trim spaces before removing duplicates.

Limitations

Remove Duplicates compares values, not meaning. It cannot tell that "Bob Smith" and "Robert Smith" are the same person, or that two addresses refer to one building. It also cannot merge partial records. If row 2 has a phone number and row 12 has the same email but no phone number, removing duplicates keeps whichever row comes first and discards the other information entirely.

The tool also works only on the selected range. When you remove duplicate values, only the values in the selected range of cells or table are affected, and any other values outside that range are not altered or moved [2]. If your sheet has related data in other columns that you did not include, the rows will no longer line up after deletion. Select the full width of your table, or copy the unique values elsewhere with Advanced Filter and rebuild from there.

What the Column Selection Really Does

The dialog is a filter, not a magic wand. Each ticked column becomes part of a composite key, and Excel compares the full key row by row. Think of it as gluing the selected columns into one string and asking whether that string has appeared before.

One column versus several

Suppose your sheet has Name, Email, and City, and two rows read "Alice Johnson, [email protected], New York" and "Alice Johnson, [email protected], Boston." Tick all three columns and Excel keeps both rows, because City differs. Tick only Email and the second row disappears, because the email matches. Tick only City and you keep one New York row and one Boston row, but you also collapse every other New York customer into a single survivor. The rows that vanish are not "duplicate cities" in any meaningful sense; they are distinct customers who happened to share a city.

Why the first row wins

Excel always keeps the first occurrence in the current row order and deletes later ones. It does not ask which record is more complete or more recent. If your data is sorted oldest first, you keep the oldest record. Sort by date descending before running the tool if you want the newest record to survive, or copy unique values elsewhere with Advanced Filter and rebuild the list by hand [2].

A quick mental test

Before clicking OK, read your ticked columns aloud as a sentence: "A row is a duplicate if the ___ matches." If that sentence describes a person, an order, or an event, you have chosen well. If it describes an attribute like City, State, or Status, stop and rethink.

Headers, Outlines, and Other Dialog Traps

What "My data has headers" changes

This checkbox has a narrow effect. When ticked, Excel skips row 1 during the comparison and labels the column list with your header text. When unticked, row 1 is treated as ordinary data, so a header row that happens to repeat a value elsewhere can be deleted, and the column list reads Column A, Column B, and so on. It never deletes, sorts, or colors the header row on its own. If your headers look wrong in the dialog, cancel, tick the box, and reopen.

The outline error explained

The message about outlines and subtotals is a structural guard, not a bug. Outlined data hides rows inside groups, and subtotal rows are calculated summaries sitting inside the range. Deleting rows under those conditions would corrupt the grouping and the totals, so Excel blocks the operation entirely [2]. Clear the structure first: select the data, use Data > Outline > Ungroup on Rows repeatedly until no grouping brackets remain, then remove subtotals with Data > Subtotal > Remove All. Only then does Remove Duplicates run.

Filtered and hidden rows

Remove Duplicates acts on the selected range, hidden or not. A filter that hides rows does not shrink the range, so hidden duplicates are still evaluated and can still be deleted. If you want to clean only the visible rows, copy the filtered result to a new location and run the tool there.

Worked Comparison: Three Ways to Clean the Same List

Take a 13-row customer list where rows 12 to 14 repeat the names, emails, and cities of rows 2 to 4 but carry new CustomerIDs.

Remove Duplicates on all four columns. Nothing is removed, because the IDs differ. The report says zero duplicates found. This is the most common false alarm.

Remove Duplicates on Email only. Three rows are deleted and 10 unique records remain. The surviving rows keep their original IDs, so the list is clean but the ID column now has gaps.

Advanced Filter with Unique records only. Select the range, choose Copy to another location, set a destination, and tick Unique records only [2]. The 10 unique rows are copied to the new spot and the original 13 rows stay intact [2]. This is the better choice when you need to compare before committing, or when the ID gaps from Remove Duplicates would cause problems downstream.

A fourth option, conditional formatting to highlight duplicates before deleting anything, lets you eyeball each repeated row first [1]. It is slower but reversible.

Misconceptions Worth Clearing Up

"It merges duplicate records." It does not. It keeps one row and discards the rest, including any information unique to the deleted rows. If row 2 has a phone number and row 12 has the same email but no phone number, the phone number is lost if row 12 comes first.

"It ignores case, so spacing does not matter." Case is ignored, but spaces are not. A trailing space makes two otherwise identical emails count as different values, which inflates the unique count and leaves a duplicate behind. Trim spaces before running the tool.

"The reported count is exact." The counts after removal might include empty cells, spaces, and similar near-empty values [1]. Verify with a distinct count formula or the status bar rather than trusting the dialog alone.

"It only touches the columns I selected." It deletes entire rows within the selected range. Any columns outside the range are untouched and no longer line up with the surviving rows [2]. Always select the full width of your table.

"Undo always works." Ctrl+Z reverses the deletion only while the workbook is open and unsaved. Once saved and closed, the rows are gone, which is why the backup copy matters [1].

Frequently Asked Questions

How do I remove duplicates in Excel without deleting the original data?

Use Advanced Filter. Select the range, choose Copy to another location, enter a destination cell in the Copy to box, tick Unique records only, and click OK [2]. The unique values are copied to the new location and the original data is not affected [2]. You can also copy the whole sheet before running Remove Duplicates [1].

How do I remove duplicate entries in Excel based on one column?

In the Remove Duplicates dialog, clear the Select All check box and tick only the column you want to use [2]. Excel then treats two rows as duplicates when that single column matches, keeps the first row, and deletes the rest. This is the standard approach for email lists and customer IDs.

Why does Excel say no duplicates were found when I can see them?

The selected columns do not all match. Excel only flags a row as a duplicate when every selected column has the same value. Uncheck the columns that differ, such as a unique ID or a timestamp, and run the tool again. Trailing spaces can also make identical-looking text count as different.

Can I undo Remove Duplicates?

Only straight away. Pressing Ctrl+Z immediately after the tool runs restores the deleted rows, but once you save and close the workbook the duplicate data is permanently gone [1]. The reliable recovery method is a backup copy of the sheet or workbook made before you run the tool [1].

How do I remove duplicates in Google Sheets?

Open the Data menu, choose Data cleanup, then Remove duplicates. Select the data range, indicate whether the data has a header row, and confirm. Google Sheets reports how many duplicates were removed and how many unique rows remain, using the same column-based logic as Excel.

Does Remove Duplicates work on a filtered range?

It works on the range you select, but hidden rows are still part of that range. If you filter first and then remove duplicates, Excel evaluates the hidden rows too. Clear the filter, or copy the filtered result to a new location and remove duplicates there.

References

  1. Find and remove duplicates | Microsoft Support
  2. Filter for or remove duplicate values | Microsoft Support

Further Reading

Related Articles

Related articles