How to Highlight Duplicates in Excel with Different Colors (2 Ways)

Sometimes we need to find duplicates and mark them with different colors for many purposes. Also, it helps to analyze data. Excel has some useful features to do it. In this article, I’ll show two quick and useful methods to highlight duplicates in Excel with different colors with easy steps, and clear screenshots.


Highlight Duplicates in Excel with Different Colors: 2 Ways

To explore the methods, we’ll use the following dataset that represents some salesperson’s Sales in different Regions. Have a look that some region names are repeated, we’ll highlight those duplicate regions with different colors.


1. Apply Conditional Formatting to Highlight Duplicates in Excel with Different Colors

First, we’ll learn how to use Conditional Formatting for highlighting duplicates with different colors. Conditional Formatting has many customizations to highlight data. But the limitation is that we’ll have to use separate Conditional Formatting for every different duplicate to get different colors.

Steps:

  • Select the data range where you want to apply Conditional Formatting. I selected C5:C11.
  • Then click as follows: Home > Conditional Formatting > Highlight Cells Rules > Equal To.

Soon after, a Conditional Formatting dialog box named Equal To will open up.

Apply Conditional Formatting to Highlight Duplicates in Excel with Different Colors

  • Type the name of the Region for what you are looking for duplicates. I typed UK in the Format cells that are EQUAL TO:
  • Then click on the drop-down icon from the right side of the dialog box and select your desired color.
  • Finally, just press OK.

Apply Conditional Formatting to Highlight Duplicates in Excel with Different Colors

Then you will get all the duplicates for the region of the UK highlighted with your selected color.

Apply Conditional Formatting to Highlight Duplicates in Excel with Different Colors

Now let’s highlight the duplicates for another RegionLondon.

  • Follow the first step to open the Conditional Formatting dialog box.
  • Type the region name- London in the Format cells that are EQUAL TO:
  • Later, select your desired color from the drop-down list.
  • And then press OK.

Apply Conditional Formatting to Highlight Duplicates in Excel with Different Colors

Duplicates of the London region are now highlighted with a different color.

Apply Conditional Formatting to Highlight Duplicates in Excel with Different Colors

  • To highlight the duplicates for the Canada region, again follow the first step to open the Conditional Formatting dialog box.
  • Write Canada in the Format cells that are EQUAL TO: box and select another color from the drop-down list.
  • Finally, just press OK.

Now, all different duplicate Regions are highlighted with different colors.

Read More: How to Highlight Duplicates in Multiple Columns in Excel


2. Embed Excel VBA to Highlight Duplicates with Different Colors

Using VBA macros is quicker than the first method. Because VBA will help you to highlight all different duplicates with different colors at a time.

Steps:

  • Right-click on the sheet title to open the VBA window.

Embed Excel VBA to Highlight Duplicates with Different Colors

  • After that type the following codes in the VBA window.
Sub Duplicates_Dif_Colors()
    Dim RG As Range
    Dim TT As String
    Dim CL As Range
    Dim CR As String
    Dim CP As Range
    Dim CD As Long
    Dim Cltn As Collection
    Dim J As Long
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      TT = ActiveWindow.RangeSelection.AddressLocal
    Else
      TT = ActiveSheet.UsedRange.AddressLocal
    End If
    Set RG = Application.InputBox("Select the range of data:", "Duplicates with Colors", TT, , , , , 8)
    If RG Is Nothing Then Exit Sub
    CD = 2
    Set Cltn = New Collection
    For Each CL In RG
      On Error Resume Next
      Cltn.Add CL, CL.Text
      If Err.Number = 457 Then
        CD = CD + 1
        Set CP = Cltn(CL.Text)
        If CP.Interior.ColorIndex = xlNone Then CP.Interior.ColorIndex = CD
        CL.Interior.ColorIndex = CP.Interior.ColorIndex
      ElseIf Err.Number = 9 Then
        MsgBox "Found excessive duplicates", vbCritical, "Duplicates with Colors"
        Exit Sub
      End If
      On Error GoTo 0
    Next
End Sub
  • Then click on the Run icon to run the codes.

Soon after, an lnputBox will pop up to select the data range.

Embed Excel VBA to Highlight Duplicates with Different Colors

  • Select the data range C5:C11 by dragging it with your mouse.
  • Later, just press OK.

Now see that Excel has highlighted all the duplicates with different fill colors.

Read More: [Fix:] Highlight Duplicates in Excel Not Working


Practice Section

You will get a practice section in the Excel file given above to practice the explained methods.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


Conclusion

I hope the procedures described above will be good enough to highlight duplicates in Excel with different colors. Feel free to ask any question in the comment section and please give me feedback.


Related Articles


<< Go Back to Highlight Duplicates in Excel | Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

