How To Highlight the Highest or Lowest Value in Google Sheets (Easy Guide)

Don’t feel like reading? Watch the video tutorial:

When working with data in Google Sheets, you sometimes need to highlight the highest value (maximum) or the lowest value (minimum) in a dataset at a glance. Conditional Formatting in Google Sheets makes this easy by applying a format to any cell that meets the condition you define.

In this tutorial, I will show you how to highlight the highest value, the lowest value, the top three or bottom three values, an entire row based on the highest value, and the highest value within each row. I also include a quick-reference table of every formula covered so you can copy exactly what you need.

=B2=MAX($B$2:$B$21)

Formula Quick-Reference Table

How to Highlight the Highest Value in Google Sheets

Suppose you have a dataset with students’ names and scores, and you want to highlight the highest score.

Here are the steps to highlight the highest value using Conditional Formatting:

1. Select the data range that has the scores.
2. Click the Format option in the dropdown menu.
   
3. Click on Conditional Formatting.
   
4. In the Conditional format rules pane that opens, confirm the Single color option is selected.
   
5. Confirm the range is correct. You can edit it directly in this pane if needed.
6. In the Format rules drop-down, select Custom formula is.
   
7. Enter the following formula in the field:

   =B2=MAX($B$2:$B$21)

   

8. Choose the color you want to apply. The default green works well for highlighting a maximum value. If you don’t want to fill green, choose another color with the paint bucket.
9. Click Done.

The cell with the highest value is now highlighted.

How Does This Work?

In Conditional Formatting, the custom formula must return either TRUE or FALSE. Google Sheets evaluates every cell in your selected range against that formula. When the formula returns TRUE, the formatting applies. When it returns FALSE, the cell is left unchanged.

In the formula above, Google Sheets visits each cell, checks whether the score in that cell equals the maximum score in the entire range, and highlights only the cell where that condition is true. If two or more cells share the max value, all of them will be highlighted. That is intentional behavior and exactly what you would expect.

How to Highlight the Lowest Value in Google Sheets

Highlighting the lowest value follows the same process. The only change is swapping MAX for MIN in the formula.

Suppose you have the same dataset and want to highlight the lowest score in column B.

1. Select the range that has the scores.
2. Click Format in the menu.
3. Click Conditional Formatting.
4. In the Conditional format rules pane, confirm Single color is selected.
5. Confirm the range is correct.
6. In the Format rules drop-down, select Custom formula is.
7. Enter the following formula:

   =B2=MIN($B$2:$B$21)

   

8. Choose a color that signals a low value. Red works well here for contrast with the green used on the maximum.
   
9. Click Done.

The cell with the lowest score is now highlighted in red.

The same TRUE/FALSE logic applies here. Only the cell whose value equals the minimum value in the range returns TRUE, so only that cell receives the formatting. If two students share the same lowest score, both cells are highlighted.

Pro tip: You can run both rules on the same range at the same time. Apply the MAX rule with a green fill and the MIN rule with a red fill, and Google Sheets highlights both the highest and lowest values simultaneously without any conflict.

How to Highlight the Top or Bottom N Values

Instead of highlighting just the single highest or lowest value, you can highlight any number of top or bottom values. The logic stays the same. You just need a formula that returns TRUE for the cells that fall within your target range.

Suppose you want to highlight the top three scores in the dataset.

1. Select the range that has the scores.
2. Click Format in the menu.
3. Click Conditional Formatting.
4. In the Conditional format rules pane, confirm Single color is selected.
5. Confirm the range is correct.
6. In the Format rules drop-down, select Custom formula is.
7. Enter the following formula:

   =B2>=LARGE($B$2:$B$21,3)

   

8. Choose a highlight color and click Done.

The top three scores are now highlighted.

How Does the LARGE Formula Work?

The LARGE function returns the Nth largest value in a range. LARGE($B$2:$B$21,3) returns the third-highest score. The Conditional Formatting formula then checks whether each cell’s value is greater than or equal to that third-highest score. Any cell that meets the condition gets highlighted, which gives you the top three values.

To highlight the bottom three scores instead, use the SMALL function, which works the same way in reverse:

=B2<=SMALL($B$2:$B$21,3)

