How to Highlight Duplicate Rows in Excel (3 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

While working with a large dataset in Excel, there is a possibility that you are getting the same values in rows or columns. Sometimes we may need to find and highlight those duplicate values to get a clear concept of the worksheet. Excel provides some built-in features by which you can easily highlight duplicate rows. Today, in this article, we will learn in 3 quick ways how to highlight duplicate rows in Excel.


Download Practice Workbook

Download this practice sheet to practice while you are reading this article


3 Quick Ways to Highlight Duplicate Rows in Excel

To demonstrate the methods, we will create some sample datasets and highlight duplicate rows from them. For this, follow the processes below.


1. Highlight Duplicate Rows in One Column with Built-in Rule

In Microsoft Excel, we have an interesting tool named Conditional Formatting. Using this feature you can easily highlight your duplicate rows with or without the first value occurrence. Let’s learn both of the procedures!

1.1. Including First Occurrence

In the following example, we prepared a dataset containing some names of the camera model in the column named Product List. Now in this column, there are some duplicate rows. We need to find them and highlight them.

Highlight Duplicate Rows in One Column with Built-in Rule

  • First, select the Cells from B5 to B14.
  • Now, go to the Home tab and click on Conditional Formatting in the Style section.
  • Then, click on Highlight Cell Rules.

  • Afterward, select Duplicate Values.

  • Then, a dialogue box named Duplicate Values will open up.
  • Here, choose Duplicate as the condition.
  • Along with it, click on the drop-down icon to select your format to highlight.
  • For example, we have selected Red Text to highlight our duplicate rows.

Highlight Duplicate Rows in One Column with Built-in Rule

  • Now, click OK to get your duplicate rows highlighted.

Highlight Duplicate Rows in Excel


1.2. Excluding First Occurrence

Now we will highlight our duplicate rows without the first occurrence. To do that we will use the COUNTIF function. Let’s discuss the process.

  • In the beginning, select the Cell range B5:B14.
  • Then, choose Home > Conditional Formatting > New Rule.

Highlight Duplicate Rows in One Column with Built-in Rule

  • Afterward, in the New Formatting Rule window, select Use a Formula to Determine Which Cells to Format.
  • Following this, in the Format Values Where This Formula is True box, apply this formula.
=COUNTIF($B$5:$B5,$B5)>1

Highlight Duplicate Rows in One Column with Built-in Rule

Here, the COUNTIF function counts cells in the $B$5:$B5 Cell range based on the criteria in Cell $B5.
  • Next, click on Format to select format styles for your highlighted rows.

  • For instance, we have chosen Bold as the Font style and the Color is Red.

  • Lastly, click on OK twice to complete the task and highlight your duplicate rows.
  • Finally, we have got our highlighted duplicate rows without the first occurrence.

Read More: How to Highlight Duplicates in Excel (6 Easy Ways)


Similar Readings


2. Insert COUNTIFS Function to Highlight Duplicate Rows in Excel

In the following example, we have a range of datasets where the Model and Price of some Items are given. In this dataset, there are some duplicate rows that we need to find out and highlight. The COUNTIFS function can help you to highlight your duplicate rows in a dataset. The COUNTIFS function allows comparing cells by multiple criteria.

Insert COUNTIFS Function to Highlight Duplicate Rows in Excel

  • First, select the dataset and go to Home > Conditional Formatting > New Rule.

  • Next, in the New Formatting Rule window, select Use a Formula to Determine Which Cells to Format.
  • Following this, in the Format Values Where This Formula is True box, apply this formula.
=COUNTIFS($B$5:$B$14,$B5,$C$5:$C$14,$C5,$D$5:$D$14,$D5)>1
  • Then, select the Format for your duplicate rows as per your preferences.

Insert COUNTIFS Function to Highlight Duplicate Rows in Excel

In the formula, the COUNTIFS function applies the criteria ($B5, $C5, $D5) across $B$5:$B$14, $C$5:$C$14, $D$5:$D$14 Cell ranges and count cells where the criteria meet.
  • Lastly, click OK to apply.
  • That’s it, we can see that the duplicate rows are highlighted.

  • If you need to highlight duplicate rows without the first occurrence, then apply this formula.
=COUNTIFS($B$5:$B5,$B5,$C$5:$C5,$C5,$D$5:$D5,$D5)>1
  • Along with this, select your  Format for the duplicate rows and click OK.

Insert COUNTIFS Function to Highlight Duplicate Rows in Excel

  • Finally, we have got our highlighted duplicate rows without the first occurrence.

Read More: Formula to Find Duplicates in Excel (6 Easy Ways)


3. Highlight Duplicate Rows in Range Using Conditional Formatting

We can also highlight duplicate rows from a range of data too. We can easily do it by using the Conditional Formatting feature. To demonstrate this procedure, we will use the previous example. Let’s learn this method by following these steps.

  • First, select the dataset and go to Home > Conditional Formatting > New Rule.
  • Then in the Format Values Where This Formula is True box, apply this formula.
=COUNTIFS($B$5:$D$14,B5)>1
  • Following this, select your preferred Format for the highlighted rows and click OK to proceed.

Highlight Duplicate Rows in Range Using Conditional Formatting

Here the range is $B$5:$D$14 and the criteria is B5.
  • That’s it, we have our duplicate rows that are highlighted in a range.

  • We can also highlight the duplicate rows in a range without the first occurrence.
  • To do this, apply this formula in the Format Values Where This Formula is True box.
=COUNTIFS($B$5:$B5,$B5)>1
  • Also, select your Format and click OK twice to close the window.

Here, the range and criteria are $B$5:$B5 and $B5 respectively.
  • That’s it, we have our required output.

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


Things to Remember

  • When you select your Range, you have to use the Absolute Cell References ($) to block the array.
  • You can also highlight the unique values instead of the duplicate values. Just change the highlight option from Duplicate to Unique.
  • Make sure to select the total dataset for highlighting duplicate rows.

Conclusion

So far we discussed in 3 quick ways how to highlight duplicate rows in Excel. We hope this article proves useful to you. If you have any ideas or suggestions, you are welcome to share your thoughts in the comment box. Follow ExcelDemy for more tutorials.


Related Articles

Asikul Himel

Asikul Himel

Hi! I am Asikul Islam Himel. Glad you are here. I am a Team Leader of ExcelDemy, running an excellent team of five efficient Excel & VBA Content Developers. Here at ExcelDemy, we give the best sustainable solutions by posting articles related to MS Excel-related problems. I have completed my under graduation degree from Bangladesh University of Engineering and Technology and my program was Naval Architecture and Marine Engineering. I have found passion in data analysis and research-based fields. I am currently working to grow my leadership quality. I have a great interest in project management and critical thinking. In my free time, I love to travel and read books.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo