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!

clones

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.

1. 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…

condition-formatting-highlight-duplicates

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

highlight

2. How to Count Duplicates

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

=COUNTIFS(range, cell)
countifs

3. How to Delete and Remove Duplicates

Step 1: Select the list of values.

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

remove-duplicates

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

remove-duplicates-2

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.

remove-duplicates-status

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


4. How to Count Case-Sensitive Duplicates

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

=SUMPRODUCT(--EXACT(range, cell))
count-case-sensitive-duplicates

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 our Excel workshops. If you like this article and is interested in mastering Excel to advace your career, check out our Excel courses.

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.

Get The Defeat Excel Handbook Today!

50+ Frequently asked how-tos

No more struggling with how to ask an Excel question. This book is written exactly the way you asked questions.

PDF Format

Easily search through the book to find the solution you need. Plus it works offline too.

Effective solutions

No need to read through a few thousand words or long videos. Each solution is carefully chosen to be short, direct and effective.