To highlight both the top three and bottom three at the same time, apply two separate Conditional Formatting rules to the same range: one using the LARGE formula with green fill, and one using the SMALL formula with a different color such as orange or yellow.

Make the N Value Dynamic

If you regularly change how many top or bottom values you want to highlight, hardcoding the number 3 in the formula means editing Conditional Formatting every time. A more flexible approach stores the number in a cell, say E1, and references it in the formula:

=B2>=LARGE($B$2:$B$21,$E$1)

Now you can type any number into cell E1 and the highlighting updates instantly across the entire range without touching the Conditional Formatting rule.

How to Highlight the Entire Row with the Highest or Lowest Value

So far, these examples only highlight the cell that contains the score. But what if you want to highlight the entire record, including the student’s name? This requires one small change to the formula: a mixed reference.

Suppose you want to highlight the full row for the student with the highest score.

1. Select the entire dataset, for example A2:B21.
2. Click Format in the menu.
3. Click Conditional Formatting.
4. In the Conditional format rules pane, select Single color.
5. Confirm the range covers both columns.
6. In the Format rules drop-down, select Custom formula is.
7. Enter the following formula:

   =$B2=MAX($B$2:$B$21)

   

8. Choose a highlight color and click Done.

The entire row for the student with the highest score is now highlighted.

Understanding Mixed References

The key difference in this formula is the dollar sign placement. Here is how the three reference types work in Google Sheets:

• B2 — A relative reference. Both the column and row shift as Google Sheets moves through each cell.
• $B2 — A mixed reference. The column is locked to B, but the row number adjusts as Google Sheets moves down.
• $B$2 — An absolute reference. Both the column and row are fully locked.

Using $B2 ensures that even when Google Sheets evaluates the name cell in column A, the formula still checks the score in column B for that same row. Without the dollar sign on the column, the formula would check column A’s value against the max, which would never match and nothing would highlight.

This is one of the most important concepts to understand when building any row-level Conditional Formatting rule.

How to Highlight the Highest Value in Each Row

Sometimes your data is laid out with categories or subjects across multiple columns, and you want to highlight the best result within each individual row rather than across the entire dataset. This is common in scorecards, comparison tables, and multi-subject grade sheets.

Suppose your data spans columns B through E, with one row per student and one column per subject. To highlight the highest value in each row:

1. Select the full data range, for example B2:E21.
2. Click Format in the menu, then click Conditional Formatting.
3. In the Format rules drop-down, select Custom formula is.
4. Enter the following formula:

   =B2=MAX($B2:$E2)

5. Choose a highlight color and click Done.

The formula uses a mixed reference on the range ($B2:$E2) to lock the columns while allowing the row number to shift downward as Google Sheets evaluates each row. The result is that only the highest value in each individual row gets highlighted, regardless of how it compares to scores in other rows.

Color Scale: A Useful Alternative

If you want to visualize the full spectrum of values rather than pinpoint specific highs and lows, Google Sheets includes a built-in Color Scale option under Conditional Formatting. Instead of highlighting a single cell, color scale applies a gradient across the entire range: typically red for low values, yellow for mid-range, and green for high values.

To use it, open the Conditional Formatting pane and click the Color scale tab instead of Single color. No custom formula is required.

The custom formula approach covered throughout this tutorial works best when you need to call out a specific value, such as the single highest score or the exact top three. Color scale works best when you want a visual overview of the full distribution at once.

Frequently Asked Questions

Wrapping Up

Conditional Formatting in Google Sheets gives you a fast, formula-driven way to highlight the highest value, lowest value, top N values, bottom N values, or the best result in each row of your dataset. The core logic is simple: write a formula that returns TRUE for the cells you want to format, choose a color, and Google Sheets does the rest.

The mixed reference concept covered in the row-highlighting section is one of the most transferable skills in Google Sheets. Once you understand the difference between B2, $B2, and $B$2, you can apply it to any Conditional Formatting rule that spans multiple columns.

Leave a comment below if you have any questions.

Other Google Sheets tutorials you may also like:

• How to Highlight Duplicates in Google Sheets
• How to Alphabetize in Google Sheets
• Count Cells Based on Cell Color in Google Sheets
• How to Copy Conditional Formatting in Google Sheets
• Apply Conditional Formatting Based on Another Cell Value in Google Sheets