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.
- 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.
- 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.
Then you will get all the duplicates for the region of the UK highlighted with your selected color.
Now let’s highlight the duplicates for another Region– London.
- 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.
Duplicates of the London region are now highlighted with a different color.
- 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.
- How to Highlight Duplicates but Keep One in Excel (4 Methods)
- Highlight Duplicates across Multiple Worksheets in Excel (3 Formulas)
- How to Highlight Duplicates in Two 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.
- Right-click on the sheet title to open the VBA window.
- 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.
- 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.
You will get a practice section in the Excel file given above to practice the explained methods.
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.