Find or Remove Duplicates in Excel – 3 Simple Ways

Yodalearning>Tutorials>Find or Remove Duplicates in Excel – 3 Simple Ways

How to Remove Duplicate in Excel with these 3 Simple Ways?

In Microsoft Office Excel 2007 and in any subsequent versions, you have several ways to filter for unique values or find duplicates in Excel. Now, you will learn how to find duplicates in excel and remove it.

  1. Use the Remove Duplicates command in the Data Tools group on the Data
  2. To filter for unique values, use the Advanced Filter command in the Sort & Filter group on the Data
  3. To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home

Option 1: Remove Duplicates in Excel:

  1. Click any single cell inside the data set.
Select the data, Remove duplicate in excel
Image 1
  1. On the Data tab, click Remove Duplicates.

how to remove duplicate

(Image 2)

  1. The following dialog box appears. Click On all checkboxes and click OK.

column-selection-in-remove-duplicate

(Image 3)

Result: Excel removes all identical rows (red) i.e it removes all duplicate data from duplicate rows & column.

Image-4-Effect-of-remove-duplicate

(Image 4)

To remove rows with the same values in certain columns in excel, execute the following steps:

  1. For example, remove rows with the same Airport Name. Select any single cell inside the data set.

Image-5-Remove-Duplicate

(Image 5)

  1. Check Airport Name and click OK.

Image-6-Remove-Duplicate

(Image 6)

tweet-graphic-3

Tweet this article with your friends

Option 2: Advanced Filter:

  1. Select the range of cells.
  2. On the Data tab, in the Sort & Filter group, click Advanced.

Image-7-Advance-Filter

(Image 7)

  1. In the Advanced Filter dialog box, do one of the following:
  2. Click Copy to another location.
  3. In the Copy to box, enter a cell reference.
  4. Select the Unique records only check box, and click OK.

Image-8-Advance-Filter

(Image 8)

The unique values from the selected range are copied to the new location.

Read More: How to use countif function in excel?

Option 3: Conditional Formatting:

This function is used to find the duplicate values in excel and also further may remove it by using filter option.

  1. Select the range of cells you wish to test.
  2. On the Home tab, click Conditional Formatting, Highlight Cells Rules, and Duplicate Values.

Image-9-Conditional-formatting

(Image 9)

  1. Select a formatting style and click OK.
  2. Excel highlights the duplicate names.

Image-10-Duplicate-Value

(Image10)

As you can see, Excel highlights duplicates, triplicates, etc. Execute the following steps to highlight triplicates only.

  1. First, clear the previous conditional formatting rule.

Image-11-Clear-Confitional-Formatting

(Image 11)

  1. Select the range of cells you wish to test.
  2. Select “Use a formula to determine which cells to format”.
  3. Enter the formula “=COUNTIF(A:A,A5)=3”.
  4. Select a formatting style and click OK.
  5. Excel highlights the triplicate names.

Image-12-Countif-find-duplicate-value

(Image 12)

Learn more: Advanced Excel Courses and Free Online Excel Courses


Watch the video tutorial on remove duplicate in 5 seconds (excel data cleaning trick)

Advanced Excel Course

Your courses were useful for me. Thank you again for your quick support and also for your availability.

- Madalina Elena Gheorghe

Free Excel Course
with 210+ Tricks
(UPDATED 2017)