When you highlight the Max or Large value in a row using Max or Large functions, it will highlight duplicates, if any, also. In this tutorial, I am going to elaborate on how to highlight Max or Large value leaving duplicates in each row in Google Sheets.

In the case of Large 2 (Max 2) and Large 3 (Max 3), the definition of highlighting Max value leaving duplicates is as follows (under the below subtitle).

Examples to Understand the Scenario (Highlighting)

Go through the values in the range B2:H2 in table 1.

Table 1:

	B	C	D	E	F	G	H
2	319	60	170	318	11	318	1

In this, the Max value or largest 1 value is 319. So there is no issue in highlighting.

Select the range B2:H2 and apply the below LARGE formula to highlight the max or largest 1 value.

=B2=large($B2:$H2,1)

This is an alternative to Large 1.

=B2=max($B2:$H2)

But the second largest (Large 2) value is 318 which repeats in cells E2 and G2.

So normally the following formula would highlight both these cells if you highlight the Large 2 (Max 2) value.

=B2=large($B2:$H2,2)

I just want to highlight the cell E2 that is the first appearance of the second largest value in that row.

Similarly, if the largest 3 value has a duplicate, I just don’t want to highlight that duplicate value. See row 3 of the above table below.

Table 2:

	B	C	D	E	F	G	H
3	133	87	85	85	28	30	1

The following (normal or standard use formula) would highlight the cells D3 and E3. I just want to highlight only the cell D3 (first appearance).

=B2=large($B2:$H2,3)

In this Google Sheets tutorial, you are going to get 3 rules (conditional format formulas) to highlight Max 1, Max 2, and Max 3 values leaving duplicates in row-wise.

If you use all the 3 rules, there is something more from my side to clarify. Here is that.

Max Value Highlighting without Duplicates – Things to Know

If I want to highlight Max 1, Max 2, and Max 3 values, as per row 3 (second table above), the cells to be highlighted are B3, C3, and D3. In row 2 (first table), it would be B2, E2, G2.

That means;

When I say, highlight max value leaving duplicates, it means, leaving duplicates of Max 1, Max 2, and Max 3 individually.

If you just want to highlight Max 2, if Max 1 and Max 2 are the same value, the second cell containing the value will be highlighted and a third value of the same will be ignored.

That means if you want to use three rules to highlight Max 1, Max 2, and Max 3 leaving/without duplicates, then the highlighting will be limit to 3 cells in that row. The values may or may not be duplicate values.

Please see the below image for more clarification.

Steps to Highlight Max Value Leaving Duplicates in Google Sheets

To highlight max value leaving duplicates as above, we can adopt the cell address matching method. We will match the cell addresses of the range B2:H2 with the required max/large value cell address.

You can learn the same in 5 steps below. In the 5th step, you will get the formulas to highlight max value leaving duplicates in Google Sheets.

I’ll first give you the formula to highlight the largest 1 value without duplicates. Then we can modify that to the largest 2, and to the largest 3 leaving duplicates. Here we go!

Finding the Max | Large Value (Step 1)

Note: For sample data, please refer to the image above. The range is B2:H. Only keep the rows that you require for your project to improve the performance.

Since we are going to use the formula in conditional formatting, we only want to write the formula for the first row (B2:H2).

While applying the formula, just select the range B2:H in “Apply to range” in the conditional format panel to apply the same rule to subsequent rows.

Step_1_Formula:

=LARGE($B2:$H2,1)

It would return 319, which is Large 1 (Max) value in the range B2:H2.

Extracting the Cell Addresses from the Row (Step 2)

As I have explained, to highlight max value leaving duplicates, we are going to adopt cell address matching.

We can use the ADDRESS function as below (as an array formula) for this.

Step_2_Formula:

=ArrayFormula(address(row(),column($B$2:$H$2)))

In the above formula, I didn’t specify any cell address within the ROW function as I want the above formula to return the cell address of the row it keyed in.

If you insert the above formula in cell K2, it would return the below cell IDs in K2:Q2.

$B$2

$C$2

$D$2

$E$2

$F$2

$G$2

$H$2

If it’s in K4, it would be;

$B$4

$C$4

$D$4

$E$4

$F$4

$G$4

$H$4

So the formula for the range B2:H2 would be enough for an open range like B2:H in highlighting max values leaving duplicates in Google Sheets.

Filter the Cell ID of the Max Cell or Cells (Step 3)

To filter the cell address of the Large 1 or Max 1 value in a row we can use the below FILTER generic formula (as per my example).

=FILTER(
     Step_2_Formula,
     $B2:$H2=Step_1_Formula:,
     len($B2:$H2)
)

So the formula would be;

Step_3_Formula:

