Find and Highlight Duplicates in Excel (3 Handy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview Image to Find and Highlight Duplicates in Excel


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

Dataset to Find and Highlight Duplicates in Excel


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.

Selecting Duplicate Values from Conditional Formatting

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.

Formatting Duplicate Cells

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.

Highlighted Duplicates Cells

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: Find Duplicates in Two Columns in Excel


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.

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

Typing Formula to Highlight Duplicates excluding 1st Occurrences

Then, a Format Cells dialog box will appear.

  • From Fill >> select a Color >> click OK.

Selecting Fill Color

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

Clicking OK on New Formatting Rule window

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

Highlighted Duplicates in Single Column Excluding First Occurrences

Read More: Excel Formula to Find Duplicates in One Column


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.

Dataset for Highlighting Duplicates in Multiple Columns


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.

Highlighting Duplicates in Multiple Columns

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

Duplicates in multiple columns excluding first occurrences

Read More: How to Find Duplicates in Excel Workbook


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.

highlighted duplicates excluding first occurrences

Read More: How to Find Similar Text in Two Columns in Excel


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.

Highlighting nth and all subsequent duplicates

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

Result Showing 3rd and all subsequent duplicates


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 to bring out the 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.

Typing Formula to Find Nth Duplicate Only

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

Highlighted Nth Duplicate


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 to bring out the 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.

 Highlighting rows based on duplicates in one column

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

Highlighted Entire rows including First Occurrences

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.

 Highlighting Entire Row Excluding Duplicates

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

Result after Highlighting Entire Row Excluding Duplicates


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.

Typing Formula to Highlight Duplicate Rows

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

Duplicate Rows Including First Occurrences

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.

Highlighting Duplicates Excluding First Occurrences

You can see the highlighted rows in the following image.

Duplicate Rows Excluding First Occurrence


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.

 Highlighting Consecutive Duplicates including First Occurrences

Hence, you can see the highlighted consecutive duplicate cells.

Highlight Consecutive Duplicates

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.

 Highlighting Consecutive Duplicates Excluding First Occurrences

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

Highlighted Consecutive Duplicates Excluding First Occurrences


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.

Using COUNTIF Function


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.

Selecting Remove Duplicates

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

  • Click Expand the Selection >> click Remove Duplicates.

Select Expand the selection

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

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

Selecting Item

  • Then, click OK in the confirmation dialog box.

Clicking OK

Therefore, you can see that we have removed duplicates.

Dataset has no Duplicates

Read More: How to Find Duplicates without Deleting in Excel


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.

Download Practice Workbook

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


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


<< Go Back to Find Duplicates in Excel | Duplicates in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo