How to highlight duplicate values in excel with different colors

Answer

Highlighting duplicates with different colors is a method that assigns a unique color to each repeated value so the duplicates stand out visually across the dataset. To highlight duplicates with multiple colors in Excel, a built-in rule is not enough because Excel applies one color per rule. A conditional formatting rule identifies duplicates, and a helper feature uses a formula to group values so each duplicate group receives a different color.

Method 1: Highlight duplicates with different colors using Conditional Formatting + Advanced Filter (Windows and Mac)

Step 1: Extract unique duplicate groups

1. Select the column that contains values.

2. Go to Data.

3. Select Advanced under the Sort & Filter group.

4. Choose Copy to another location.

5. Set List range to your data.

6. Leave Criteria range empty.

7. Set Copy to to an empty cell.

8. Tick Unique records only.

9. Press OK.

This generates a list of unique values that appear in your original column.

Step 2: Assign each unique value a color

1. Select the list of extracted unique values.

2. Go to Home → Fill Color.

3. Assign a different color to every value manually.
   Excel uses 60 standard theme colors and 12 accent variants with 100% brightness, which gives more than 72 color choices.

Step 3: Apply Conditional Formatting for each colored value

1. Select the original dataset that contains duplicates.

2. Go to Home → Conditional Formatting → New Rule.

3. Select Use a formula to determine which cells to format.

4. Enter a formula that checks for a specific value, for example:

   =A1="Product A"

   Change the value to match the first item from your extracted list.

5. Select Format → Fill, choose the same color used in Step 2.

6. Press OK.

7. Repeat steps 1–6 for every remaining value from your extracted list, assigning its matching color.

This method highlights every duplicate group uniquely.

Windows vs. Mac differences

• Windows: The Advanced Filter appears under Data → Sort & Filter → Advanced.

• Mac: The Advanced Filter appears under Data → Filter → Advanced Filter.
  Both platforms support the same rule types for Conditional Formatting.

How to highlight duplicates automatically with a VBA one-click macro

A VBA macro applies a different color automatically for every duplicate group without manual rules.

Steps

1. Press Alt + F11 (Windows) or Option + F11 (Mac) to open the VBA editor.

2. Select Insert → Module.

3. Paste the code below:

   Sub ColorDuplicatesByGroup()

       Dim rng As Range, cell As Range

       Dim dict As Object, clrDict As Object

       Dim nextColor As Long

       

       Set rng = Selection

       Set dict = CreateObject("Scripting.Dictionary")

       Set clrDict = CreateObject("Scripting.Dictionary")

       

       nextColor = 3

       

       For Each cell In rng

           If Not dict.exists(cell.Value) Then

               dict.Add cell.Value, 1

               clrDict.Add cell.Value, nextColor

               nextColor = nextColor + 1

               If nextColor > 56 Then nextColor = 3

           End If

       Next cell

       

       For Each cell In rng

           cell.Interior.ColorIndex = clrDict(cell.Value)

       Next cell

   End Sub

Usage

1. Select the range that contains duplicates.

2. Run the macro: Developer → Macros → ColorDuplicatesByGroup → Run.

Excel uses ColorIndex values 3 to 56, which gives over 50 distinct color options.

When to use each method

• Manual color assignment: Needed when full control over every color is required.

• VBA macro: Best for datasets with large volumes or when an automated workflow is required.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report