[Fix:] Highlight Duplicates in Excel Not Working

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.

Dataset-Highlight Duplicates in Excel 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.

Formatting-Highlight Duplicates in Excel Not Working

🔼 Excel displays the Duplicate Values window.

Select Duplicates as Format cells that contain.

Choose a Highlighting Color or Default Color.

Click on OK.

Duplicate Values

🔼 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.

Highlighting

🔺 Replace the Decimal Comma (,) with Decimal Point (.), you see Excel automatically highlights all the duplicates previously it was unable to do.

Formatting issue resolved-Highlight Duplicates in Excel Not Working


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.

Spaces-Highlight Duplicates in Excel Not Working

🔺 Inspect a couple of the entries you see Leading Space or Spaces in them.

Leading Spaces

🔺 Also, you see Trailing Space or Spaces.

Trailing Spaces

🔺 Some of them may contain Extra Spaces between words.

Extra Spaces

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.

Spaces issue resolved-Highlight Duplicates in Excel Not Working


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.

Text and Number issue-Highlight Duplicates in Excel Not Working


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.

Mismatch Range-Highlight Duplicates in Excel Not Working🔺 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.

Formula correction🔺 Apply the formula after the correction. You see the formula range is matched with the Applies to the range. Click on Apply.

Formula application

🔺 Excel highlights all the duplicates as you can see from the below screenshot.

Mismatch Range issue resolved-Highlight Duplicates in Excel Not Working

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.


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.

Absolute reference-Highlight Duplicates in Excel Not Working

🔺 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.

Formula correction

🔺 After making the reference as Absolute Cell Reference, Apply the formula to highlight the 2ndduplicates.

Formula application

🔺 In a moment, you see all the cells get highlighted exception of the previous attempt.

Absolute reference issue resolved-Highlight Duplicates in Excel Not Working

Absolute References refer to logical arguments to be weighed and without them, formulas don’t find any similarities or discrepancies to highlight or sort.


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.

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo