How to Highlight Duplicate Rows in Excel (3 Ways)

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


Download Practice Workbook

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

Highlight Duplicate Rows in Excel (3 Ways)

1. Highlight Duplicate Rows in One Column using the 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!

i. Highlight Duplicate Rows Including the First Occurrence

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

 Highlight Duplicate Rows Including the First Occurrence

Step 1:

  • To highlight the duplicate rows, select cells from B4 to B13.
  • Now go to Home, click on Conditional Formatting in the Style Then click on Highlight Cell Rules and select Duplicate Values.

Home → Conditional Formatting → Highlight Cell Rule → Duplicate Values

 Highlight Duplicate Rows Including the First Occurrence

Step 2:

  • A dialogue box named Duplicate Values will open up. From this window, you can highlight, color, and format your duplicate values or unique values.
  • Just click on the drop-down icon to select your format.
  • We have selected Red Text to highlight our duplicate rows.

 Highlight Duplicate Rows Including the First Occurrence

Step 3:

Now Click Ok to get your duplicate rows highlighted.

 Highlight Duplicate Rows Including the First Occurrence


ii. Highlight Duplicate Rows Excluding the 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 methods.

Step 1:

  • Go to,

Home → Conditional Formatting → New Rule

 Highlight Duplicate Rows Excluding the First Occurrence

Step 2:

  • In the New Formatting Rule window, select Use a Formula to Determine Which Cells to Format.

 Highlight Duplicate Rows Excluding the First Occurrence

  • In the Format Values Where This Formula is True box, apply the COUNTIF
  • The formula is,

                         

=COUNTIF($B$4:$B4,$B4)>1
  • Where $B$4:$D$13 is the range
  • $B4 is the criteria

 Highlight Duplicate Rows Excluding the First Occurrence

Step 3:

  • Click on the Format to select format styles for your highlighted rows.
  • We have chosen Bold as the font style and the color is Red.
  • Click Ok to proceed

 Highlight Duplicate Rows Excluding the First Occurrence

  • Now click on Ok to complete the task and highlight your duplicate rows.

 Highlight Duplicate Rows Excluding the First Occurrence

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

 Highlight Duplicate Rows Excluding the First Occurrence


Similar Articles to Explore


2. Insert the COUNTIFS Function to Highlight Duplicate Rows

In the following example, we have a range of datasets where the “Model”,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 the COUNTIFS Function to Highlight Duplicate Rows

Step 1:

  • Select the dataset and go to

Home → Conditional Formatting → New Rule

Insert the COUNTIFS Function to Highlight Duplicate Rows

Step 2:

  • In the New Formatting Rule window, select Use a Formula to Determine Which Cells to Format
  • In the Format Values Where This Formula is True box, apply the COUNTIFS function to match multiple criteria.
  • Input the criteria and ranges. The final formula is,
=COUNTIFS($B$4:$B$13,$B4,$C$4:$C$13,$C4,$D$4:$D$13,$D4)>1
  • Where, $B$4:$B$13, $C$4:$C$13, $D$4:$D$13 are the ranges.
  • $B4, $C4, $D4 are the criteria.
  • Then select the format for your duplicate rows as per tour preferences.

Click Ok to apply

Insert the COUNTIFS Function to Highlight Duplicate Rows

So the duplicate rows are highlighted.

Insert the COUNTIFS Function to Highlight Duplicate Rows

Step 3:

  • We can also apply the same procedure to find duplicate rows without the first occurrence.
  • For this condition, the COUNTIFS formula is,
=COUNTIFS($B$4:$B4,$B4,$C$4:$C4,$C4,$D$4:$D4,$D4)>1
  • Select your format for the duplicate rows and click OK

Insert the COUNTIFS Function to Highlight Duplicate Rows

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

Insert the COUNTIFS Function to Highlight Duplicate Rows.xlsx


Similar Articles to Explore


3. Highlight Duplicate Rows in a Range

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.

Step 1:

  • Using the Conditional Feature, go to the New Formatting Rule
  • In the Format Values Where This Formula is True box, apply this formula.
=COUNTIFS($B$4:$D$13,B4)>1
  • Here the range is $B$4:$D$13 and the criteria is B4
  • Select your preferred format for the highlighted rows and click OK to proceed.

Highlight Duplicate Rows in a Range

  • So our duplicate rows are highlighted in a range.

Highlight Duplicate Rows in a Range

Step 2:

  • We can also highlight the duplicate rows in a range without the first occurrence.
  • To do that apply this formula in the Format Values Where This Formula is True box
=COUNTIFS($B$4:$B4,$B4)>1
  • Where the range and criteria are $B$4:$B4, $B4
  • Select your format and click

Highlight Duplicate Rows in a Range

And our task is done.

Highlight Duplicate Rows in a Range


Quick Notes

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

Conclusion

Highlighting duplicate rows in excel is quite easy if you follow the procedures we discussed in this article. 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.


Similar Articles to Explore

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo