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

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.

1. Highlighting Duplicate Rows in One Column with Built-in Rule in Excel

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.

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

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

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.

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

• 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 but Keep One in Excel

2. Inserting 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.

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

• Then, select the Format for your duplicate rows as per your preferences.

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

• Along with this, select your  Format for the duplicate rows and click OK.

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

3. Highlighting Duplicate Rows in Range Using Excel 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.

• Following this, select your preferred Format for the highlighted rows and click OK to proceed.

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

• Also, select your Format and click OK twice to close the window.

• That’s it, we have our required output.

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.

Download Practice Workbook

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

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.

Related Articles

• Highlight Cells If There Are More Than 3 Duplicates in Excel
• How to Highlight Duplicates in Excel with Different Colors
• How to Highlight Duplicates in Two Columns in Excel
• How to Highlight Duplicates in Two Columns Using Excel Formula
• How to Highlight Duplicates in Multiple Columns in Excel
• Highlight Duplicates across Multiple Worksheets in Excel
• [Fix:] Highlight Duplicates in Excel Not Working