logo ASAP Utilities
HomeExtra features for ExcelTip of the Day | Sunday March 15th, 2026

Tip of the Day | Sunday March 15th, 2026

Discover new tools to get things done. ASAP Utilities makes YOU rock in Excel!

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

    Info & List  ›  Highlight Each Set of Duplicates with a Different Color

    Starting this tool


    Info & List  ›  Highlight Each Set of Duplicates with a Different Color

    Choose your language preference below

    English (us) ⁄ Nederlands ⁄ Deutsch ⁄ Español ⁄ Français ⁄ Português do Brasil ⁄ Italiano ⁄ Русский ⁄ 中文(简体) ⁄ 日本語

    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 26 years, ASAP Utilities has helped users worldwide save time and do what Excel alone cannot.