Looking for ways to find and highlight duplicates in Excel? Do not worry, we are here for you. In this article, we will describe **3** easy ways to do the task effortlessly. Finding and highlighting duplicates in Excel can be very crucial sometimes. When you are dealing with a large dataset, and have a bulk amount of data, then highlighting duplicates might seem complicated. However, it is rather very easy. This article will describe the task with easy examples and handy methods. So, let’s dive in!

In the following image, you can see the overview of finding and highlighting duplicates in Excel.

## Download Practice Workbook

You can download the Excel file from the link below and practice the explained methods.

## Why Do You Need to Highlight Duplicate Values in Excel?

Below we are providing the importance of finding and highlighting duplicates in Excel.

**Making Data Accurate:**When there are multiple duplicates of the same data, it can cause mistakes and wrong conclusions. To make sure the information is correct and trustworthy, it’s important to identify and delete any duplicates.**Saving Time:**If you have a lot of information that has duplicates, finding and pointing out those duplicates can help you save time. This way, you can quickly figure out the special values and concentrate on studying them.**Getting Rid of Errors:**In some cases, duplicates can lead to errors in calculations or data analysis. By identifying and removing duplicates, you can avoid these errors.**Managing Data:**Making sure your data is tidy and well-ordered is crucial for managing it effectively. Eliminating copies can aid in keeping your data set organized and efficient.

Overall, It’s essential to identify and mark duplicates in Excel to maintain accurate data, save time, prevent mistakes, and manage your data efficiently.

## Find and Highlight Duplicates in Excel: 3 Ways

The following dataset has the **Purchase Date**, **Item**, and** Cost **columns. Here, the **Item** columns have numerous duplicates. Using this dataset, we will** find duplicates** and highlight duplicates in Excel.

Here, we used **Excel 365**. You can use any available Excel version.

### 1. Finding and Highlighting Duplicates of a Single Column in Excel

Here, we will show how you can find and highlight duplicates of a single column in Excel. We will use **Conditional Formatting** for this.

#### 1.1 Using Conditional Formatting

Here, we will highlight duplicates with first occurrences.

- First of all, we will select the cells where you need to
**find duplicates**. - Here, we selected the entire
**Item**column >> go to the**Home** - Then, from the
**Conditional Formatting**group >> select**Highlight Cells Rule**. - Select
**Duplicate Values**.

At this point, a **Duplicate Values** dialog box will appear.

- Select
**Duplicate**>> Select a color type. - Here, we selected
**Green Fill with Dark Green Text**. - Then, click
**OK**.

Therefore, you can see the highlighted Duplicates with light green color.

**Note:**We can select more than one column to highlight the duplicate rows. But there is a little technical problem with this selection. We should be aware of this problem. For example, if a text appears under one column and in another column, Excel will highlight this text as a duplicate though this text is not actually a duplicate text.

**Tip:**It may happen that our table contains huge data. And we want to see the duplicate rows at the top of the column. Excel provides the technique to sort the cell based on colors. In our example, click the table header “Name”, it will show some options: Sort A to Z, Sort Z to A, Sort by Color. Select Sort by Color and then Filter by Cell Color. Your output will look like the following image.

**Read More:** **How to Find Duplicate Values in Excel Using Formula (9 Methods)**

#### 1.2 Find and Highlight Duplicate Rows Without 1st Occurrences

Here, we will find and highlight duplicates in Excel without the first occurrences.

- First of all, we will select the entire
**Item**column >> go to the**Home** - Then, from the
**Conditional Formatting**group >> select**New Rule**.

At this point, a New Formatting Rule dialog box will appear.

- Select
**Use a formula to determine which cells to format**. - Then, write the following formula in the formula box.

`=COUNTIF($C$5:$C5,$C5)>1`

- Then, click on
**Format**.

Then, a **Format Cells** dialog box will appear.

- From
**Fill**>> select a Color >> click**OK**.

Then, in the **New Formatting Rule **dialog box you can see the** Preview **of the Color >> click **OK**.

Therefore, you can see the highlighted duplicates in a single column excluding the first occurrences

**Read More:** **How to Compare Rows for Duplicates in Excel (3 Easy Ways)**

### 2. Finding and Highlighting Duplicates in a Range of Multiple Columns in Excel

In the following dataset, you can see that we have the **Customer Name**,** Items Purchased in January**, and** Items Purchased in February** columns.

Here, both the **Items Purchased in January** and** Items Purchased in February** columns have several duplicates.

Now, using this dataset, we will find and highlight duplicates in a range of multiple columns in Excel.

#### 2.1 Highlighting Duplicates Including 1st Occurrences

Here, we will highlight duplicates in multiple columns including 1st occurrences. We will use **the COUNTIF function** for this.

- In the first place, we will select the entire
**Items Purchased in January**and**Items Purchased in February**columns excluding the column headings. - Then, we will follow the
**Steps described in Method 1.2**to bring out the**New Formatting Rule**dialog box and to bring out the formula box. - Type the following formula in the formula box.

