How to Find and Highlight Duplicates in Excel (3 Handy Ways)

If you are looking for how to find and highlight duplicates in Excel, then you are in the right place. Sometimes we may want that we will observe which rows are duplicates in the spreadsheet. Then we can manually delete those rows. It is easy to mark duplicate rows in a column. For a long list, it will take time to delete the duplicate ones manually. But for a small list, it works nice. Whatever, we’ll try to discuss now how to find and highlight duplicates in Excel.


Download Practice Workbook


3 Ways to Find and Highlight Duplicates in Excel

We can find and highlight duplicates in a single column, multiple columns, or all of the dataset by adopting some simple steps. Excel offers these steps in an effective way.


1. Utilizing Conditional Formatting

We can use the Conditional Formatting feature to highlight duplicate rows. We just need to follow some simple steps to do that.

Steps:

  • Firstly, select the cells that we want to duplicate. In this case, it is D5:D18.
  • Secondly, go to Home > choose Conditional Formatting > go to Highlight Cells Rules > select Duplicate Values.

Utilizing Conditional Formatting to Find and Highlight Duplicate Rows

  • Eventually, a Duplicate Values window will appear.
  • Thirdly, choose Duplicate.
  • Fourthly, select any color options in the values with In this case, we have selected Light Red Fill with Dark Red Text.

Utilizing Conditional Formatting to Find and Highlight Duplicate Rows

Consequently, we’ll see that the duplicate values in the D Column are highlighted.

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.

Read More: Finding out the number of duplicate rows using COUNTIF formula

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.

2. Find and Highlight Duplicate Rows without 1st Occurrences

We can use the COUNTIF function to identify and highlight duplicate rows. By using this function we can do that for both without 1st occurrences and with 1st occurrences. To show the ways to identify and highlight duplicate rows without 1st occurrences, just follow the steps.

Steps:

  • Firstly, select the range of the cells which we need to find duplicates, in this case, it is D5:D18.
  • Secondly, go to Home > choose Conditional Formatting > select New Rule.

Using COUNTIF Function to Identify and Highlight Duplicate Rows without 1st Occurrences

  • Consequently, a New Formatting Rule window will appear.
  • Thirdly, click Use a formula to determine which cells to format.
  • Fourthly, write the following formula in the formula box.
  • =COUNTIF($D$5:$D5,$D5)>1
  • Fourthly, click OK.

Additionally, we need to set the font size, color etc. by going into the Format option.

Using COUNTIF Function to Identify and Highlight Duplicate Rows without 1st Occurrences

Eventually, we’ll see that the duplicates are shown in light orange without the 1st occurrences.

find and highlight duplicates


3. Highlighting Duplicates in a Range of Multiple Columns

We can highlight duplicates in a range of multiple columns very easily. We can do it for both with 1st occurrences and without 1st occurrences cases.


3.1 Highlighting Duplicates Including 1st Occurrences

We need to use the COUNTIF function to highlight duplicates in multiple columns, including the 1st occurrences. To show this, we’ll work with the following dataset where we need to highlight the duplicates in the D and E Columns.

Highlighting Duplicates in Multiple Columns Including 1st OccurrencesSteps:

  • Firstly, go to the New Formatting Rule window by going to the Conditional Formatting option as before.
  • Secondly, choose  Use a formula to determine which cells to format.
  • Thirdly, write the following formula in the formula box.
=COUNTIF($D$5:$E$16,$D5)>1
  • Fourthly, click OK.

Highlighting Duplicates in Multiple Columns Including 1st Occurrences

Consequently, we’ll notice that the D and E Columns are highlighted for duplicates, including the 1st occurrences.

find and highlight duplicates


3.2 Highlighting Duplicates Excluding 1st Occurrences

We can highlight duplicates for both multiple columns and for all the columns where there is a database. In both of these cases, we need the help of the COUNTIF function.

⧪ Highlighting Duplicates in 1st Column

If we want to highlight duplicates in multiple columns, we have to put the COUNTIF function in the 1st column.

Steps:

  • To do this, first go to the New Formatting Rule through the Conditional Formatting option as we have discussed before.
  • Secondly, select Use a formula to determine which cells to format in the same way as before.
  • Thirdly, write the following formula in the formula box.
=COUNTIF($D$5:$D5,$D5)>1
  • Fourthly, click OK.

Highlighting Duplicates in Multiple Columns Except for 1st Occurrences

Finally, our output will be like this, showing the highlighting of the duplicates except for the 1st occurrences.

⧪ Highlighting Duplicates to All Subsequent Columns 

We can highlight duplicates for all subsequent columns by following these simple steps.

Steps:

  • Similarly, as before, go to the New Formatting Rule through the Conditional Formatting
  • Secondly, select Use a formula to determine which cells to format in the same way as before.
  • Thirdly, write the following formula in the formula box.
=IF(COLUMNS($B5:B5)>1,COUNTIF(A$5:$B$16,B5),0)+COUNTIF(B$5:B5,B5)>1
  • Fourthly, click OK.

Using COUNTIF Function to All Subsequent Columns

Eventually, we’ll find duplicates of all the columns in the database.


How to Count Duplicates in Excel

We can count duplicates in an Excel file through some simple methods. One of the methods is to use the COUNTIF function. Suppose we have the following dataset where the column header of D Column is Item Name. In this column, there are different fruit names. We want to count duplicates in this column. We’ll count it in the G Column named Count with the usage of the F Column named Item.

How to Count Duplicates in Excel

Steps:

  • Firstly, write the following formula in the G5 cell like this.
=COUNTIF($D$5:$D$16,F5)

How to Count Duplicates in Excel

  • Secondly, press ENTER to get the output as 1.
  • Thirdly, use the Fill Handle by dragging down the cursor while holding the right-bottom corner of the reference G5
  • Eventually, we’ll find all the counts of fruits in the G Column like this.


How to Remove Duplicates in Excel

We can remove duplicates in Excel very easily by following some simple methods. One of the methods is by using the Data Tools option. However, we’ll remove duplicates of the cells B4:D16 of the following dataset where we have column headers as Business Type, Branch Location, Item Name.

How to Remove Duplicates in Excel

Steps:

  • Firstly, select the cells. In this case it is B4:D16.
  • Secondly, go to Data > choose Data Tools > select Remove Duplicates.

  • Eventually, a Remove Duplicates window will appear.
  • Thirdly, select the options in the Column box that we want to take into account.
  • Fourthly, click OK.

Consequently, we’ll see that the duplicates are removed from the dataset like this.


Things to Remember

  • 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 the font type, especially 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

We can find and highlight duplicates in Excel very easily if we study this article properly. Please feel free to visit our official Excel learning platform ExcelDemy for further query.


Read More

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo