How to Highlight Duplicates in Excel (6 Easy Ways)

how to highlight duplicates in excel

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.

Sample Dataset: 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.

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

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

Highlight Duplicates in Excel Using 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.

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

Output: Highlight Duplicates in Excel Using Built-in Function of Conditional Formatting

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.

Highlight Duplicates Based on Occurrence Using New Rule of Conditional Formatting

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

Highlight Duplicates in Excel Using 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.

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

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

Output: Highlight Duplicates Based on Occurrence Using New Rule of Conditional Formatting


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.

Highlight Duplicates in Excel Using 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.

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

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.

Highlight Duplicates in Excel Using Conditional Formatting: New Rule

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

Highlight Duplicates in Excel Using Conditional Formatting: Fill Color

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

Highlight Duplicates in Multiple Columns in Excel

  • You have to select New Rules from the Conditional Formatting. You can find it in the Home tab.

Highlight Duplicates in Excel Using 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:$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.

Highlight Duplicates in Excel Using Conditional Formatting: Fill 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.

Output: Highlight Duplicates in Multiple Columns in Excel


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.

Highlight Entire Rows Based on Duplicates in a Column: Duplicates without 1st Occurrence

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

Output: Highlight Entire Rows Based on Duplicates in a Column- Duplicates without 1st Occurrence


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.

Highlight Entire Rows Based on Duplicates in a Column: Duplicates with 1st Occurrence

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

Output: Highlight Entire Rows Based on Duplicates in a Column: Duplicates with 1st Occurrence

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.

Highlight Entire Rows Based on Duplicates in a Column: Duplicates with and without 1st Occurrence

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.

Highlight Consecutive Duplicate Cells in Excel: without 1st Occurrence

  • 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:
=$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.

Output: Highlight Consecutive Duplicate Cells in Excel- 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:
=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.

VBA Macros to Highlight Duplicates in Excel

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

Output: VBA Macros to Highlight Duplicates in Excel


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.


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

ExcelDemy
Logo