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.


Download Practice Workbook

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


2 Ways to Highlight Duplicates in Excel with Different Colors

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 nameLondon 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 (4 Ways)


Similar Readings


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.


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

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

10 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/).

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo