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.
- Highlight duplicates in Excel with conditional formatting.
- Use functions COUNTIF and COUNTIFS and create features to find duplicate, distinct or unique values.
- Find duplicates with Excel Pivot Table.
- Filter distinct values with Excel Advanced Filter.
- Use function UNIQUE to get unique or distinct values.
- Use the Remove Duplicates tool.
- 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.
After selecting data, it is necessary to choose conditional formatting that highlights duplicates.
Here is the result where I can see records with duplicates visually.
It is possible to use cell formatting to filter.
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)
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)
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)
Find duplicates with Excel PivotTable
You can use Excel PivotTable to group values by rows and detect duplicates.
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.
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.
Here is the 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)
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.
Remove Duplicates tool in Excel
There is a dedicated tool to delete duplicates in Excel. You can find that in the Data tab.
You can choose between one and multiple columns to look for duplicates and delete them.
Here is a result with the table that has two columns.
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.
