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 6 easy ways to learn about how to highlight duplicates in Excel.

## Download Practice Workbook

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

**6 Easy Ways for How to Highlight Duplicates in Excel**

We will be using the following dataset to explain the 6 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 in Excel Using Built-in Function of Conditional Formatting**

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**Hom**e 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.

Now, if you want to highlight in a way so that adding duplicate values in the future will highlight them too.

Every step will be similar to the above ones except for one thing. Here you have to select the whole single column. To do this simply click on the column header. We have clicked on **column B**.

Follow the rest of the steps given above and the result for this is shown below.

You can see that all the duplicates are highlighted. Now if you add further data in the column you will see the following things.

For duplicate values, it highlights the cell and for the non-duplicate value, it does not highlight. So, if you want to make a column dynamic for future use you can do this using this method.

**Read more:** **How to Highlight Duplicates in Two Columns 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 occurrences. We will be seeing the occurrences without the 1st one, only the second one or 3rd time. In addition, I would like to mention by observing the results for the 3 types you can easily learn to modify the formula to get other occurrence-based results.

Let us divide the 3 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:**

- Select

`=COUNTIF($B$5:$B5,$B5)>1`

- 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:**

- Select

`=COUNTIF($B$5:$B5,$B5)=2`

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

**2.3 Values Occurring 3rd Time**

To highlight the 3rd occurrence and above we have to follow the steps which are similar to the above two. The only difference is the formula here.

For your ease, the steps are as follows:

**Steps:**

- You have to select the dataset.

- Select
**New Rule**from the**Conditional Formatting**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:**

- Select

`=COUNTIF($B$5:$B5,$B5)=3`

- Click
**OK**.

Follow the picture for ease of understanding.

- Clicking on
**Format**will take you to a new box where you can choose the color of your choice. We have used a light blue to highlight the 3rd occurrence.

- Clicking
**OK**in the first box will show the result.

There is only 1 cell containing a value that occurred 3rd time in the dataset.

All the results are shown together below.

Moreover, modifying the formula with few operators and numbers will help you to see varieties of occurrences in a dataset.

**3. Highlight Duplicates in Multiple Columns in Excel**

Moving forward to duplicates in multiple columns. To highlight duplicates in multiple columns you can use method 1. However, you can do this using formula also.

Below are the steps to highlight duplicates in multiple columns.

**Steps:**

- Â Select the columns in the dataset.

- You have to select
**New Rules**from the**Conditional Formatting**. You can find it 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:**

- Select

`=COUNTIF(B$5:$B$14,B5)+COUNTIF(C$5:C5,C5)>1`

- Click
**OK**.

Follow the picture for ease of understanding.

- In the
**Format Cells**, we have chosen light orange and then clicked**OK**. Anyway, you can choose any other color.

- After selecting a color come back to the first box and click
**OK**there.

You can notice the duplicates in the columns are highlighted with a light orange color.

**4. Highlight Entire Rows Based on Duplicates in a Column**

Furthermore, you can also highlight duplicates entire row in a column.

We have divided this into two-part based on cases including or excluding 1st occurrence.

**4.1 Duplicates without 1st Occurrence**

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:**

- Select

`=COUNTIF($B$6:$B6, $B6)>1`

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

**4.2 Duplicates with 1st Occurrence**

For highlighting the duplicates with 1st occurrence in the entire row of a column we have to follow the steps.

**Steps:**

- Select the dataset.

- Go to
**New Rule**from 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:**

- Select

`=COUNTIF($E$6:$E$15, $E6)>1`

- Click
**OK**.

- Afterward, choose a color using which you want to highlight the duplicates in the entire row. We have used light orange here.
- Click
**OK**.

- In the Edit Formatting Cells box, click
**OK**.

The result is shown below.

Here you can notice all the cells are colored. It does not matter whether the column values besides the column we have used contains duplicates or not. Since all the selected column data contains duplicates, it highlights the entire row.

Both the result is shown in the picture below.

**Read more:** **How to Highlight Duplicate Rows in Excel**

**5. Highlight Consecutive Duplicate Cells in Excel**

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

**5.1 Duplicates without 1st Occurrence**

**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:**

- Select

`=$D5=$D4`

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

**5.2 Duplicates with 1st Occurrence**

For the consecutive duplicated cells with 1st occurrence, you have to follow the following steps.

**Steps:**

- Select the data for showing consecutive cells with duplicates.

- From the
**Home tab,**choose**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:**

- Select

`=OR($D5=$D4, $D5=$D6)`

- Click
**OK**.

- Select the orange color and click
**OK**.

- If everything seems like the picture below in the first box, then click
**OK**.

The result will highlight the consecutive duplicate cells.

See the results for both cases below.

**6. 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 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**Insert**tab.

A **general window** will open.

- Write the following
**code**in the**general window**.

**Code:**

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

**Things to Remember**

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

## Conclusion

The article explains 6 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 for most of the 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.