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

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
Count Attendance and Absence1

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

Count Cells that are Case Sensitive
count cell that contain case sensitive1

If you are a frequent user of Microsoft Excel, you may have come across situations where you needed to add or count the cells that were case-sensitive. If you have done this task manually, I would like to point out ...

Check Dates in chronological order
sort dates in chronological order1

Assume you have a date list that has different date formats, as seen in the accompanying picture. In this instance, Excel's Sort function will fail to sort them appropriately. However, you may convert all various date formats to a particular ...

Check If a Cell is Blank or Empty
check if a cell is blank 1

The article demonstrates how to check if a range of cells is blank or empty by ISBLANK and other functions to recognize empty cells in Excel and take appropriate action based on their status. There are several circumstances in which ...

Extract matching values From Two Lists
extract matching values from two list1

Suppose that you are working with two lists containing few values, and you want to extract the matching values from those two lists into another separate list. You might prefer to manually extract the matching values from the two lists, ...

Extract Common Values in Two Lists

Just assume that you have two lists containing values/words in the few cells, and you want to extract the same or common values/words from the two lists into another separate list; then you might think that it's not a big ...

Extract Unique Items From A List
Extract Unique Items From A List In Excel1

This post will guide you how to extract unique itmes from a given list in Microsoft Excel. How to create a newly formula to get unique values from a range cells in Excel. The unique list of items is the ...

Sidebar