How to Filter Duplicates in Excel (7 Suitable 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.

How to Filter Duplicates in Excel: 7 Suitable Ways

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

1. Using Remove Duplicates Tool

The quickest way to remove duplicates is to use the Remove Duplicates tool. Follow the steps below to do this.

📌 Steps:

First, select your dataset >> go to Data tab >> Data Tools group >> Remove Duplicates tool.

After selecting the Remove Duplicates option, the following pop-up will appear, here you can click on the Select All button or you can filter according to your preference.
Following, click on the OK button.

After pressing OK, the following filtered data will appear.

2. Applying Advanced Filter Tool

Another easy way to filter data in Excel is to use the Advanced Filter tool. Go through the steps below to do this.

📌 Steps:

For this method, You have to select your dataset first.
Following, 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 a list range.
Afterward, choose the destination position in the Copy to: text box, tick on the Unique records only option, and click on the OK button.

As a result, the following filtered data table will appear.

3. Employing Pivot Table to Filter Duplicates

Besides, you can use a Pivot Table to filter duplicates in Excel. Follow the steps below to do this.

📌 Steps:

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

Then the following PivotTable from table or range window will appear where you have to select the following options as shown below.

As we are replacing previous data with new pivot table data, a Microsoft Excel dialogue box would appear.
Subsequently, click on the OK button.

Then, the PivotTable and PivotTable Fields pane 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.

4. Using Power Query to Filter Duplicates

You can also use the power Query feature of Excel to accomplish your desired target in this regard. Follow the steps below to achieve this.

First, you have to select your dataset
Following, choose the From Table/Range option under the Data tab.

Next, you have to select the data range and click on the OK button. And don’t forget to click on the option named My table has headers.

Consequently, a Power Query Editor will appear where the table will be formed.
Here, you have to hold the Ctrl key and select the table’s columns individually and then select the Remove Duplicates option under the Remove Rows tool under the Home tab.

After that, the filtered table will appear.
Then You have to click on the Copy Entire Table and close the Power Query Editor window.

After that, you will get the following filtered table as you wanted.

5. Merging CONCATENATE and COUNTIFS Functions to Filter Duplicates

Another unique way to filter duplicates in Excel is to use Excel functions. Follow the steps below to accomplish this.

📌 Steps:

First, you have to join all of the texts according to a row using the CONCATENATE function in a new column named Combined Text. The CONCATENATE function refers to different texts which you want to join together.
So, click on cell E5 and insert the following formula.

=CONCATENATE(B5,C5,D5)

Subsequently, hit the Enter key.

Following, place your mouse cursor on the bottom right position of the cell and drag the fill handle downward upon its appearance.

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.
So, click on cell F5 and insert the following formula.

=COUNTIFS($E$5:E5,E5)

Subsequently, hit the Enter key.

Afterward, use the fill handle feature below; thus, the following Count column will be created.

Now, select the Count column >> Home tab >> Editing group >> Sort & Filter tool >> Filter option.

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

Thus, the following filtered table will be formed.

6. Employing Dynamic Array Formula to Filter Duplicates

You can also use the dynamic array formula to accomplish your desired result in this regard. Go through the steps below to do this.

📌 Steps:

You have to use the UNIQUE function which will give us unique values by filtering duplicates.
So, click on cell B5 and insert the following formula.

=UNIQUE('Sample Dataset'!B5:D17,FALSE,FALSE)

Subsequently, hit the Enter key.

As a result, you will get the table containing filtered data.

7. Applying Conditional Formatting to Filter Duplicates

You can also use conditional formatting to filter duplicates in Excel. Follow the steps below to achieve this.

📌 Steps:

First, select the data table range.
Then, go to the Home tab >> select the Duplicate Values option under Highlight Cells Rules under the Conditional formatting group 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 Province column as it has the unique value here only.
Afterward, go to the Home tab >> Editing group >> Sort & Filter tool >> Filter option.

Following, click on the Filter button and select the option Filter by Color and click on the option Filter by Cell Color as No Fill.

As a result, the following filtered table will be formed.

Download Practice Workbook

You can download our free practice workbook from here for free!

Filter Duplicates.xlsx

Conclusion

These are the most suitable 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 them with us. Feel free to ask any questions. Thank you.

How to Filter Duplicates in Excel (7 Suitable Ways)

How to Filter Duplicates in Excel: 7 Suitable Ways

1. Using Remove Duplicates Tool

2. Applying Advanced Filter Tool

3. Employing Pivot Table to Filter Duplicates

4. Using Power Query to Filter Duplicates

5. Merging CONCATENATE and COUNTIFS Functions to Filter Duplicates

6. Employing Dynamic Array Formula to Filter Duplicates

7. Applying Conditional Formatting to Filter Duplicates

Conclusion

Related Contents

Leave a reply Cancel reply