How to Filter Duplicates in Excel (7 Suitable Ways)

Let’s get introduced to the data table first. We have a table that has 3 columns and 14 rows. The columns are named Ship Mode, Province, and Customer Segment. Sample Dataset to Filter Duplicates in Excel


Method 1 – Using Remove Duplicates Tool

The quickest way to remove duplicates is to use the Remove Duplicates tool.

Steps:

  • Select your dataset
  • Go to Data tab >> Data Tools group >> Remove Duplicates tool.

Access the Remove Duplicates Tool

The Remove Duplicates pop-up will appear,

  • Click on the Select All button, or you can filter according to your preference.
  • Click on the OK button.

Remove Duplicates Window to Filter Duplicates in Excel

The following filtered data will appear.

Filtered Table

Read More: How to Find Duplicate Rows in Excel


Method 2 – Using Advanced Filter Tool

Steps:

  • Select your dataset.
  • Select the Data tab >> Advanced option in the Sort & Filter group.

Select Advanced Filter Option to Filter Duplicates in Excel

The Advanced Filter pop-up will appear.

  • Select Copy to another location.
  • Select the data table as the list range.
  • Select the destination position in the Copy to: text box.
  • Tick Unique records only.
  • Click on the OK button.

Advanced Filter Window

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

Filtered Duplicates in Excel

Read More: How to Find Repeated Cells in Excel


Method 3 – Using Pivot Table

Steps:

  • After selecting the data table, click on the Pivot Table option under the Insert tab.

Convert Data into Pivot Table

The PivotTable from table or range window will appear

  • Select the options as shown below:

PivotTable from table or range Window

As we are replacing previous data with new pivot table data, a Microsoft Excel warning box will appear.

  • Click OK.

Microsoft Excel Dialogue Box

The PivotTable and PivotTable Fields pane will appear.

  • Drag the Ship Mode and Province fields to the Rows area and Customer Segment to the Columns area. Or other fields of your choice.

Arrange your Pivot Table

The filtered data will appear on the left side.

Filtered Table

Read More: How to Find Repeated Numbers in Excel


Method 4 – Using Power Query

Steps:

  • Select your dataset.
  • Select the From Table/Range option under the Data tab.

Convert Data to Table

  • Select the data range.
  • Tick My table has headers.
  • Click the OK button.

Create Table Window

A Power Query Editor will appear, where the table will be formed.

  • Hold down the Ctrl key and select the table’s columns individually.
  • Under the Home tab, select the Remove Duplicates option under the Remove Rows tool.

Using Power Query to Remove Duplicates in Excel

The filtered table will appear.

  • Click on Copy Entire Table.
  • Close the Power Query Editor window.

Copy Entire Table to Main Excel Window

The filtered table as specified appears.

Filtered Duplicates in Excel

Read More: How to Compare Rows for Duplicates in Excel


Method 5 – Using CONCATENATE and COUNTIFS Functions

Steps:

This method requires joining all of the texts in a row using the CONCATENATE function into a new column named Combined Text.

  • Click on cell E5 and insert the following formula:
=CONCATENATE(B5,C5,D5)
  • Press Enter.

Using CONCATENATE Function to Combine Text

  • Place your mouse cursor on the bottom right position of the cell and drag the fill handle down to Autofill the rest of the column.

Using Fill Handle Feature to Copy Formula

The combined text will form as below.

Combined Texts

  • Click on cell F5 and insert the following formula:
=COUNTIFS($E$5:E5,E5)
  • Press Enter.

Using COUTIFS Function to Filter Duplicates in Excel

  • Use the fill handle to copy the formula to the rest of the column.

A Count column will be created.

Count Column to Filter Duplicates in Excel

  • Select the Count column.
  • Go to the Home tab >> Editing group >> Sort & Filter tool >> Filter option.

Filter Count Column to Filter Duplicates in Excel

  • Click on filter 1 only, because only number 1 here contains the unique data.

Filtered Count Column

The following filtered table will be formed:

Filtered Duplicates in Excel

Read More: Excel Find Duplicate Rows Based on Multiple Columns


Method 6 – Using Dynamic Array Formula

Steps:

We’ll use the UNIQUE function, which returns unique values by filtering duplicates.

  • Click on cell B5 and insert the following formula:
=UNIQUE('Sample Dataset'!B5:D17,FALSE,FALSE)
  • Press Enter.

Using UNIQUE Function to Filter Duplicates in Excel

The table containing filtered data will be returned.

Read More: How to Compare Two Excel Sheets for Duplicates


Method 7 – Applying Conditional Formatting

Steps:

  • Select the data table range.
  • Go to the Home tab.
  • From the Conditional formatting drop-down, select Duplicate Values under Highlight Cells Rules.

Using Conditonal FOrmatting to Filter Duplicates in Excel

The Duplicate Values pop-up will appear.

  • Select the Duplicate option and formatting of your choice.

Duplicate Values Window

  • Select the Province column, as it is the only column with a unique value.
  • Go to the Home tab >> Editing group >> Sort & Filter tool >> Filter option.

Choose Filter Option to Filter Duplicates in Excel

  • Click on the Filter button.
  • Select Filter by Color.
  • Select No Fill for option Filter by Cell Color.

Filter by Cell Color to Filter Duplicates in Excel

The following filtered table will be formed.

Filtered Duplicates in Excel

Read More: How to Find Matching Values in Two Worksheets in Excel


Download Practice Workbook


Related Contents


<< Go Back to Find Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

1 Comment
  1. greatttttt

    thankssssss

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo