find duplicates in Excel, remove duplicates in Excel, finde unique values in Excel

7 ways how to find or remove duplicates in Excel

Here are seven ways how to find or remove duplicates in Excel that are helpful to do other necessary actions with data. For example, filtering, deleting, or fixing duplicates. Sometimes duplicates exist if there is something missing that makes records unique.

 

Before you are working with duplicates in Excel and if you are unsure what you are doing, create a copy of the original data. The first couple of methods will help you to identify duplicates in Excel without deleting anything.

 

Here is a list of Excel duplicate detection or removal techniques in this blog post.

  1. Highlight duplicates in Excel with conditional formatting.
  2. Use functions COUNTIF and COUNTIFS and create features to find duplicate, distinct or unique values.
  3. Find duplicates with Excel Pivot Table.
  4. Filter distinct values with Excel Advanced Filter.
  5. Use function UNIQUE to get unique or distinct values.
  6. Use the Remove Duplicates tool.
  7. Remove duplicates with Power Query.

 

Highlight duplicates in Excel and use the result in the filter

One of the ways to find duplicates in Excel is by using conditional formatting in Excel. I have a simple table where it is necessary to detect duplicates in a single column.

table with duplicates in Excel

After selecting data, it is necessary to choose conditional formatting that highlights duplicates.

find duplicates with conditional formatting in Excel

Here is the result where I can see records with duplicates visually.

highlighted duplicates in Excel

It is possible to use cell formatting to filter.

filter by conditional formatting in Excel

 

Use COUNTIF or COUNTIFS to detect duplicates and filter unique or distinct values

By using the function COUNTIF, you can count the appearance of value in a range in different ways. It is possible to get the count of certain values, and you can use results to find duplicates in Excel and get unique values.

=COUNTIF($A$2:$A$13, A2)

find duplicates with COUNTIF in Excel

If you want to find the number of repetitions of a certain value, it is possible to use the function COUNTIF to create an index for column values.

=COUNTIF($A$2:A2, A2)

index column with COUNTIF in Excel

Now you can use a filter to get records where something appears the first time, the second time, etc.

If you have to check for duplicates in more than one column in the table, use the function COUNTIFS.

=COUNTIFS($A$2:$A$13, A2, $B$2:$B$13, B2)

duplicates in column combination in Excel

 

Find duplicates with Excel PivotTable

You can use Excel PivotTable to group values by rows and detect duplicates.

detect duplicates with Excel PivotTable

If you are using PivotTable, I recommend you add conditional formatting. Here is a post from this blog on how to add conditional formatting to Excel Pivot Table.

You can also use a multiple-column combination to find duplicates with PivotTable. Try a tabular layout in the design tab.

detect duplicates using multiple columns in Excel PivotTable

 

Use Excel advanced filter to get distinct values

Excel advanced filter is an excellent tool that can do complex filtering in one take. There is also possible to get distinct values by using a filter like this.

filter unique records with Excel advanced filter

Here is the result.

Excel advanced filter unique records result

The same method works if you are using multiple columns to filter out duplicates. If you want to know more about what you can do with the advanced filter, look at this post that contains useful tips and tricks.

 

Get data without duplicates automatically with the Excel function UNIQUE

UNIQUE is a relatively new Excel formula that can automatically remove duplicates.

=UNIQUE(A1:A13)

how to use the Excel function UNIQUE

Excel function UNIQUE will return a table without duplicates if you are using a combination of multiple columns.

There is also the argument exactly_once that can get you unique or distinct values. Here is the difference between unique and distinct values in the customer ID column.

unique and distinct in Excel

 

Remove Duplicates tool in Excel

There is a dedicated tool to delete duplicates in Excel. You can find that in the Data tab.

where is remove duplicates tool in Excel

You can choose between one and multiple columns to look for duplicates and delete them.

remove duplicates in multiple Excel columns

Here is a result with the table that has two columns.

result of remove duplicates in Excel

Meanwhile, you can use that as a duplicate detection tool. If you can remove something as a duplicate, that means that present. After using this tool, it is also possible to undo that action.

 

Power Query to find and remove duplicates in Excel or Power BI

Power Query is a great tool to repeat data transformation of processes automatically and is available in Excel and Power BI. You can select one or multiple columns, right-click on the header section and choose to remove duplicates.

remove duplicates in Power Query