Excel - Find & Highlight Duplicate Rows - 3 Methods | Conditional Formatting

Microsoft Excel can find duplicates easily with Conditional Formatting. The issue is Conditional Formatting finds duplicates based on the cell value, but I want to find duplicate rows. I use three methods in the video to find duplicate rows.

Excel conditional formatting - duplicates are based on cell value, not row values.

Duplicates cell values using Conditional Formatting in Excel

In the video below, I start off with CONCATENATE, then use the TEXTJOIN function, and finally COUNTIFS with no helper column. To highlight the rows I use Conditional Formatting using a formula.

YouTube video

Excel - Find & Highlight Duplicate Rows - 3 Methods | Conditional Formatting

CONCATENATE in EXCEL

_Method 1_

Use CONCATENATE, one of the text functions, to join two or more text strings into one string. You can use an ampersand also which I use in the video.

Example: cell A2 has Chris and cell B2 contains Menard. **=a2&" "&B2** will return Chris Menard in cells C2.  Same example but using CONCATENTATE, you would type in cell C2, **=CONCATENTATE(A2," ",B2)** to get Chris Menard.

Concatenate using an ampersand in Excel

CONCATENATE Function

TEXTJOIN Function

_Method 2_

The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.

Who has TEXTJOIN Function in Excel?

- Office 2019 Windows - Office 2019 Mac - Microsoft 365 subscriber

TEXTJOIN Function in Excel

Conditional Formatting with COUNTIFS Function

_Method 3_

The two methods I used earier - CONCATENTATE and TEXTJOIN - required a helper column. This 3rd method uses Conditional Formatting with the COUNTIFS function. This is the longest method, but a helper column in not required.

The formula I wrote for COUNTIFS inside on Conditional Formatting is

=COUNTIFS($A$2:$A$15,$A2,$B$2:$B$15,$B2,$C$2:$C$15,$C2,$D$2:$D$15,$D2,$E$2:$E$15,$E2,$F$2:$F$15,$F2,$G$2:$G$15,$G2)>1

Steps

1. Select **A2 to G15** or the range. 2. Click **Conditonal Formatting - New Rule**. 3. Select **Use a formula to determine which cells to format**. 4. Type the function above or copy and paste it. 5. Click **Format** and pick a color. 6. Click **OK** twice.

Conditional Formatting with COUNTIFS for Duplicate Rows|