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.

How to Highlight Duplicates in Excel


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.

Dataset

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.

select dataset how to highlight duplicates in excel

  • 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.

Conditional formatting

  • 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.

duplicate values

  • Then, click OK.

The result will look like the below picture.

result how to highlight duplicates in excel


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.

without 1st occurrence

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

Conditional Formatting

  • 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:
=COUNTIF($B$5:$B5,$B5)>1
  • Click OK.

You can also follow the picture numbers and do accordingly.

new formatting rules how to highlight duplicates in excel

  • 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.

Highlight Duplicates in Excel Using Conditional Formatting: Fill color

  • Finally, you have to click OK.

new formatting rules how to highlight duplicates in excel

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

result


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.

dataset for 2nd occurrence

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

conditional formatting

  • 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:
=COUNTIF($B$5:$B5,$B5)=2
  • Click OK.

You can also follow the picture below for this step.

new formatting rules how to highlight duplicates in excel

  • 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.

Highlight Duplicates in Excel Using Conditional Formatting: Fill Color

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

how to highlight duplicates in excel

The result highlights values that occurred 2nd time only.

output

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.

duplicate in entire row

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

conditional formatting

  • 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:
=COUNTIF($B$6:$B6, $B6)>1
  • Click OK.

Follow the picture.

new formatting rules how to highlight duplicates in excel

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

format cells how to highlight duplicates in excel

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

new formatting rules how to highlight duplicates in excel

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

result how to highlight duplicates in excel

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.

dataset how to highlight duplicates in excel

  • Click New Rule in the Conditional Formatting.

conditional formatting how to highlight duplicates in excel

  • 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:
=$D5=$D4
  • Click OK.

Edit formatting rules

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

Format cells

  • Finally, click OK in the first box.

Formula how to highlight duplicates in excel

The result highlights consecutive duplicate cells without 1st occurrence.

how to highlight duplicates in excel

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.

VBA

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

VBA

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.

Duplicate code

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

VBA result

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

Syeda Fahima Nazreen

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo