fbpx

How to Find, Count, Delete and Remove Duplicates in Excel

No matter which profession you are in, one annoying enemy is always present — duplicates. They multiply themselves like clones, and no matter how many times you scroll and press Delete, they always seem to be never-ending.

Today we’ll discuss simple and fast methods to find, count, delete and remove these duplicates from our Excel worksheets.

Exterminate these pesky clones, you will!

How to Find, Count, Delete and Remove Duplicates in Excel 1
Dealing with Duplicates

Summary

NO TIME TO READ?

Grab the PDF version of this article to read it at a later time or print it out for your desktop reference.

Click here to get the Excel file to follow along. The file contains the following list as our example throughout this article:

How to Find and Highlight Duplicates

Step 1: Select the list of values.

Step 2: Go to Home | Styles | Conditional Formatting | Highlight Cell Rules | Duplicate Values…

How to Find, Count, Delete and Remove Duplicates in Excel 3
Conditional Formatting to find duplicates

Step 3: In the Duplicate Values window, choose the preferred highlight format and colors.

How to Find, Count, Delete and Remove Duplicates in Excel 5
Find and highlight duplicates

How to Count Duplicates

To count the number of duplicates, we can use the following formula:

=COUNTIFS(range, cell)
How to Find, Count, Delete and Remove Duplicates in Excel 7
Count duplicates

How to Delete and Remove Duplicates

Step 1: Select the list of values.

Step 2: Go to Data | Data Tools | Remove Duplicates.

How to Find, Count, Delete and Remove Duplicates in Excel 9
Remove duplicates

Step 3: In the Remove Duplicates window, click OK.

How to Find, Count, Delete and Remove Duplicates in Excel 11
Remove Duplicates window

Step 4: Excel will provide you with a summary. Except for the first duplicate value, the rest of the duplicates will be deleted and removed.

How to Find, Count, Delete and Remove Duplicates in Excel 13
Remove Duplicates summary

Note that all the methods above are not case-sensitive (i.e. Excel treats “Apple”, “apple”, “apPLe” as duplicates).

How to Count Case-Sensitive Duplicates

To count the number of case-sensitive duplicates, we can use the following formula:

=SUMPRODUCT(--EXACT(range, cell))
How to Find, Count, Delete and Remove Duplicates in Excel 15
Count

The EXACT function tells Excel that you want to search for something exactly like the cell.

Conclusion

Duplicates are irritating and they often can multiply out of control. Learning to control them is a good skill set to have.

Dealing with duplicates is one of the modules in my self-paced online course, DataCraft. If you like this article and is interested in mastering Excel to jumpstart your career, check out this premium course on data analysis — the fast track to Excel mastery.

NO TIME TO READ?

Grab the PDF version of this article to read it at a later time or print it out for your desktop reference.

Leave a Comment

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

The Premium Data Analysis Course

Special Promo ends in

Days
Hours
Minutes
Seconds