"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...
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
- ASAP Utilities » Range » Clear duplicated values in selection (leave one original)
- Select duplicates via: ASAP Utilities » Select » Select cells based on content, formatting and more...
- ASAP Utilities » Information » Fast calculation on selected cells...
- ASAP Utilities » Text » Delete leading, trailing and excessive spaces
- Microsoft: Not all list items are displayed in the AutoFilter list in Excel
- Microsoft: Count occurrences of values or unique values in a data range
- Microsoft: Count how often a value occurs
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.