How to filter duplicate data in Excel

Filtering duplicate data in Excel on the same sheet will help you quickly detect incorrect and duplicate data in Excel tables. From there, users can easily delete duplicate data in Excel. Especially, Excel also supports you to create duplicate data entry messages on Excel in case you are working directly with data tables. And to filter duplicates in Excel, you have as much as filtering duplicate data in Sheets, according to the instructions below.

Filter duplicates in Excel with Remove Duplicates

Remove Duplicates is a feature available in Excel that makes it easy to find and filter duplicate data in the same sheet.

Step 1:

First, you highlight the data column or data area to be filtered. Then you press select Data in the toolbar and then click on Remove Duplicates feature.

Remove Duplicates Tool

Step 2:

Then we will see the display interface, you click next Remove Duplicates…

Filter data with Remove Duplicates

Next Check the data column to delete duplicate data that you want to filter, click OK to do.

Select the data to filter

Step 3:

Soon will display the number of duplicate data cells and immediately delete duplicate data in the data table as shown below.

Delete duplicate data

How to mark duplicate data in Excel

If you do not want to automatically delete duplicate data in Excel, we can mark the duplicate data to check, to avoid deleting the wrong data.

Step 1:

First, you also highlight the data area to search for data. Then tap Conditional Formatting, continue to choose Highlight Cells Rules then press select Duplicate Value.

Conditional Formatting in Excel

Step 2:

Display the interface for you to set up. First of all we will Choose to mark duplicate data or non-duplicate data. If you choose Unique, it will display the value that does not match in the data series we zoned. If you select Duplicate, it will mark duplicate data.

See more:  How to save LibreOffice files in Microsoft Office format

Next Values ​​with choose color data markup.

Select the conditional formatting style in Excel

As a result, you will see the same data as shown below.

Highlight duplicate data in Excel

Filter duplicate data in Excel with Advanced Filter

Step 1:

We select the range of cells or columns to filter duplicate values.. Then at Data group you click Sort & Filter tool you press select Advanced.

Advanced Filter in Excel

Step 2:

Show the new interface, we click Copy to another location. In Copy to, you click in the box in the new location area to display the filtered value. Tick Select the box Unique records only and press OK.

Set up Advanced Filter in Excel

Step 3:

Then the data that does not overlap will be copied to the location area you have selected.

Filter out duplicate data in Excel

So you have many ways to filter duplicate data in Excel, delete duplicate data immediately if you want or just highlight duplicate data in Excel only.

Source link: How to filter duplicate data in Excel

, , ,

Leave a Reply

Your email address will not be published. Required fields are marked *