Maruf Islam
Excel users get their datasets from external sources. As a result, some of the operations such as highlight duplicates in Excel not working issues occur. Oblivion to the data Formatting, Spacing, Assigned Ranges in Formulas or Reference Excel’s duplicate feature seems not working. However, what may be the reasons, Excel hasn’t broken.
Let’s say we have a dataset containing Product Sales on a certain date. We want to find the duplicates in Product, Quantity, and Unit Price columns. For whatever issue, it seems the Highlight Duplicates feature in Excel is not working.
In this article, we demonstrate ways to resolve the highlight duplicates in Excel not working issues.
Download Excel Workbook
Reasons Behind Highlight Duplicates Not Working in Excel
The Highlight Duplicates feature is available under the Conditional Formatting section. Executing Highlight Duplicates highlights identical values within a selection. Identical values mean entries that have the same Value, Format, and Datatype (i.e., Text or Number). Ignoring one of them leads Excel to assume that they are not identical.
If we want to find reasons for Highlight Duplicates unable to find duplicates the followings are prominent:
🔺 Data Formatting
🔺 Leading, Trailing, or Extra Spaces
🔺 Text and Number Data Type
🔺 Mismatch between used and Formula Assigned Ranges
🔺 Missing Absolute References
In the upcoming sections, we discuss and resolve the issues that cause Highlight Duplicates in Excel not working.
Resolving Issues Regarding Highlight Duplicates Not Working
Issue 1: Data Formatting Results in Highlight Duplicates Not Working
🔼 For the dataset, apply the Highlight Duplicates feature by going Home > Conditional Formatting > Highlight Cells Rules > Duplicates Values.
🔼 Excel displays the Duplicate Values window.
⧫ Select Duplicates as Format cells that contain.
⧫ Choose a Highlighting Color or Default Color.
⧫ Click on OK.
🔼 You see Excel highlights duplicates within the Unit Price Column but not all of the duplicates get highlighted. You see Excel doesn’t highlight entries with Decimal Comma (,) instead of Decimal Point (.). It’s one of the reasons Excel doesn’t recognize identicals.
🔺 Replace the Decimal Comma (,) with Decimal Point (.), you see Excel automatically highlights all the duplicates previously it was unable to do.
Read More: How to Highlight Duplicates in Excel with Different Colors (2 Ways)
Issue 2: Leading, Trailing or Extra Spaces Cause Not Highlight Duplicates Issue
🔼 Following the Steps of Issue 1, after applying Highlight Duplicates for entries in the Product column; you see no highlighting of duplicates as shown in the below picture.
🔺 Inspect a couple of the entries you see Leading Space or Spaces in them.
🔺 Also, you see Trailing Space or Spaces.
🔺 Some of them may contain Extra Spaces between words.
Containing Leading, Trailing or Extra Spaces results in recognizing them as unique entries. Therefore, Highlight Duplicates tends to ignore or are unable to highlight them in the first place.
🔺 Trim or remove all kinds of Spaces from the entries, you see Excel highlights all the duplicates as it is supposed to.
Read More: How to Highlight Duplicates in Multiple Columns in Excel (4 Ways)
Issue 3: Distinguishing Text and Number to Highlight Duplicates
Sometimes, users store the same entry values in different formats such as Text, Number, and Currency. Though Excel 365 highlights the duplicates irrespective of their data format (i.e., Text, Number, or Currency).
However, some Excel previous versions don’t support this kind of feature. So, when working on Excel versions except Excel 365; make sure you keep all the entries in the same number format (i.e., Text, Number, or Currency) prior to Highlight Duplicates feature execution.
Read More: Highlight Duplicates across Multiple Worksheets in Excel (3 Formulas)
Issue 4: Mismatch Ranges Cause Highlight Duplicates Not Working
🔼 For a certain range, users apply formulas to highlight duplicates within the range. Using Home > Conditional Formatting > New Rule, users input a formula to determine which cells to format. However, the application of the formula doesn’t highlight desired entries as depicted in the below picture.
🔺 If you want to find out the wrongdoing, just match the ranges within the formula and entries. You see the entries are in Column B whereas the formula inserts arguments for Column A entries. Mismatching ranges among formulas and data results in Highlight Duplicates not working. Correct the formula with the proper range.
🔺 Apply the formula after the correction. You see the formula range is matched with the Applies to the range. Click on Apply.
🔺 Excel highlights all the duplicates as you can see from the below screenshot.
User’s incorrect insertion of ranges or formulas is the most common reason behind Excel features malfunctioning. And Highlight Duplicates in Excel not working is not an exception.
Read More: How to Highlight Duplicate Rows in Excel (3 Ways)
Issue 5: Missing Absolute Reference ($) in Formulas
🔼 Users use Special Formulas for specific purposes that contain Absolute References that ensure logic in findings. Missing Absolute References leads to undesirable outcomes in Excel. In case of highlighting duplicates, the formula =COUNTIF($B5:$B5,$B5)>1 is supposed to highlight 2ndduplicates among the entries. But it doesn’t.
🔺 If you thoroughly check the Criteria within the COUNTIF function, you realize there must be an Absolute Reference of the 1st Cell Reference to satisfy the Criteria.
Therefore, just put a Dollar Sign ($) in the 1st Cell Reference making it an Absolute Cell Reference.
🔺 After making the reference as Absolute Cell Reference, Apply the formula to highlight the 2ndduplicates.
🔺 In a moment, you see all the cells get highlighted exception of the previous attempt.
Absolute References refer to logical arguments to be weighed and without them, formulas don’t find any similarities or discrepancies to highlight or sort.
Read More: How to Highlight Duplicates in Two Columns in Excel
Conclusion
In this article, we demonstrate probable reasons and resolve them regarding Highlight Duplicates in Excel not working. You may have one or multiple issues with your dataset for not being highlighted after Highlight Duplicates execution. Hope this article clarifies your concept of highlighting duplicates and their issues. Comment, if you have further inquiries or have anything to add.
