logo ASAP Utilities
HomeExtra features for ExcelRangeHighlight Each Set of Duplicates with a Different Color

The ASAP Utilities menu in Excel (English)

Excel with ASAP Utilities in its menu
Trustpilot

Highlight Each Set of Duplicates with a Different Color

ASAP Utilities  › Range  ›  22. 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.

    Screenshots

    Range  ›  22 Highlight Each Set of Duplicates with a Different Color

    Starting this tool


    Range  ›  22 Highlight Each Set of Duplicates with a Different Color
    Additional keywords for 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
    Trustpilot
    Download  



    • Home Privacy Policy Cookie Policy EULA Download All added Excel tools Sitemap Contact Us


    🏆 Winner of the "Best Microsoft Excel Add-in" Award at the Global Excel Summit 2025 in London!
    For over 25 years, ASAP Utilities has helped users worldwide save time and do what Excel alone cannot.