How to Filter Duplicates in Excel (Easiest 7 ways)

If you are looking for the easiest ways to filter duplicates in Excel then you are in the right place. You can get a clear concept of filtering duplicates by reading this article.

Filtering data in Excel is a common concept for getting access to desired data easily. While working on a large sheet for any type of purpose, some duplicate data may appear. So, it becomes tedious to work with this repetitive data and to solve this problem filtering duplicate data is essential.

In this article, I have tried to cover the possible easiest ways to filter duplicates in Excel.

Download Excel Workbook

7 Methods to filter duplicates in Excel

Let’s get introduced to the data table first. I am working here with a table that has 3 columns and 18 rows. The columns are named Ship Mode, Province, and Customer Segment. The table has been shown below.

dataset to filter duplicates in excel

Method 1: Using Remove Duplicates option under the data tab

  1. Now, to remove duplicate data click on the Remove Duplicates ribbon under Data Tab as shown below:

using remove duplicates under data tab

2. After selecting the Remove Duplicates ribbon the following pop-up will appear, here you can select Select All options or you can filter according to your preference.

remove duplicates dialog box

  1. After pressing OK the following filtered data will appear.

data table after filter duplicates in excel

Method 2: Using Advanced Filter under data tab

  1. For this method, You have to select the Data tab and then the Advanced option on the Sort & Filter area.

selecting advanced filter under data tab

  1. Then the following pop-up will appear where you have to select Copy to another location and then select the data table as list range.

filter duplicates in excel using advanced filter

  1. After that, you have to select the Unique records only and then select the copy to option and the cell in the Excel where the new filtered data table should appear.

Copy to the selected area the new dataset

  1. Then the following filtered data table will appear.

filter duplicates in excel using advanced filter

Method 3: Using Pivot Table to filter duplicates

  1. After selecting the data table, click on the pivot table option under the Insert tab as shown below.

pivot table to filter duplicates in excel

  1. Then the following Create PivotTable pop-up will appear where you have to select the following two options as shown below.

Create PivotTable dialog box

  1. Then a new sheet containing PivotTable and PivotTable Fields will appear.

PivotTable and PivotTable Fields

  1. Here I have dragged Ship Mode and Province fields to the Rows area and Customer Segment to the Columns area. It is up to your choice. After that, the filtered data will appear on the left side.

filtered data set using pivot table

Method 4: Using Power Query to filter duplicates

  1. At first, you have to select From Table/Range option under the Data tab.

Power Query to filter duplicates in excel

  1. Then you have to select the data range and don’t forget to click on the option named My table has headers.

Create Table dialog box

  1. Then a Power Query Editor will appear where the table will be formed, here you have to select the table and then select the Remove Duplicates under the Remove Rows option under the Home

Power Query editor

  1. After that, the following filtered table will appear.

table form of dataset

  1. Then You have to click on the Copy Entire Table.

copy entire table of filtered dataset

  1. After that, you will get the following filtered table

filtered dataset using Power Query

Method 5: Using CONCATENATE and COUNTIFS function to filter duplicates

  1. At first, you have to join all of the texts according to a row using the CONCATENATE function in a new column named as Combined Text. The CONCATENATE function refers to different texts which you want to join together.
=CONCATENATE(text1,text2,text3, ....) here text1, text2, text3 are respectively A2, B2, C2

concatenate function

  1. Then the combined text will form as below.

Column of combined text

  1. Now use the COUNTIFS function, whereas the criteria range selects the first row of the Combined Text In the range, the first part has to be referenced as absolute using F4 and the second portion will be referenced as relative because it will change with respect to row. In the case of criteria also the first row of the Combined Text column will be selected using relative referencing.
=COUNTIFS(criteria range1,criteria1,...) here criteria range =$D$2:D2 ,criteria=D2

COUNTIF Function

  1. After that, the following count column will be created.

column of count values

  1. Now, select the count column and the Filter option in the Sort & Filter area as below.

filter duplicates in excel

  1. As per the following you have to click on 1 only. Because only number 1 here contains the unique data.

  1. After clicking on Ok, the following filtered table will be formed.

filtered data range using COUNTIFS

Method 6: Using Dynamic Array to filter duplicates

  1. You have to use the UNIQUE function which will give us the unique values by filtering duplicates.                    
    =UNIQUE(array ,FALSE ,FALSE), here array=A2:C18, FALSE is for return unique rows, FALSE is for return every distinct item.

UNIQUE function

  1. After entering the function the following table will be formed containing filtered data.

filtered dataset using UNIQUE Function

Method 7: Using Conditional Formatting to filter duplicates

  1. First, select the data table range and then select the Duplicate Values option under Highlight Cells Rules under Conditional formatting as shown below.

using conditional formatting to filter duplicates

  1. Then the following pop-up will appear where you will have to select the duplicate option and you can select the formatting as per your own choice. I have selected the following formatting for duplicate values.

Duplicate Values dialog box

  1. Then you have to select the Filter option from the Sort & Filter area and then you have to filter by the column province as it has the unique value here only and then select the option Filter by Color and click on the option Filter by Cell Color as No Fill.

Filter by color

  1. Now the following filtered table will be formed.

Filtered Dataset using Conditional formatting

 Conclusion:

These are the easiest ways to filter duplicates in excel. Hope this article will fulfill your requirements. If you know any further ways to filter duplicates in excel you can share with us. Feel free to ask any questions. Thank you.

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo