Is there a way to check for duplicate values in Excel WITHOUT using the CountIf function?

Question posted 2012 · +5 upvotes

A lot of the solutions here on SO involve using CountIf to find duplicates. When I have a list of 100,000+ values however, it will often take minutes for CountIf to search for duplicates.

Is there a quicker way to search for duplicates within an Excel column WITHOUT using CountIf?

Thanks!

EDIT #1:
After reading the comments and replies I realize I need to go into greater detail. Let’s pretend I’m a birdwatcher, and after I return from a birdwatching trip I input anywhere from 1 to 25 or 50 new birds that I saw on my trip into my “Master List of Birds Seen”. This is really a dynamically growing list, and with each addition I want to make sure I’m not duplicating something that already exists in my list.

So, in column A of my file are the names of the birds. Column B-M might contain other attributes of the birds. I want to know if a bird that I just added in column A after my latest birdwatching trip ALREADY exists somewhere ELSE in my list. And, if it does, I would manually merge the data of the 2 entries and throw away some and keep some after careful review. I clearly don’t want to have duplicate entries of the same bird in my database.

So, ultimately I want some indication that there is or isn’t a duplicate somewhere else, and if there is duplicate please tell me what row to look in (or highlight or color both of the duplicates).