Highlight Each Set of Duplicates with a Different Color

This tool finds and highlights all duplicate values in your selected range.
Each group of matching values is filled with a different color or pattern, so you can quickly spot and distinguish duplicate sets across your data.

For example:

• If "London" appears in five cells, all five will be filled with the same color (e.g., blue).
• If "Paris" appears in three cells, those cells will be filled with a different color (e.g., green).
  Each set of duplicates gets its own distinct formatting.

This is helpful when:

• You want a clear, color-coded view of all repeated values
• You need to visually group duplicates across columns or rows
• Excel's built-in Conditional Formatting or COUNTIF functions fall short, especially with long or complex values

The tool uses a wide range of distinctive colors and fill patterns to help each group stand out.
It supports up to 264 unique sets of duplicates. If more are found, the additional sets will share the same color, and a message will inform you which duplicates could not be uniquely formatted.

After the process, you are informed of:

• The total number of cells containing duplicate values
• The number of unique duplicated values found

Note:

• The tool is case-sensitive. For example, "Excel" and "EXCEL" are considered different values.
• If your selection includes formulas, the tool compares the calculated values, not the formulas themselves. For instance, if one cell displays the number 12 and another contains the formula =3*4, resulting in 12, they are considered duplicates.
• Hidden cells within your selection are included in the analysis.
• In some instances, such as when working with long numeric values like credit card numbers, this tool provides more accurate results than Excel's built-in methods like Conditional Formatting or COUNTIF, which can misidentify such values as duplicates due to rounding.

Starting this tool

Additional keywords to help you find this tool:
highlight duplicate values, color code duplicates, Excel duplicate sets, find and color duplicates, duplicate values different colors, identify duplicate values visually, Excel highlight matching values, case-sensitive duplicate finder, Excel compare duplicate cells, group duplicates by color, color each duplicate group, show duplicates with formatting, detect repeated values Excel, better alternative to COUNTIF for finding duplicates

Tip:Alt+P for the previous tool.