How to Highlight Max Value in a Row in Google Sheets

Published on

Want to highlight max values like scores, amounts, or dates in a row or across multiple rows in Google Sheets? Since there’s no built-in rule for this, you can use a custom formula to achieve it. This formula works seamlessly with numbers, dates, and times and adjusts row-wise. Let’s dive into two examples to see how it works.

Example 1: Highlight Max Value in a Row

Assume you have the scores of one player in five attempts in the range B2:F2, and you want to highlight the max score. Here is how to do it:

  1. Select the range B2:F2.
  2. Click Format > Conditional Formatting.
  3. Select Custom Formula under Format Rules.
  4. Enter the following formula:=AND(B2<>"", B2=MAX($B2:$F2))
  5. Select the fill color or font color you want to apply to the max value.
  6. Click Done.

This will highlight the max value in the row.

Highlighting the max value in a row in Google Sheets

Formula Breakdown

The formula follows the syntax:

AND(logical_expression1, [logical_expression2, ...])
  • logical_expression1: B2<>"" – Ensures the cell is not empty.
  • logical_expression2: B2=MAX($B2:$F2) – Checks if the value in the cell equals the max value in the range.

If the row is empty, no highlighting will be applied.

Example 2: Highlight the Highest Value in Each Row

If you want to highlight the max value in each row, simply extend the “Apply to range” selection. No changes to the formula are needed.

For example, assume three players participated in a game and attempted five times each. The scores are in the range B2:F4. Here’s what to do:

  1. Select the range B2:F4.
  2. Click Format > Conditional Formatting.
  3. Use the same formula:=AND(B2<>"", B2=MAX($B2:$F2))
  4. Select your desired highlight color.
  5. Click Done.

The formula will automatically adjust for each row because the MAX function calculates the max value in each row, as the column reference is absolute and the row reference is relative.

Highlighting the highest value in each row in Google Sheets

Additional Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.