Tip of the Day | Saturday 7 November 2026

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