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.
Method 1: Using Remove Duplicates option under the data tab
- Now, to remove duplicate data click on the Remove Duplicates ribbon under Data Tab as shown below:
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.
- After pressing OK the following filtered data will appear.
Method 2: Using Advanced Filter under data tab
- For this method, You have to select the Data tab and then the Advanced option on the Sort & Filter area.
- 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.
- 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.
- Then the following filtered data table will appear.
Method 3: Using Pivot Table to filter duplicates
- After selecting the data table, click on the pivot table option under the Insert tab as shown below.
- Then the following Create PivotTable pop-up will appear where you have to select the following two options as shown below.
- Then a new sheet containing PivotTable and PivotTable Fields will appear.
- 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.
Method 4: Using Power Query to filter duplicates
- At first, you have to select From Table/Range option under the Data tab.
- Then you have to select the data range and don’t forget to click on the option named My table has headers.
- 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
- After that, the following filtered table will appear.
- Then You have to click on the Copy Entire Table.
- After that, you will get the following filtered table
Method 5: Using CONCATENATE and COUNTIFS function to filter duplicates
- 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
- Then the combined text will form as below.
- 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
- After that, the following count column will be created.
- Now, select the count column and the Filter option in the Sort & Filter area as below.
- As per the following you have to click on 1 only. Because only number 1 here contains the unique data.
- After clicking on Ok, the following filtered table will be formed.
Method 6: Using Dynamic Array to filter duplicates
- 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.
- After entering the function the following table will be formed containing filtered data.
Method 7: Using Conditional Formatting to filter duplicates
- First, select the data table range and then select the Duplicate Values option under Highlight Cells Rules under Conditional formatting as shown below.
- 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.
- 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.
- Now the following filtered table will be formed.
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.