Tip: Easily count and/or color duplicate values

Save 5 minutes a day by using ASAP Utilities to quickly identify the duplicated/repeated values.

"How can I see which are the duplicate values?"

Just select the cells and then start:
ASAP Utilities » Information » Count and/or color duplicates in selection...

Count and color duplicated values

Do you recognize any of these situations?

  • How can I highlight duplicates in Excel?
  • I need to quickly verify that my list of email addresses doesn't contain any duplicates.
  • I'm using Excel's =COUNTIF() to count duplicates, but all cells with long text entries (>255 chars) are being counted as the same.
  • When I format any column of my spreadsheets to highlight duplicate values, my filtering slows to a crawl. It is entirely frustrating! I noticed the same issue on the last version of Excel and I just got a new pc with a super-fast processor so I don't think it is the computer. Even when I shorten the rows to 2000 I still have the same problem. Is there any better way to highlight duplicate values within a column that will not slow my filtering down?

Just select the cells and then use the following tool: ASAP Utilities » Information » Count and/or color duplicates in selection...

This often a quicker alternative than using Excel's =COUNTIF() function which requires extra steps and is limited to a length of 255 characters. Longer values that are duplicated aren't recognized by this formula. ASAP Utilities doesn't have this 255 characters limit.

Excel also has the option to highlight the duplicates via conditional formatting. In fact, they've made it quite easy to do this in Excel 2007 and later.
However, this conditional formatting doesn't recognize duplicate entries with more than 255 characters and conditional formatting formulas keep running in the background which can make your workbook slow.

Bonus tips, also interesting

How much time will it save?

It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool.
You can easily see how much time ASAP Utilities has saved you so far.


In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial.
Download page