![]() ![]() EXCEL HIGHLIGHT DUPLICATES HOW TOWe’ve got you covered with a step-by-step look at how to highlight duplicates in Google Sheets, complete with images to make sure you’re on the right track when it comes to de-duplicating your data. The potential problem raises a good question: How do you highlight duplicates in Google Sheets? ![]() Given the volume and variety of data now entered by teams, it’s possible that duplicate data in tools like Google Sheets may be relevant and necessary, or it could be a frustrating distraction from the primary purpose of spreadsheet efforts. If something should be fixed, it is at least the documentation.Duplicate data is the bane of spreadsheet solutions, especially at scale. I almost deleted unique values considering them to have duplicates somewhere in the list.Ĭurrent behavior is logical from Excel viewpoint but draws a huge exclamation mark for use by inexperienced users. Today, I took a list of 2000 values to provide data for the customer and false positives were spotted only by coincidence and after double checking of results. You were just presented by Duplicate values conditional formatting type without further explanation. You were not warned, that you need a special formulas to discover real duplicates. and the results of showing duplicate values can be quite unexpected as you can see in the question or here:īased on the above, I would say this is not a defect, but an unexpected and undocumented behavior.Īnd this poses a problem in real-life scenarios. It is possible that Excel simply uses its standard search algorithm (which honors wildcard characters) also for searching of cells with duplicate values. īut back to duplicates: it can be discussed whether sample values PT_INTERNAL2859736 and *736 are duplicates by definition. functions like SEARCH(), SEARCHB(), COUNTIF().On the contrary, support of wildcards is explicitly named in functionalities, where presence of wildcards is obvious and expected: Nowhere in these articles is mentioned that the internal algorithm searching for duplicates still respects wildcard characters ( *, ?, ~). Highlight patterns and trends with conditional formatting.Filter for unique values or remove duplicate values.Microsoft documents the Duplicate Values mode of conditional formatting in the following articles: The question unanswered before is if they are expected also in conditional formatting. Other answers reminded us of use of wildcard characters ( *, ?, ~) in Excel formulas. This might become an issue when processing xml files where the lines normally start with <.Īfter further research of the behavior and documentation, I can answer the question from high-level perspective: This is rather an undocumented behavior than a defect. See an example here (each column is a separate example). Similar undocumented behavior can also be observed with other symbols such as, = when these symbols are placed in the beginning of the text in a cell and there are 2 or more non-empty cells in the column. Is this an expected behavior of the Duplicate Values functionality (usefulness of which I am overlooking)? Or is this rather a defect which has to be reported? The other will lose the highlighting but the *736 will keep it! Now, if you reproduced the behavior, try to delete one of PT_ values. The problem can be reproduced based on the image. I thought the Duplicate Values rule is reliable until I found this case. Why this happens and how can I stop highlighting the unique value as duplicate? If I let Excel highlight two duplicate values PT_INTERNAL2859736, then also unique value *736 gets highlighted. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |