Excel Highlight Duplicates With Unique Colors?

Formulas & functions · Updated November 19, 2026

Yes—but Excel’s built-in duplicate-highlighting command applies one format to every duplicate, not a different color to each repeated value. To give duplicate groups different colors, create separate conditional-formatting rules for known values, or use a helper column to assign each duplicated value a group number and color groups from that number.

What Excel’s Duplicate Highlighting Does

In Excel for Microsoft 365 on Windows, select the cells you want to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a format, and click OK. Excel applies that format to cells whose contents occur more than once in the selected range. The rule updates as values change.

This is a quick way to find repeated names, invoice numbers, product codes, or other entries. However, the command creates one rule and one chosen format for the duplicate cells. It does not automatically give every distinct repeated value its own color. For example, all repeated instances of “Apple” and “Pear” receive the same format if you use the built-in rule.

Conditional formatting changes how cells look; it does not remove duplicates or change their contents. To remove repeated records, use a separate tool such as Data > Remove Duplicates, taking care to select the right columns. Highlighting is usually safer when you want to review the results before deciding what to keep.

Give Specific Duplicate Values Their Own Colors

If you know which values you want to distinguish, make one formula-based rule for each value. For example, suppose the entries are in A2:A100 and you want repeated instances of “Apple” and “Pear” to have different fills. Select A2:A100, then choose Home > Conditional Formatting > New Rule and select Use a formula to determine which cells to format.

With the selected range A2:A100 and the active cell A2, use this formula for the Apple rule:

=AND($A2<>"",COUNTIF($A$2:$A$100,$A2)>1,$A2="Apple")

Choose Format, select a fill color, and confirm. Add another rule for Pear using this formula:

=AND($A2<>"",COUNTIF($A$2:$A$100,$A2)>1,$A2="Pear")

Give the second rule a different fill. The first test excludes blank cells, the COUNTIF test checks that the current value appears more than once, and the final test identifies the particular value. Replace Apple and Pear with the exact text you need. For a number, compare against the number without quotation marks, such as $A2=42.

This approach is straightforward for a short, stable list of values. It becomes tedious if the data changes often or contains many duplicated groups, because each color needs its own rule. If two rules can match the same cell, review their order and settings in Home > Conditional Formatting > Manage Rules.

Use a Helper Column for Many Duplicate Groups

For a longer list, a helper column can assign the same group number to every occurrence of a duplicated value. You can then create a small set of color rules based on those numbers. This method cycles through a chosen palette: with four colors, groups 1 and 5 share a color, as do groups 2 and 6. A finite palette cannot give an unlimited number of groups all-distinct colors.

Assume the values are in A2:A100, and put the helper formula in B2. Fill it down through B100. The formula gives each distinct duplicated value a sequence number based on the order in which it first appears; nonduplicates and blank cells return a blank:

=IF(OR(A2="",COUNTIF($A$2:$A$100,A2)<2),"",SUMPRODUCT(($A$2:A2<>"")*(COUNTIF($A$2:A2,$A$2:A2)=1)*(COUNTIF($A$2:$A$100,$A$2:A2)>1)))

For example, if A2:A8 contains Apple, Pear, Apple, Pear, Plum, Banana, Banana, the duplicated groups receive 1 for Apple, 2 for Pear, and 3 for Banana. Plum is not duplicated, so its helper cell is blank. This formula uses COUNTIF to identify values occurring at least twice and SUMPRODUCT to count the first appearances of duplicated values up to the current row.

Next, select A2:A100 and create a formula-based conditional-formatting rule. Because the formatting applies to column A, the formula checks the corresponding helper cell in column B. For the first of four color groups, use:

=AND($A2<>"",$B2<>"",MOD($B2-1,4)=0)

Choose a fill, then add three more rules using MOD($B2-1,4)=1, MOD($B2-1,4)=2, and MOD($B2-1,4)=3, each with a different fill. Keep the rest of each formula the same. These four rules color every occurrence in each group consistently. If you need more colors, add more rules and change the divisor from 4 to the number of colors used.

Hide column B if you do not want the helper numbers visible. Keep in mind that the helper formula’s assigned group numbers depend on first-appearance order: inserting or rearranging data can change which group gets which number and therefore which color. The colors are for visual comparison, not permanent labels.

Choose a Method That Fits the Data

  • Use the built-in Duplicate Values rule when one highlight color is enough and you want a quick check.
  • Use one formula rule per value when there are only a few known groups and each needs a deliberately chosen color.
  • Use a helper column and color rules when there are many groups and a repeating palette is acceptable.
  • Use a separate review list or table when you need a durable, explicit mapping from values to named categories or colors. Conditional formatting is not a substitute for documenting what each color means.

“Unique colors” can mean either one color per duplicated group or one color for every repeated cell. The methods above use one color per value group, so both Apple cells match each other. Giving every individual cell a different color would not show which entries belong together and is rarely useful for spotting duplicates.

What Counts as a Duplicate?

Excel’s duplicate rule and the COUNTIF formulas above compare cell values without distinguishing uppercase and lowercase. Thus, “apple” and “Apple” are normally treated as the same text for this purpose. If case matters, the built-in duplicate rule is not a case-sensitive solution; a different formula approach using case-sensitive comparison is needed.

Also check for differences that are hard to see. “Pear” and “Pear ” with a trailing space are different text strings, while cells containing formulas may display the same result even if their underlying values differ. If apparently identical entries are not being grouped, inspect the formula bar, check for leading or trailing spaces, and confirm that values are genuinely the same. Text imported from other systems may need cleaning before highlighting is dependable.

The helper formula deliberately excludes truly blank cells. A cell containing a formula that returns an empty string can behave differently from an empty cell in some comparisons, so verify the result if your range contains formulas. COUNTIF also treats wildcard characters such as an asterisk or question mark specially in criteria. If those characters occur as literal text in your data, test the results carefully before relying on a COUNTIF-based rule.

Common Problems and Fixes

  • Every duplicate has the same color: That is expected from the built-in Duplicate Values command. Add separate formula rules or use the helper-column method for group colors.
  • No cells are highlighted: Check that the rule applies to the intended range and that the formula’s row reference matches its first row. A rule applied starting at A2 should normally use A2 as its relative row reference.
  • Only some repeats match: Look for spaces, inconsistent data types, or formulas returning different underlying results. Sorting or filtering may help inspect the entries, but does not itself correct them.
  • Colors change after adding rows: Make sure the rule’s “Applies to” range includes the new rows. For regularly growing data, converting the range to an Excel table can make it easier to manage, though you should verify that the conditional-formatting rule extends as intended.
  • The sheet becomes hard to read: Use a restrained palette with adequate contrast, and avoid using color as the only way to communicate meaning. Keep the helper values or add a label if other people need to interpret the groups.

Excel for Mac and Excel for the Web

In current Excel for Mac, conditional formatting is also available from the Home tab, and the built-in Duplicate Values option and formula-based rules are available. The precise dialog layout can vary by release, but the main logic is the same. Excel for the web also supports conditional formatting, though some rule-editing options and dialogs can differ from the Windows desktop app. If a complex rule is difficult to create or edit in the web version, open the workbook in desktop Excel.

The formulas shown use ordinary worksheet functions available in Microsoft 365 Excel. They do not require VBA or a macro. Because conditional formatting and formulas recalculate when source values change, the colors can update without running a separate command.

Frequently Asked Questions

Can Excel automatically give every duplicate a different color?
The built-in Duplicate Values rule cannot; it uses one format for the selected range. Formula rules or a helper column can assign colors by duplicated value, but a finite palette repeats when there are more groups than colors.

Can I highlight duplicates in two columns together?
Yes. Select the range you want Excel to evaluate and use the built-in duplicate rule, or adapt a formula rule to the relevant columns. Be clear whether a duplicate means a repeated value in either column or a repeated combination of values across both columns; those are different checks.

Does conditional formatting delete duplicate values?
No. It only changes cell appearance. To remove duplicate records, use Data > Remove Duplicates after checking the selected columns and keeping a backup if the original data matters.

Why are duplicates with different capitalization highlighted together?
COUNTIF and Excel’s standard duplicate highlighting are not case-sensitive. Text that differs only by capitalization is generally treated as a match.

Will the colors update when I edit a cell?
Yes, conditional-formatting rules recalculate when their referenced values change. A helper formula also recalculates, but its group numbers—and therefore color assignments—can shift when values are inserted, deleted, or reordered.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.