fbpx
Email: [email protected] Call Now! +1 203-349-9909 / +91-8080042523

How to Find and Remove Duplicates in Excel

In Microsoft Office Excel 2007, Excel 2010, Excel 2013, Excel 2016, you have several ways to filter for unique values or find and remove duplicates in Excel.

Now, you will learn how to find & remove duplicate values in excel.

  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

➡ How to Find and Remove Duplicates

Excel for Office 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007

  1. First, click any single cell inside the data set.

Select the data, Remove duplicate in excel

  1. On the Data tab, click Remove Duplicates.

how to remove duplicate

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

column-selection-in-remove-duplicate

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

Image-4-Effect-of-remove-duplicate

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

  1. Check Airport Name and click OK.

Image-6-Remove-Duplicate

➡ Steps on how to do Advanced Filter in Excel:

Excel for Office 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007

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

Advanced-Filter in Excel for Removing Duplicates in Excel

  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

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

This was the complete step by step tutorial for doing Advanced Filter in Excel

➡ Steps to Conditional Formatting in Excel:

Excel for Office 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007

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.

Remove Duplicates using Conditional formatting

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

Image-10-Duplicate-Value

Since 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

  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.

Remove Duplicates in Excel using this steps

Now, after completing the process, you can check for duplicate values in another sheet. Although, you can see no duplicate values and all same value got permanently deleted.

Find & Remove duplicates in Excel

Excel for Office 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007

In Excel, there are several ways to filter for unique values—or remove duplicate values:

  1. Select the cells you want to check for duplicates.
  2. Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
  3. In the box next to values with, pick the formatting you want to apply to the duplicate values, and then click OK.

This is how we successfully completed on how to remove duplicate in Excel with this simple steps