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

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

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.

Highlighting

Replace the Decimal Comma (,) with a Decimal Point (.).

Formatting issue resolved-Highlight Duplicates in Excel Not Working

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.

Spaces-Highlight Duplicates in Excel Not Working

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

Leading Spaces

There are also Trailing Spaces.

Trailing Spaces

Some values may contain Extra Spaces between words.

Extra Spaces

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

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

Text and Number issue-Highlight Duplicates in Excel Not Working

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.

Mismatch Range-Highlight Duplicates in Excel Not Working

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.

Formula correction

Apply the formula after the correction.

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

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.

Absolute reference-Highlight Duplicates in Excel Not Working

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.

Formula correction