=FILTER(
     address(row(),column($B$2:$H$2)),
     $B2:$H2=LARGE($B2:$H2,1),
     len($B2:$H2)
)

Note: The ArrayFormula function removed from the step_2_formula here as it’s not required with FILTER.

The formula would return $B$2 in row 2, $B$3 (if you drag the formula from row 2 to row 3) in row 3…. $B$7 | $C$7 | $D$7 | $E$7 | $F$7 in row 7…

The above image is just for explanation. No need for you to key the formula as above in cell J2 and copy down.

To highlight max value leaving duplicates that also in row-wise we only want to use our step_5_formula in conditional format. That you will get after one more step.

Index to Extract the Correct Cell ID as per the Nth Max Value to Highlight (Step 4)

On the above image, you can see that there are multiple Max 1 values (duplicates) in row 7. So multiple cell addresses in Filter result.

We must make sure that we are only using one cell ID in conditional formatting. The Index is best for this.

Related: Select Only the Required Column from an Array Result in Google Sheets.

Index for Max 1 (Large 1)

Using the INDEX we can extract the first cell ID from the step_3_result.

Large_1_Formula:

=index(
     FILTER(
        address(row(),column($B$2:$H$2)),
        $B2:$H2=LARGE($B2:$H2,1),
        len($B2:$H2)
     ),
     0,1
)

The 0 in the last part means all rows and 1 means 1 column.

Index for Max 2 (Large 2)

If you want to highlight the Large 2 (Max 2) value leaving duplicates, in the above formula use the LARGE as below.

LARGE($B2:$H2,2)

Further you must change the column argument in Index from 1 to the below IF logical formula.

if(large($B2:$H2,2)=large($B2:$H2,1),2,1)

It means if Max 2 is equal to Max 1, return the second column from the cell IDs, else the first column from the cell IDs.

Large_2_Formula:

=index(
     FILTER(
        address(row(),column($B$2:$H$2)),
        $B2:$H2=LARGE($B2:$H2,2),
        len($B2:$H2)
     ),
     0,if(large($B2:$H2,2)=large($B2:$H2,1),2,1)
)

Index for Max 3 (Large 3)

Similarly for highlighting Max 3 leaving duplicates, use the below Large formula and the subsequent IF logical formula.

LARGE($B2:$H2,3)

IF Logical (Index Column Argument):

=IF(
     large($B2:$H2,3)=large($B2:$H2,1),3,
     if(large($B2:$H2,3)=large($B2:$H2,2),2,1
     )
)

If Max 3 is equal to Max 1, then this formula would return 3, if Max 3 is equal to Max 2, it would return 2, else 1.

The number (3, 2, or 1) returned by this formula is the column to extract in Index.

Large_3_Formula:

=index(
     FILTER(
        address(row(),column($B$2:$H$2)),
        $B2:$H2=LARGE($B2:$H2,3),
        len($B2:$H2)
     ),
     0,IF(
          large($B2:$H2,3)=large($B2:$H2,1),3,
          if(large($B2:$H2,3)=large($B2:$H2,2),2,1
          )
       )
)

Format Rule to Highlight Max | Large Value Leaving Duplicates (Step 5)

We are ready to apply the highlighting rule to highlight Max 1, Max 2, and Max 3 values leaving duplicates in Google Sheets.

We have 3 formulas. They are Large_1_Formula, Large_2_Formula, and Large_3_Formula. To apply the rules go to Format (menu) > Conditional Formatting.

In the “Conditional format rules” panel on the right, enter B2:H as “Apply to range”. The “Format rules” must be “Custom formula is”.

Now in the provided field (which will be blank), copy-paste the Large_1_Formula as per the below Generic formula.

=cell("address",B2)=Large_1_Formula

Set the background fill color to green and save. Similarly add another two rules.

Fill Color: Blue.

=cell("address",B2)=Large_2_Formula

Fill Color: Red.

=cell("address",B2)=Large_3_Formula

Note: Replace Large_1_Formula, Large_2_Formula, and Large_3_Formula with corresponding formulas.

I hope you could understand how to highlight max value leaving duplicates in row-wise in Google Sheets. If not, make a copy of my example sheet from the link below and see the formulas.

Thanks for the stay. Enjoy!

Example_Sheet_11620

Resources:

• How to Highlight Max Value in a Row in Google Sheets.
• How to Highlight Largest 3 Values in Each Row in Google Sheets.
• Conditional Format Max Two Values Based on Criterion in Google Sheets.
• Highlight Min Excluding Zero and Blank Cell in Google Sheets.
• How to Highlight the Smallest N Values in Each Row in Google Sheets.
• Skip Duplicates in Min | Small Value Highlighting Row Wise in Google Sheets.
• Google Sheets – Highlight the Max Value in Each Group.