`=COUNTIF($C$5:$D$20, C5)>1`

- After that, click on
**Format**and format the cells according to your need. - Click
**OK**.

Therefore, we have found and highlighted duplicates in multiple columns including first occurrences.

**Read More:** **Excel VBA to Find Duplicate Values in Range (7 Examples)**

#### 2.2 Highlighting Duplicates Without 1st Occurrences

Here, we will highlight duplicates in multiple columns excluding 1st occurrences.we will use the combination of **IF** and **COUNTIF** functions for this.

- In the beginning, we will select the entire
**Items Purchased in January**and**Items Purchased in February**columns excluding the column headings. - Then, we will follow the
**Steps described in Method 1.2**to bring out the**New Formatting Rule**dialog box and to bring out the formula box. - Type the following formula in the formula box.

`=IF(COLUMNS($B5:B5)>1,COUNTIF(A$5:$B$20,B5),0)+COUNTIF(B$5:B5,B5)>1`

- Afterward, click on
**Format**and format the cells according to your need >> click**OK**.

highlighting duplicates in multiple columns excluding first occurrences

Therefore, we have found and highlighted duplicates in multiple columns excluding first occurrences.

**Read More:** **Find Duplicates in Two Columns in Excel (6 Suitable Approaches)**

**Similar Readings**

**How to Find Matching Values in Two Worksheets in Excel (4 Methods)****Excel Formula to Find Duplicates in One Column****How to Find Duplicates in Excel and Copy to Another Sheet (5 Methods)****Excel Top 10 List with Duplicates (2 Ways)****How to Find Similar Text in Two Columns in Excel (3 Easy Ways)**

### 3. Finding and Highlighting Nth Subsequent Duplicate in Excel

Here, we will find Nth subsequent duplicates of a dataset in Excel. You can find out 3rd, 4th, or any subsequent duplicates.

For this example, we will show how you can find out 3rd and all subsequent duplicates, and 3rd duplicate only.

#### 3.1 Finding and Highlighting 3rd and All Subsequent Duplicates

Here, we will find and highlight 3rd and all subsequent duplicates of the duplicate **Items**.

- Select the
**Item** - Next, follow the
**Steps described in Method 1.2**to bring out the**New Formatting Rule**dialog box. - Type the following formula in the formula box.

`=COUNTIF($C$5:$C5,$C5)>=3`

- After that, click on
**Format**and format the cells according to your need. - Click
**OK**.

**Note: **Instead of 3rd and all subsequent duplicates, if you want to find out the 2nd or 4th, just input 4 in the above formula instead of 3.

Thus, the formula will be

**=COUNTIF($C$5:$C5,$C5)>=2, **for the 2nd and all subsequent duplicates.

**=COUNTIF($C$5:$C5,$C5)>=4, **for the 4th and all subsequent duplicates.

Thus, put the order number in the formula according to your needs.

Therefore, you can see the result in the following image.

#### 3.2 Finding and Highlighting 3rd Duplicates Only

Here, we will find out only 3rd duplicates of an **Item**.

- First of all, select the
**Item** - Then, we will follow the
**Steps described in Method 1.2****New Formatting Rule**dialog box. - Input the following formula in the formula box.

`=COUNTIF($C$5:$C5,$C5)=3`

- Click on
**Format**and format the cells according to your need. - Click
**OK**.

**Note: **Instead of 3rd, if you want to find out the 2nd or 4th duplicate, just input 4 in the above formula instead of 3.

Thus, the formula will be

**=COUNTIF($C$5:$C5,$C5)=2, **for the 2nd duplicate.

**=COUNTIF($C$5:$C5,$C5)=4, **for the 4th duplicate.

Thus, put the order number in the formula according to your needs.

Finally, you can see the result in the image below.

## Highlighting Entire Rows When Only One Column Contain Duplicates in Excel

Here, we will **highlight an entire row** when only one of the columns of that row contains a duplicate.

In our dataset, you can notice that the **Purchase Date** and **Cost** columns contain no duplicates. However, the Item columns contain duplicates. Thus, we will highlight the entire row, based on the duplicate items.

__Including First Occurrences:__

Here, we will highlight an entire row based on duplicates in one column including 1st occurrences.

- In the beginning, we will select the entire dataset excluding the column headings. Follow the
**Steps described in Method 1.2****New Formatting Rule**dialog box. - Type the following formula in the formula box.

`=COUNTIF($C$5:$C$20, $C5)>1`

- Then, click on
**Format**and format the cells according to your need >> click**OK**.

As a result, you can see that for the duplicate items, the entire rows get highlighted.

__Excluding First Occurrences:__

Now, we will highlight an entire row based on duplicates in one column excluding 1st occurrences.

- We will select the entire dataset.
- Go through the
**Steps described in Method 1.2**to bring out the**New Formatting Rule**dialog box. - Type the following formula in the formula box.

`=COUNTIF($C$5:$C5, $C5)>1`

