How to Highlight Duplicates in Excel (5 Easy Ways)

You might work with a lot of data that contains duplicates. You might want to highlight the duplicates. Excel has some easy and useful ways to highlight duplicates. The article will explain 5 easy ways to learn about how to highlight duplicates in Excel.

This is the overview of this article.

Download Practice Workbook

Please download the following sample workbook so that you can practice along with it.

5 Easy Ways to Highlight Duplicates in Excel

We will be using the following dataset to explain the 5 ways on how to highlight duplicates in Excel.

The dataset contains the name and ID of the products of a company. The dataset contains duplicates in both columns. We will be using the dataset and highlighting the duplicates.

1. Highlight Duplicates Using Built-in Function of Conditional Formatting in Excel

Excel has a built-in function using which duplicates can be easily highlighted.

You have to follow the steps below for this:

Steps:

• Select the dataset where you want to find and highlight duplicates.

• From the Conditional Formatting option in the Home tab, select Highlight Cells Rules.
• This will open the drop-down menu. From there, choose Duplicate Values.

• A small box will come up. There select Duplicates from left drop-down options. Then select the color of your choice using which you want to highlight the duplicates. For this case, we have chosen Light Red Fill with Dark Red Text.

• Then, click OK.

The result will look like the below picture.

2. Highlight Duplicates Based on Occurrence Using New Rule of Conditional Formatting

However, you can also highlight duplicates based on the time of occurrence. We will be seeing the occurrences without the 1st one and the second time. In addition, I would like to mention by observing the results for the 2 types you can easily learn to modify the formula to get other occurrence-based results.

Let us divide the 2 types and see them one by one.

2.1 Duplicates without 1st Occurrence

Follow the steps to highlight duplicates without the 1st occurrence.

Steps:

• Select the data.

• Go to Conditional Formatting in the Home tab. For this method, you have to select New Rule from the drop-down menu.

• A box will appear. Follow the steps for the box that appeared.
  • Select Use a formula to determine which cells to format in the Select a Rule Type:
  • Write the formula in the Format values where this formula is true:

• Click OK.

You can also follow the picture numbers and do accordingly.

• For selecting the highlighting color follow the picture below. You can select any color of your choice in the 1. No part is shown in the picture. We have chosen light golden color.

• Finally, you have to click OK.

You can observe that the result shows duplicates without the 1st occurrence.

2.2 2nd Occurrence of Duplicates Only

For highlighting the data only 2nd time in the dataset you can follow the steps below.

Steps:

• First, you have to select the data.

• Next, you have to select New Rule from the Conditional Formatting drop-down menu in the Home tab.

• A box will appear. Follow the steps for the box that appeared.
  • Select Use a formula to determine which cells to format in the Select a Rule Type:
  • Write the formula in the Format values where this formula is true:

• Click OK.

You can also follow the picture below for this step.

• After that, in Format cells, choose the color you want and click OK. In this case, we chose an orange color to highlight the 2nd occurrence of values.

• Then, you have to click OK in the first box.

The result highlights values that occurred 2nd time only.

Read More: How to Highlight Duplicates but Keep One in Excel (4 Methods)

3. Highlight Entire Rows Based on Duplicates in a Column

Furthermore, you can also highlight duplicates in entire rows in a column.
You have to follow a few steps highlighting duplicates in the entire row in a column.

Steps:

• Select the dataset.

• Then, click on New Rule from Conditional Formatting of the Home tab.

• A box will appear. Follow the steps for the box that appeared.
  • Select Use a formula to determine which cells to format in the Select a Rule Type:
  • Write the formula in the Format values where this formula is true:

• Click OK.

Follow the picture.

• Choose the color you want and click OK. For this, we chose the orange color.

• Click OK in the previous box after the above steps are done.

The result highlights the duplicates in a column for the entire row in orange.

Read More: How to Highlight Duplicates in Two Columns in Excel (2 Examples)

4. Highlight Consecutive Duplicate Cells in Excel

You might want to highlight consecutive duplicates. For this, you have to follow the steps below.

Steps:

• Select the data.

• Click New Rule in the Conditional Formatting.

• Follow the steps for the box that appeared.
  • Select Use a formula to determine which cells to format in the Select a Rule Type:
  • Write the formula in the Format values where this formula is true:

• Click OK.

• You can choose any color from the new appeared box. We chose light orange.
• Then, click OK.

• Finally, click OK in the first box.

The result highlights consecutive duplicate cells without 1st occurrence.

Read More: How to Highlight Duplicates in Two Columns Using Excel Formula

5. VBA Macros to Highlight Duplicates in Excel

Moreover, you can use VBA macro code for a dynamic result.
You have to follow the steps to apply the VBA macro code to highlight duplicates.

Steps:

• Select the dataset.

• Press ALT+F11 from your keyboard.
• VBA macro window will open. There select your worksheet.
• Select Module from the Insert tab.

A general window will open.

• Write the following code in the general window.

Option Explicit
Sub ColorDuplicates()
Dim xRange As Range
Dim xText As String
Dim xCell As Range
Dim xChar As String
Dim xCellPre As Range
Dim xCIndex As Long
Dim xCol As Collection
Dim I As Long
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
xText = ActiveWindow.RangeSelection.AddressLocal
Else
xText = ActiveSheet.UsedRange.AddressLocal
End If

Set xRange = Application.InputBox("please select the data range:", "Color Duplicates", xText, , , , , 8)
If xRange Is Nothing Then Exit Sub
xCIndex = 2
Set xCol = New Collection
For Each xCell In xRg
On Error Resume Next
xCol.Add xCell, xCell.Text

If Err.Number = 457 Then
xCIndex = xCIndex + 1
Set xCellPre = xCol(xCell.Text)

If xCellPre.Interior.ColorIndex = xlNone Then xCellPre.Interior.ColorIndex = xCIndex
xCell.Interior.ColorIndex = xCellPre.Interior.ColorIndex

ElseIf Err.Number = 9 Then
MsgBox "Too many duplicate companies!", vbCritical, "Color Duplicates"
Exit Sub
End If
On Error GoTo 0
Next
End Sub

• After that, press F5 from your keyboard.
• A small box will appear. Check the range of your dataset and click OK.

The result will show the same color for duplicates of the same value.

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

Things to Remember

You need to follow all the steps carefully and make the necessary changes in the cell references to get the desired result.

Conclusion

The article explains 5 different easy ways to explore how to highlight duplicates in Excel. The ways use Excel formula with the COUNTIF function. On the other hand, it uses Conditional Formatting from the Home tab in most ways. It also uses VBA code to create a value-wise color for duplicates. I hope the article was helpful and informative to you. For any further queries, write in the comment section.

Related Articles

• How to Highlight Duplicates in Multiple Columns in Excel (4 Ways)
• [Fix:] Highlight Duplicates in Excel Not Working
• Highlight Cells If There Are More Than 3 Duplicates in Excel (3 Examples)
• How to Highlight Duplicate Rows in Excel (3 Quick Ways)
• Highlight Duplicates across Multiple Worksheets in Excel (3 Formulas)