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

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 Remove Duplicates in Excel?

  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.


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


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.


  1. Check Airport Name and click OK.


➡ Steps on how to do Advanced Filter in Excel:

  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

  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.


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:

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.


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


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.


  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.


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.

Complete, Step by step video to remove duplicate in Excel

Pre-Registration Open

Power BI Dax

Related Tutorials

Text To Column in Excel | Split Cells
January 4, 2019
Pivot Tables (Complete Guidelines)
December 27, 2018
Power Bi Dax Deduplication Based On Column
December 17, 2018
Special Character Symbol List with Shortcodes in Excel
December 15, 2018
December 10, 2018
December 10, 2018
How to use ABS Function in Excel
December 10, 2018
How to use AND Function in Excel
December 10, 2018
Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018