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
If Cell Equals Certain Text String
If cell equals certain text_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2
If cell contains text1 or text2_1

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

If Cell Begins with One of Three Supplied Characters
If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or ...

Average per Week by Formula in Excel
Average per Week 1

We usually apply AVERAGE function or relevant functions to return average directly in Excel worksheet. But in some situations, only applying average relevant functions cannot figure out our problem. Sometimes we can create a formula with functions and mathematical operation ...

How to Statistic Pass 4 Out of 5 Rounds in a Competition by Excel Functions
Statistic Pass 4 Out of 5 Rounds 1

Suppose you are counting the numbers of participants who are the winners in a competition. And the passing line is provided as pass 4 out of 5 rounds of games. You need to know if participant pass the game or ...

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column
How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column14

Sometimes we may meet the case that subtotal values for different groups and only record a subtotal value for one group in a column, for other cells in this group, keep them blank. Today we will introduce you how can ...

Running Count of Occurrence List
running count of occurrentce in list2

This post will guide you how to get running count of an occurrence in a list with formula in Excel 2013/2016 or Excel office 365. How to create a running count of certain values that appear in the given range ...

Count Non-Contiguous Range using COUNTIF
count non-contiguous ranges 2

This post will guide you how to count a non-contiguous range with criteria in COUNTIF function in Excel 2013/2016 or Excel office 365. How do I build a formula for counting non-contiguous range in Excel using COUNTIF function in Excel. ...

How to Count Unique Numeric Values in a Range in Excel
count unique numberic values7

This post will guide you how to count unique numeric values in the given range in Excel 2013/2016 or Excel office 365. How do I count the unique numeric values in a list of data with some duplicate values using ...

How to Count Unique Dates in Excel

This post will guide you how to count unique dates in a given range in Excel 2013/2016 or Excel office 365. How do I count the unique dates in a list of dates with some duplicate dates using a formula ...

Sidebar