How to find and remove duplicates in excel

Answer

To find duplicates in Excel, use Conditional Formatting. The tool highlights repeated entries so you can review them before removal.

Steps

1. Select the range you want to inspect.

2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

3. Choose a highlight color in the dialog box.

4. Click OK.

5. Review the highlighted duplicates in the worksheet.

Windows and Mac follow the same path for Conditional Formatting.

How to remove duplicates in Excel

To remove duplicates in Excel, use the built-in Remove Duplicates feature. The tool deletes repeated rows based on one or more selected columns.

Steps

1. Select the dataset you want to clean.

2. Go to Data > Remove Duplicates.

3. Check the columns Excel uses to identify duplicates.

   • Excel removes a row when all checked column values match an earlier row.

4. Click OK.

5. Review the notification box showing:

   • The number of duplicates removed.

   • The number of unique values left.

Windows and Mac share the same steps for this feature.

How to find duplicates using a formula (optional)

To flag duplicates with a formula instead of highlighting, insert a helper column and enter:

Formula (entered without formatting instructions in Excel)

$=COUNTIF(A:A,A2)>1$

A TRUE result means the value in A2 appears more than once. A FALSE result means the value is unique.

How to remove duplicates while keeping the first occurrence

To keep the first instance of each value, remove duplicates with the normal tool.

Steps

1. Select your data.

2. Go to Data > Remove Duplicates.

3. Select the columns Excel should compare.

4. Click OK.
   Excel always preserves the first appearance and removes the later matches.

How to remove duplicates across multiple columns

Excel removes duplicates across multiple columns when all selected column values match.

Steps

1. Select the full table.

2. Go to Data > Remove Duplicates.

3. Select multiple columns in the dialog.

4. Click OK.
   The row disappears when every selected column matches an earlier row.

How to find duplicates across sheets

To check duplicates between two sheets, use a formula.

Example formula

$=COUNTIF(Sheet2!A:A,A2)>0$

A TRUE result means the value appears on the other sheet.

How to remove duplicates but keep the most recent entry

To keep the most recent entry, sort the dataset first.

Steps

1. Select your full dataset.

2. Go to Data > Sort.

3. Sort the date column from Newest to Oldest.

4. Go to Data > Remove Duplicates.

5. Select the identifying column (such as an ID or name).

6. Click OK.

The most recent row appears first, and Excel removes the older entries.

How to prevent duplicates in Excel

To prevent duplicates in a column, use Data Validation.

Steps

1. Select the column for new entries.

2. Go to Data > Data Validation.

3. Choose Custom.

4. Enter this formula:

   $=COUNTIF(A:A,A1)=1$
   

5. Click OK.
   Excel blocks duplicate entries and displays a warning.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report