How to find and highlight duplicate rows in a range in Excel?

Sometimes, duplicate records may exist in your worksheet's data range, and you may need to find or highlight these duplicate rows, as shown in the following screenshots. Manually checking each row for duplicates is time-consuming and impractical, especially when dealing with hundreds of rows. This guide will introduce several efficient methods to handle this task effectively.

Find duplicate rows across multiple columns with formulas

Highlight duplicate rows across multiple columns with Conditional Formatting

Select or highlight duplicate rows across multiple columns with a handy feature

Find duplicate rows across multiple columns with formulas

The following formula can help you find the duplicate records, please do as this:

1. In the adjacent blank cell, cell D2 in this case, please enter the below formula:

2. And then drag the fill handle down to the cells for applying this formula, now, you can see, if there are identical rows in this used range, it will display Duplicate row, see screenshot:

Tips: If you want to find duplicate rows without the first occurrences, please apply the following formula:

Highlight duplicate rows across multiple columns with Conditional Formatting

If you can’t apply the formula correctly, please don’t worry, the Conditional Formatting utility also can help you highlight the duplicate rows. Do with the following steps:

1. The first step you should to use the CONCATENATE function to combine all the data into one cell for each row. Type the below formula in cell D2, then copy the formula down until the last row of data see screenshot:

2. Then, select the range that you want to find the duplicate rows including the formulas in column D, and then go to Home tab, and click Conditional Formatting > New Rule, see screenshot:

3. In the New Formatting Rule dialog box, please do the following operations:

• Click Use a formula to determine which cells to format from the Select a Rule Type list box;
• And then, enter this formula =COUNTIF($D$2:$D$12,$D2)>1 (Highlight duplicate rows with first occurrences) or =COUNTIF($D$2:$D2,$D2)>1 (Highlight duplicate rows without first occurrences) into the Format values where this formula is true text box;
• At last, click Format button.

4. In the popped out Format Cells dialog box, click Fill tab, and then, choose one color that you need to highlight the duplicates.

5. Click OK > OK to close the dialog boxes, and the duplicate rows are highlighted by the color you choose at once, see screenshot:

Highlight duplicate rows with the first ones	Highlight duplicate row without the first ones

Select or highlight duplicate rows across multiple columns with a handy feature

The above methods are somewhat troublesome for you, so here, I can introduce you an easy and handy tool-Kutools for Excel, with its Select duplicate & unique cells utility, you can quickly select the duplicate rows or unique rows as you need.

1. Click Kutools > Select > Select Duplicate & Unique Cells, see screenshot:

2. In the Select duplicate & unique cells dialog box, please do the following operations:

• Click button to select the range that you want to use;
• Then, select Each row from the Based on section;
• And then,check Duplicates(Except 1st one) or All duplicates(Including 1st one) option under Rule section as you need;
• At last, you can specify a background color or font color for the duplicate rows under the Processing of results.

3. Then click OK, and the duplicate rows are selected as following screenshots:

Select duplicate rows including the first ones	Select duplicate rows excluding the first ones

More relative articles: