[Fix:] Highlight Duplicates in Excel Not Working

We have a dataset containing Product Sales on a certain date. We want to find the duplicates in the Product, Quantity, and Unit Price columns. It seems the Highlight Duplicates feature in Excel is not working.

Dataset-Highlight Duplicates in Excel Not Working

Reasons Behind Highlight Duplicates Not Working in Excel

Here are the most common sheet settings that might influence conditional formatting and highlighting issues:

Data Formatting
Leading, Trailing, or Extra Spaces
Text and Number Data Type
Mismatch between used and Formula Assigned Ranges
Missing Absolute References

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

Excel highlights duplicates within the Unit Price column but not all of the duplicates get highlighted. It 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 a Decimal Point (.).

Issue 2 – Leading, Trailing, or Extra Spaces Prevent Highlighting

Apply Highlight Duplicates for entries in the Product column.
Some of the values are not highlighted.

Inspect a couple of the entries.
There are Leading Spaces in some of them.

There are also Trailing Spaces.

Some values may contain Extra Spaces between words.

Leading, Trailing, or Extra Spaces results in recognizing these values as unique entries.
Trim or remove all extra Spaces from the entries.

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. Excel 365 highlights the duplicates irrespective of their data format (i.e., Text, Number, or Currency). However, some previous versions don’t work as well. Make sure you keep all the entries in the same format (i.e., Text, Number, or Currency) prior to using the Highlight Duplicates feature.

Issue 4 – Mismatched Ranges Cause Highlight Duplicates Not Working

Using Home > Conditional Formatting > New Rule, users can input a formula to determine which cells to format. However, the formula doesn’t highlight desired entries, as depicted in the picture below.

If you want to determine the issue, match the ranges within the formula and entries. The entries are in Column B whereas the formula inserts arguments for Column A. Correct the formula with the proper range.

Apply the formula after the correction.

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

Issue 5 – Missing Absolute References ($) in Formulas

For an example with highlighting duplicates, the formula

=COUNTIF($B5:$B5,$B5)>1 is supposed to highlight the duplicates among the entries. But it doesn’t.

For the formula to work, the starting value of the range must be fixed, so the first instance of B5 needs to be completely absolute ($B$5).
Fix the reference and apply it.