15 Comments
  1. Hi, thanks for the instruction. I have applied the VBA for my worksheet. I have more than 100 duplicates and the macro does not cover all the duplicate values. How can I modify the code? Thanks a lot.

    • Hello, DANDELION!
      Please select the range properly, this macro also works for more than 100 duplicates. There is no limitation. All you need to do is after running the code select the range properly.

      Good Luck!

      Regards,
      Sabrina Ayon
      Author, ExcelDemy.

  2. I am working with six columns separated by other data, as follows A1 and A2 merged data related is on cells b2, and b3, then it repeats for C1 and C2 merged, data related on d2 and d3, and so on. It repeats six times to the left and then six times down. The duplicate values I need to check is on the merged cells, for each duplicate value that is different use another color, if there is no duplicate no color . Any advise?

    • Reply Avatar photo
      Nazmul Hossain Shovon Dec 4, 2022 at 2:23 PM

      Hi Maikel,
      Can you share your Excel file with us, kindly? So that we may have a look at it and give you some suggestions.

  3. Is there a way to modify the code so that you can choose the colors?? or choose a color pallette?

    • Reply Avatar photo
      Fahim Shahriyar Dipto Dec 20, 2022 at 5:21 PM

      Hi Anna,
      Thanks for commenting. There are 13 commands under the Interior application. When you put a dot (.) after the Interior application you will find the commands. there are Color, Colorindex, Pattern, ThemeColor, etc in the command section. But all the commands have the built-in color code that’s why you won’t be able to choose a specific color using this code. but while you working with blanks you can insert the RGB command and the Custom Color Code. In our case, it is not possible as we have to maintain the same color for the duplicates. Hope, you understand our answer.
      Regards,
      Fahim Shahriyar Dipto
      Excel & VBA Content Developer.

  4. Is there any way to change the color scheme? The ones in this are too dark.

    • Hi Carrie,
      Greetings. Thanks for commenting. Yes, you can change the color scheme by following the below process.
      Select the data range where you want to apply Conditional Formatting. Then click as follows: Home > Conditional Formatting > Highlight Cells Rules > Equal To.
      Soon after, a Conditional Formatting dialog box named Equal To will open up
      Type the name of the Region for what you are looking for duplicates.
      Then click on the drop-down icon from the right side of the dialog box and select Custom Format.
      Therefore, the Format Cells window will appear, and select the Fill option. Then choose your desired color.
      Finally, just press OK

  5. The code is only working for the first 109 rows out of a document that contains 6000 can you point me in the right direction to get this working fully?

    • Hi Keaton, thanks for your query. As the code has no limitation on the number of rows, it should work in your case. Maybe the code worked for the first 109 rows only because you only selected the first 109 rows in the prompt. Kindly select the entire dataset while running the code. Hopefully, it will do the job for you. If the code still doesn’t work, you can share your file using our Exceldemy forum(https://exceldemy.com/forum/).

  6. Hi there,

    For the VBA code, cells with blank entries are also colour-coded. How may I modify the code to avoid this issue from happening?

  7. Hi there, for the VBA code,

    In my own workbook, cells with blank entries are also color-coded. Please advise on how i can modify the code so as to prevent cells with blank entries to be left with no color fill?

    • This code will solve your problem. The code has a condition checking values blank or not.

      Sub Duplicates_Dif_Colors()
      Dim RG As Range
      Dim TT As String
      Dim CL As Range
      Dim CR As String
      Dim CP As Range
      Dim CD As Long
      Dim Cltn As Collection
      Dim J As Long
      
      On Error Resume Next
      
      If ActiveWindow.RangeSelection.Count > 1 Then
      TT = ActiveWindow.RangeSelection.AddressLocal
      Else
      TT = ActiveSheet.UsedRange.AddressLocal
      End If
      
      Set RG = Application.InputBox("Select range of data with duplicates:", "Duplicate values with Colors", TT, , , , , 8)
      If RG Is Nothing Then Exit Sub
      
      CD = 2
      Set Cltn = New Collection
      
      For Each CL In RG
      If CL.Value <> "" Then ' check if cell is not blank
      On Error Resume Next
      Cltn.Add CL, CL.Text
      If Err.Number = 457 Then
      CD = CD + 1
      Set CP = Cltn(CL.Text)
      If CP.Interior.ColorIndex = xlNone Then CP.Interior.ColorIndex = CD
      CL.Interior.ColorIndex = CP.Interior.ColorIndex
      ElseIf Err.Number = 9 Then
      MsgBox "Found excessive duplicates", vbCritical, "Duplicates with Colors"
      Exit Sub
      End If
      On Error GoTo 0
      End If
      Next
      End Sub
      

      Output:

      Blank cells not colored

  8. Thanks so much for the VBA. It was really helpful 🙂

    Margaret (from the Philippines

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo