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.

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