How to Highlight All Duplicate Values or An Entire Row/Column with Duplicate Values in Excel

In our daily work, we often find some duplicate values in a certain range. For example, when accounting the profits for different salespersons in a week, they may make the same profit on different days, or they may have the same total profits for the whole week. If we want to highlight all duplicate profits or highlight the entire rows with only the duplicate total profits, how can we do? Actually, we can through conditional formatting function to highlight duplicate values or rows/columns with duplicate values for different situations accordingly. This free tutorial will show you the details to highlight duplicate values for above two cases.

Precondition:

See screenshot below. There are some duplicate values in the table.

How to Highlight All Duplicate Values 1

Method 1: Highlight All Duplicate Values in the Entire Table


If we just want to find all duplicate values without any limit, we can directly highlight them by applying ‘Duplicate Values’ rule in conditional formatting function. See details below.

Step 1: Select the entire table. Then click Home in ribbon, click the small triangle on Conditional Formatting icon (in Styles group), then select Highlight Cells Rules->Duplicate Values option.

How to Highlight All Duplicate Values 2

Step 2: Just keep default settings in pops up ‘Duplicate Values’ dialog. You can also change color in ‘values with’, then click OK.

How to Highlight All Duplicate Values 3

Step 3: Check result below. All duplicate values are highlighted.

How to Highlight All Duplicate Values 4

Method 2: Highlight the Entire Row or Column with Duplicate Values


In this example, we want to highlight the entire rows if they have the same Total values.

Step 1: Select the entire table, this is important. Click Home in ribbon, then click Conditional Formatting in Styles group, select New Rule in menu.

How to Highlight All Duplicate Values 5

Step 2: On New Formatting Rule dialog, select the last rule type: Use a formula to determine which cells to format.

How to Highlight All Duplicate Values 6

Step 3: In Edit the Rule Description section, in Format values where this formula is true textbox enter the formula =COUNTIF($E$2:$E$6,$E2)>1.

How to Highlight All Duplicate Values 7

Step 4: Click Format button in Preview section. Verify that Format Cells window is displayed.

How to Highlight All Duplicate Values 8

Step 5: Click Fill tab, then select a color as background color. For example, select yellow, then click OK.

How to Highlight All Duplicate Values 9

Step 6: After above step, we go back to New Formatting Rule window. You can see that in Preview section, cell is filled with yellow. Click OK.

How to Highlight All Duplicate Values 10

Step 7: Check the result below. You can see that entire rows with duplicate total values are highlighted.

How to Highlight All Duplicate Values 11

Related Functions


  • 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

Match Single Criteria in Excel

No repeat statistic based on a single condition is used wildly in daily work, for example, count type of products, the duplicate types are not encountered. This article describes the way of no repeats statistics for data in a single ...

Calculate Win Loss Tie

Suppose you got a task to calculate the win, loss, and tie totals; what would you do? If you are new to Ms Excel and don't have enough experience with it, then you might do this task manually but let ...

Break ties with helper COUNTIF and column

Suppose you got a task to adjust the values that contain the ties; what would be your first attempt to break the ties of the given value? If you are wondering about doing this task manually, let me add that ...

Sort/Rank Numeric Values with Duplicate Values Exist

Excel built-in RANK function can sort a set of values. If there are duplicate numbers, then the rank number is also duplicated. See the following example: There are two numbers “100” in range A2:A9, they are both the third largest ...

Count Attendance and Absence with COUNTIF function

In our campus life and work life, we usually record everyone's attendance. Today we will introduce you the application of Excel COUNTIF function to count the attendance. Using a week as an example, we use the following example to show ...

Sidebar