- After that, click on
**Format**and format the cells according to your need. - Click
**OK**.

As a result, you can see that for the duplicate items, the entire rows get highlighted excluding the 1st occurrences.

## Highlight Duplicate Rows Only in Excel

Here, we will highlight duplicate rows. This method is effective when you want to highlight absolute duplicate rows in Excel.

__Including First Occurrences:__

Here, we will highlight duplicate rows including 1st occurrences.

- First of all, we will select the whole dataset that we are going to apply Conditional Formatting.
- Go through the
**Steps described in Method 1.2**to bring out the**New Formatting Rule**dialog box and to bring out the formula box. - Type the following formula in the formula box.

`=COUNTIFS($B$5:$B$20, $B5, $C$5:$C$20, $C5)>1`

- After that, click on
**Format**and format the cells according to your need. - Click
**OK**.

As a result, you can see the highlighted duplicate rows.

__Excluding First Occurrences:__

Now, we will highlight duplicate rows excluding 1st occurrences.

- In the beginning, we will select the entire dataset.
- Follow the
**Steps described in Method 1.2**to bring out the**New Formatting Rule**dialog box and to bring out the formula box. - Type the following formula in the formula box.

`=COUNTIF($C$5:$C5, $C5)>1`

- After that, click on
**Format**and format the cells according to your need. - Click
**OK**.

You can see the highlighted rows in the following image.

## Finding and Highlighting Consecutive Duplicate Cells in Excel

Here, we have consecutive duplicates in the Item column. Now, we will find and highlight these consecutive duplicate cells.

__Including First Occurrences:__

Here, we will highlight consecutive duplicate cells including 1st occurrences.

- In the beginning, we will select the
**Item** - Then, following
**Steps described in Method 1.2**, we bring out the**New Formatting Rule**dialog box. - Insert the following formula in the formula box.

`=OR($C1=$C2, $C2=$C3)`

- Next, click on
**Format**and format the cells according to your need. - Click
**OK**.

Hence, you can see the highlighted consecutive duplicate cells.

__Excluding First Occurrences:__

Here, we will highlight consecutive duplicate cells excluding 1st occurrences.

- In the first place, we will select the Item column.
- Then, we will follow the
**Steps described in Method 1.2**to bring out the**New Formatting Rule**dialog box and to bring out the formula box. - Type the following formula in the formula box.

`=$C1=$C2`

- After that, click on
**Format**and format the cells according to your need. - Click
**OK**.

thus, you can see the output in the following image.

## How to Count Duplicates in Excel

Here, we will count duplicates in Excel.

- To do so, we will type the following formula in cell
**G5**.

`=COUNTIF($C$5:$C$20,F5)`

- After that, press
**ENTER**.

Therefore, you can see the result in cell **G5**.

- We drag down the formula with the
**Fill Handle tool**.

Thus, you can see the count of every item including duplicates in cells **G5:G12**.

## How to Remove Duplicates in Excel

In many situations, you might need to get rid of the duplicates in Excel. Here, we will show how you can remove duplicates in an easy way.

In the following dataset, you can see that we have duplicates in the **Item** column. Now, we will remove the duplicates.

- First of all, we will select the entire Item column >> go to the
**Data** - From the
**Data Tools**group >> click on**Remove Duplicates**.

At this point, a **Remove Duplicates Warning** dialog box will appear.

- Click
**Expand the Selection**>> click**Remove Duplicates**.

- In the
**Remove Duplicates**dialog box >> mark**Item**>> click**OK**.

Here, we marked** Item** only since only the Item column contains duplicates.

- Then, click
**OK**in the confirmation dialog box.

Therefore, you can see that we have removed duplicates.

## Things to Remember

- Be cautious to select the correct range of cells before applying
**Conditional Formatting**. - If the database has merged cells, then we need to unmerge them before applying methods of duplication. Otherwise, Excel can’t find duplicates properly.
- We need to select color, in the
**Format**option of the**New Formatting**. Otherwise, Excel will give output by selecting duplicates but we won’t be able to see it due to the absence of perfect color.

## Conclusion

In this article, we extensively describe **3** methods to find and highlight duplicates in Excel. In addition to this, we describe why we need to highlight duplicates.

Furthermore, we show how you can highlight an entire row based on duplicates of a single column, how you can highlight duplicate rows, and how you can find consecutive duplicate cells.

Also, we describe how you can count and remove duplicates.

Thank you for going through the article. We hope this was useful for you. If you have any queries or suggestions, please let us know in the Comment section.

## Related Articles

**How to Find Duplicates in a Column Using Excel VBA (5 Ways)****Find Matches or Duplicate Values in Excel (8 Ways)****How to Use VBA Code to Find Duplicate Rows in Excel (3 Methods)****Excel Find Duplicates in Column and Delete Row (4 Quick Ways)****How to Find Duplicates without Deleting in Excel (7 Methods)****Find Duplicates in Two Different Excel Workbooks (5 Methods)****How to Filter Duplicates in Excel (7 Easy Ways)**