This tutorial will teach you how to **highlight duplicates rows using conditional formatting** feature in Excel. In the previous post, we talked that how to change the color of rows based on a certain number or text or begin a specific character in a specified column. And this post will talk that how to highlight entire rows that are duplicates in excel 2016, 2013 or lower version. Or how to change the background color of duplicate rows.

Table of Contents

## Highlight duplicate rows in only one column

If you data just have only one column in each rows, then you can following the below steps to highlight duplicate rows:

**1# **select the range of cells in that column

**2#** on the **HOME** Tab, click **Conditional Formatting** command under **Styles** group, click **Highlight Cells rules**, and then select **Duplicate Values**.

**3#** select **Duplicate** and **Light Red Fill with Dark Red Text** from the **Format cells that contains** box in the **Duplicate Values** window. Click **OK** button.

**4#** you will see that all rows which are duplicates are highlighted.

## Highlight duplicate rows in multiple columns

Assuming that you want to highlight duplicate rows in a range of cells A2:C4 with conditional formatting, and you need to write a new formula to apply the conditions to find the duplicate rows.

**Method 1:**

You can create your own formula based on the COUNTIFS function to count duplicated values in each column of your range. So you can use the following formula with COUNTIFS function:

=COUNTIFS($A$2:$A$6,$A2,$B$2:$B$6,$B2,$C$2:$C$6,$C2)>1

Let’s see the below steps:

**1#** select the range of cells in your table

**2#** on the **HOME** tab, click the **Conditional Formatting** command under **Styles** group. Then select **New Rules…** from the drop-down menu list.

**3#** the New Formatting Rule window will appear.

**4#** select the **Use a formula to determine which cells to format **option under **Select a Rule Type:** box, and then enter the above formula in the **Format values where this formula is true**

**5#** click the **Format… **button, then the **Format Cells** window will appear.

**6#** in the “**Format Cells**” window, switch to the** Fill** tab, choose the background color, and then click **OK** button. you can also switch to other tabs to tweak the settings as you want.

**7#** back in the **New Formatting Rule** window, you can see a preview of your rows background color. Then click **OK** button.

**8#** let’s see the last result.

If you just want to highlight duplicate rows except for the first occurrences, and you can use the following COUNTIFS formula as the conditional formatting rule.

=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)>1

**Method 2:**

You can use the CONCATENATE function or concatenate operator to join all values into one cell in each row, then you just need to check only one cell value in one column to find the duplicate values in the same column. At this time, you can use the COUNTIF function to create a formula, then apply it as the conditional formatting rule to find the duplicate rows. You can do the following steps:

**1#** create another column where you want to combine all values and call it Joined, such as column D

**2#** enter the following formula into the cell D2 to combine columns in each row

=CONCATENATE(A2,B2,C2)

**3#** drag AutoFill Handle down to the rest of cells in column D

**4#** select the range that you want to highlight duplicate rows including the column D

**5#** on the **HOME** tab-> **Styles** -> **Conditional Formatting**, and then click **New Rule….**

**6#** select the **Use a formula to determine which cells to format **option under **Select a Rule Type:** box, and then enter the following formula in the **Format values where this formula is true** box.

=COUNTIF($D$2:$D$6,$D2)>1

**7#** click the Format… button, then switch to Fill tab, and choose one background color. Click OK.

8# you can now delete the column D.

### Related Functions

- Excel Concat function

The excel CONCAT function combines 2 or more strings or ranges together.This is a new function in Excel 2016 and it replaces the CONCATENATE function.The syntax of the CONCAT function is as below:=CONCAT (text1,[text2],…)… - Excel COUNTIFS function

The Excel COUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:**=**COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)… - Excel COUNTIF function

The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…

### Related Posts

- Highlight Rows

You will learn that how to**change the color of the entire rows**if the value of cells in a specified column meets your conditions, such as, if the value of cells is equal to or greater than a certain number or text values, then excel should be highlight entire rows or change a row color as you need.… - Find Duplicate Rows

If you want to check the entire row that duplicated or not, if True, then returns “duplicates” value, otherwise, returns “no duplicates”. You can create a formula based on the IF function and the SUMPRODUCT function..… - Highlight duplicate values

this post will teach you how to highlight duplicate values in the range of cells in excel. Normally, you may be need to identify duplicate values with a range of cells in Excel. And there is one of the fasted way that is using conditional formatting feature in Microsoft Excel…… - Combine Duplicate Rows and Sum the Values

This post will teach you how to**combine duplicate rows and sum the corresponding values**or calculate numbers in specific column in excel. And how to merge duplicate rows and then sum the values with VBA macro in Excel..…

## Leave a Reply

You must be logged in to post a comment.