Aug 28 2008

Quick Way to Check for Duplicates in a Column

I have been using this quick trick lately to check for duplicates in a column of values.  Let’s say you have a list of a large group of numbers (characters work as well) and want to see if you have more than one instance of any of the numbers.  To check for that, one simple was is to use the CountIf formula.

I have already shown you how to use the formula in the SumIf and CountIf Foumulas post. But here I just wanted to mention this is an example where you can use it.

Let’s say we have a column, say column D, with 100 values that you want to confirm do not have any duplicates. To check this, in a column next to the data you are dealing with you can add this formula:

=countif($D$1:$D$100, D1)

Basically what this is saying is count how many times the value of D1 is in the column D for rows 1 to 100. The reason we had to lock the cells (the $ character locks the cells) is that we will need to drag down the formula to do our check. If anything other than 1′s appear in our check column, then we know that we have a duplicate item somewhere. Any easy way to look for the non-1 numbers is to filter the data and look in the drop-down filters available.

Here’s a quick screenshot of how the formula would work for you:

 Countif for Duplicates

That was just a quick tip for today. There are numerous other ways to check for duplicate items in a column, such as using pivottables, but this is a quick and easy trick that you can start using today.

** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 


Comments are closed.

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks