How to Filter Duplicates in Excel (7 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

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 Practice Workbook

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


7 Suitable Ways 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 14 rows. The columns are named Ship Mode, Province, and Customer Segment. The table has been shown below.

Sample Dataset to Filter Duplicates in Excel


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.

Access the 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.

Remove Duplicates Window to Filter Duplicates in Excel

After pressing OK, the following filtered data will appear.

Filtered Table


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.

Select Advanced Filter Option to Filter Duplicates in Excel

  • 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.

Advanced Filter Window

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

Filtered Duplicates in Excel


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.

Convert Data into Pivot Table

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

PivotTable from table or range Window

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

Microsoft Excel Dialogue Box

  • 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.

Arrange your Pivot Table

After that, the filtered data will appear on the left side.

Filtered Table


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.

Convert Data to Table

  • 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.

Create Table Window

  • 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.

Using Power Query to Remove Duplicates in Excel

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

Copy Entire Table to Main Excel Window

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

Filtered Duplicates in Excel


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.

Using CONCATENATE Function to Combine Text

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

Using Fill Handle Feature to Copy Formula

  • Then the combined text will form as below.

Combined Texts

  • 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.

Using COUTIFS Function to Filter Duplicates in Excel

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

Count Column to Filter Duplicates in Excel

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

Filter Count Column to Filter Duplicates in Excel

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

Filtered Count Column

Thus, the following filtered table will be formed.

Filtered Duplicates in Excel


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.

Using UNIQUE Function to Filter Duplicates in Excel

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.

Using Conditonal FOrmatting to Filter Duplicates in Excel

  • 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 Window

  • 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.

Choose Filter Option to Filter Duplicates in Excel

  • 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.

Filter by Cell Color to Filter Duplicates in Excel

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

Filtered Duplicates in Excel


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.

And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!

Tanjima Hossain

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.

1 Comment
  1. greatttttt

    thankssssss

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo