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.

If the list is long, freeze the header row in Excel so the column names stay visible while you scroll through the rows that are left.

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.

Repeated records are common after you combine several CSV files into one, especially when the exports cover overlapping dates.

Once the repeats are gone, you can create a pivot table in Excel to total the cleaned list by category.

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.

Here’s how to sort a table by date in Excel first, including the newest-to-oldest order.

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 doesn’t mean 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.

For more ways to use these rules, read about conditional formatting in Excel.

To color cells by hand instead of with a rule, fill a cell with color in Excel.

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 where the results need to go. Move the formula to an empty area or move the data that’s in the way; don’t 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, so run the filter again when the source changes. Microsoft explains filtering for 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. Convert the values to one consistent format and try again; a green triangle is an error indicator, not a sign of a duplicate.
• Subtotals or outlines: remove these before you use Remove Duplicates.

The same stray spaces and text-formatted numbers also cause lookup formulas to fail, for example when you use VLOOKUP in Excel.

Count Distinct Values Without Deleting the List

These formulas count each different value once and leave your list unchanged. Microsoft’s UNIQUE reference and distinct-count guide cover them.

1. Click an empty cell outside your data.
2. In Excel for Microsoft 365 or Excel 2021 and later, enter =ROWS(UNIQUE(A2:A100)). Replace A2:A100 with your range and leave out the header.
3. Press Enter. For the values 1, 1, 2 and 3, the result is 3.
4. In older desktop Excel, enter =SUM(1/COUNTIF(A2:A100,A2:A100)) and press Ctrl + Shift + Enter. Don’t type the curly braces. This formula fails if the range has blank cells or errors.

Count How Often Each Value Appears

A helper column marks repeats without deleting anything. More examples are on Microsoft’s COUNTIF help page.

1. In an empty column on row 2, enter =IF(A2="","",COUNTIF($A$2:$A$100,A2)). Change the range to match your list.
2. Fill the formula down to the last row.
3. Filter the helper column for values greater than 1. Those values repeat. The count includes the first occurrence, so the number of extra copies is one less.

Find Values Shared by Two Worksheets

This checks which values in a column on Sheet1 also appear on Sheet2. COUNTIF ignores letter case and treats * and ? as wildcards, so review the matches before you delete anything.

1. On Sheet1, enter =IF(A2="","",COUNTIF(Sheet2!$A$2:$A$100,A2)) in a spare column on row 2. If the sheet name has spaces, wrap it in single quotes, such as 'Second List'!$A$2:$A$100.
2. Fill the formula down.
3. Filter that column for values greater than 0. Those Sheet1 values also appear on Sheet2.
4. To check the other direction, add the same formula on Sheet2 with the sheet references reversed.

Remove Duplicate Rows in Google Sheets

Make a copy first, then select the whole data range in Google Sheets on a computer. Choose Data > Data cleanup > Remove duplicates, say whether the range has a header row, and choose which columns count as a match. Select Remove duplicates, check the result, and use Undo right away if the wrong rows were removed. If you select only one cell, Sheets may apply it to the whole surrounding range, so it’s safer to select the range yourself. Google’s help page for this tool describes the header and column choices.

Show Only the Duplicates With a Filter

To show only repeated values, first apply Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values to the column, using a fill color that stands out. Then turn on Data > Filter, open that column’s filter arrow, and choose Filter by Color with that fill. Only the highlighted rows should remain. This shows every occurrence of a repeated value; it doesn’t check whether whole rows are duplicates. Clear the filter to show all rows again. Filtering only hides rows, while Remove Duplicates deletes them.

Excel Duplicate Questions