# 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].

## 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](/blog/data-analysis/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.

| Row | A (CustomerID) | B (Name) | C (Email) | D (City) |
|-----|----------------|----------|-----------|----------|
| 1 | CustomerID | Name | Email | City |
| 2 | 101 | Alice Johnson | alice@example.com | New York |
| 3 | 102 | Bob Smith | bob@example.com | Los Angeles |
| 4 | 103 | Carol White | carol@example.com | Chicago |
| 5 | 104 | David Brown | david@example.com | Houston |
| 6 | 105 | Eve Davis | eve@example.com | Phoenix |
| 7 | 106 | Frank Miller | frank@example.com | Philadelphia |
| 8 | 107 | Grace Wilson | grace@example.com | San Antonio |
| 9 | 108 | Henry Moore | henry@example.com | San Diego |
| 10 | 109 | Ivy Taylor | ivy@example.com | Dallas |
| 11 | 110 | Jack Anderson | jack@example.com | San Jose |
| 12 | 111 | Alice Johnson | alice@example.com | New York |
| 13 | 112 | Bob Smith | bob@example.com | Los Angeles |
| 14 | 113 | Carol White | carol@example.com | 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](/blog/data-analysis/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](/blog/data-analysis/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](/blog/data-analysis/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.** "alice@example.com" and "Alice@example.com " 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.

## 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](https://support.microsoft.com/en-us/excel/find-and-remove-duplicates)
2. [Filter for or remove duplicate values | Microsoft Support](https://support.microsoft.com/en-us/excel/filter-for-or-remove-duplicate-values)

## Further Reading

- [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)
- [Microsoft Support: Excel help and learning](https://support.microsoft.com/en-us/excel)
- [McCullough B, Heiser DA (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics & Data Analysis](https://doi.org/10.1016/j.csda.2008.03.004)

## Related Articles

- [How to Find Duplicates in Excel (Step by Step)](/blog/data-analysis/find-duplicates-in-excel)
- [How to Highlight Duplicates in Excel (Step by Step)](/blog/data-analysis/how-to-highlight-duplicates-in-excel)
- [How to Filter in Excel (Step by Step)](/blog/data-analysis/how-to-filter-in-excel)
- [How to Delete Blank Rows in Excel (Step by Step)](/blog/data-analysis/delete-blank-rows-in-excel)
- [How to Count Unique Values in Excel (Step by Step)](/blog/data-analysis/how-to-count-unique-values-in-excel)