How to Remove Duplicates in Excel

To remove duplicates in Excel, select your data, go to the Data tab, and click Remove Duplicates. Choose the columns to check, then click OK. Excel deletes the repeated rows and tells you how many it removed. This changes the source data, so make a copy first. Use Undo immediately if the result is wrong; do not rely on being able to undo after closing the workbook.

If you want to see the duplicates before deleting anything, highlight them with conditional formatting. If you want to keep your original list untouched, use the UNIQUE function to build a clean copy.

How to Remove Duplicate Rows in Excel

These steps follow Microsoft’s guide to finding and removing duplicates and work in Excel for Microsoft 365, Excel 2021, and Excel 2019.

1. Select the full range of related columns and rows, including headers if you have them. For an Excel Table, you can select a cell inside it.
2. Click the Data tab.
3. Click Remove Duplicates in the Data Tools group.
4. Leave My data has headers checked if the first row has column names.
5. Check the columns Excel should compare. Keep every column checked to remove only rows that match completely.
6. Click OK.

Excel shows a message with the number of duplicates it removed and how many unique values remain.

Excel keeps the first time a row appears and deletes the later copies. If you change your mind, press Ctrl + Z right away to undo.

Which Columns Should You Check?

• All columns: removes a row only when every value matches another row. Use this when only fully matching records count as duplicates.
• One column, such as Email: keeps one row per email address, even if other details differ. Use this carefully, because Excel keeps the first row it finds and deletes the rest.

The checkboxes define the match; they do not limit which columns lose data. A matching record is removed across the whole selected range. Selecting only one column of a larger dataset can leave the remaining columns out of alignment.

For example, imagine a table with Customer, City, and Amount. Two rows contain Ava, Boston, 25 and Ava, Boston, 40. Checking all three columns keeps both rows. Checking only Customer and City treats them as duplicates and keeps the first row, losing the other Amount. Decide which fields identify a genuinely repeated record before clicking OK.

If you need to keep the newest record, sort the entire table by its date column from newest to oldest before removing duplicates. Check only the fields that define the duplicate, not the date if different dates should count as the same record. Review a sample afterward: Excel keeps the first record in the current order, not automatically the newest or most complete one.

Highlight Duplicates Before You Delete Them

1. Select the cells to check.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Pick a color and click OK.

This rule highlights repeated cell values, including the first occurrence; it does not prove that entire rows match. For example, two different customers in the same city can both be highlighted. To remove only this formatting rule, use Conditional Formatting > Manage Rules and delete the duplicate-value rule.

Keep the Original List With the UNIQUE Function

In Excel for Microsoft 365, Excel 2021, or later, UNIQUE creates a separate result without deleting source records.

1. Select an empty cell outside your source data, with room below and to the right for the results.
2. Enter =UNIQUE(A2:C9), replacing A2:C9 with your data range and leaving out the header row.
3. Press Enter. Excel returns one copy of each distinct row.

For a single-column list, use =UNIQUE(A2:A9). A fixed range does not automatically include records added below it; extend the range when needed. The optional formula =UNIQUE(A2:A9,,TRUE) has a different purpose: it returns only values that occur exactly once, excluding every value that appears multiple times.

If UNIQUE shows #SPILL!: select the formula cell to see the intended output area. Move the formula to a clear area or move obstructing data; do not delete useful cells just to clear the error. Keep the formula outside an Excel Table. Microsoft’s #SPILL! troubleshooting guide explains other causes.

Create a unique copy in older Excel versions

If your Excel version does not support UNIQUE, use Advanced Filter in desktop Excel. This also works when you want a fixed copy rather than a formula that recalculates.

1. Select the source list, including a header row.
2. Choose Data › Advanced in the Sort & Filter group.
3. Select Copy to another location.
4. Check the List range and set Copy to to an empty destination on the worksheet.
5. Select Unique records only, then OK.

The result is a snapshot: run the filter again when the source changes. Microsoft describes extracting unique values with Advanced Filter.

Why Excel Missed Some Duplicates

• Extra spaces: “Ava Brooks” and “Ava Brooks ” look the same but aren’t. Clean them with =TRIM(A2).
• Numbers stored as text: mixed data types or inconsistent formatting can make a list harder to compare. Inspect the values and standardize the intended format before retrying; a green triangle is an error indicator, not proof of a duplicate.
• Subtotals or outlines: Microsoft recommends removing these before you use Remove Duplicates.

Frequently Asked Questions

Does Remove Duplicates delete the whole row?

It deletes the duplicate rows inside the range you selected. Data outside that range isn’t touched, which is why it helps to select the full table.

Can I remove duplicates in Google Sheets?

Yes. In Google Sheets, select your data and choose Data > Data cleanup > Remove duplicates.

What else can I do to tidy up a list?

You can combine first and last names into one cell or add a drop-down list so people pick values instead of typing them, which reduces inconsistent entries. A drop-down list does not prevent the same value from being selected more than once. If old rules get in the way of cleanup, see how to remove data validation in Excel. If the Clipboard pane fills up while you copy data around, see how to clear the clipboard in Excel.