How to Highlight Highest or Lowest Value in Google Sheets (Easy Way)

Don’t feel like reading? View our video tutorial:

When working with data in Google Sheets, you may have a need to highlight the highest value (the maximum value) or the lowest value (minimum value) in the data set.

And you can easily do that using Conditional Formatting. It’s an easy way to apply a format to a cell based on the value in it.

In this tutorial, I will show you how to use conditional formatting to highlight the highest value (max value), highlight the lowest value (min value), as well as highlight the top 3 the bottom 3 values in the data set.

So let’s get started!

Highlight the Highest Value in Google Sheets

Suppose I have the data set with students’ name and their scores in a subject, and I want to highlight the highest score in this data set.

Here is how you can do that using conditional formatting:

1. Select the range that has the scores
2. Click the Format option in the menu
3. Click on Conditional Formatting
4. In the ‘Conditional format rules’ pane that opens, make sure ‘Single color’ is selected
5. Make sure that the range is correct (if not, you can change it from here)
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. Specify the color in which you want to highlight the cell (I will go the default green color)

The above steps would highlight the cell that has the highest value.

How does this work?

In Conditional Formatting, you can use a custom formula that returns a TRUE or a FALSE.

Conditional Formatting goes through each cell in the specified range and analyzes that cell’s value using the formula that you have specified.

If the result of the formula is TRUE, then conditional formatting is going to apply the specified format, and if the result of the formula is FALSE, then conditional formatting will do nothing.

In our example above, it goes through each cell and analyzes the score, and checks whether the score is equal to the maximum score in the list or not. Only the cell that has the maximum score would return TRUE, and all the other cells would return FALSE.

This is why only the cell that has the maximum value gets highlighted in green color (which was the format that we specified in conditional formatting).

In case there are multiple cells that have the maximum value, all those cells would be highlighted.

Highlight the Lowest Value in Google Sheets

Just like I have highlighted the highest value in a data set, I can also highlight the lowest value in the data.

All it needs is a minor tweak in the formula.

Suppose you have a data set as shown below and you want to highlight the lowest score in column B.

Below are the steps to do this:

1. Select the range that has the scores
2. Click the Format option in the menu
3. Click on Conditional Formatting
4. In the ‘Conditional format rules’ pane that opens, make sure ‘Single color’ is selected
5. Make sure that the range is correct (if not, you can change it from here)
6. In the Format rules drop-down, select ‘Custom formula is’
7. Enter the following formula in the field:
   =B2=MIN($B$2:$B$21)
8. Specify the color in which you want to highlight the cell. In this case, I will go with a red shade color

The above steps would highlight the cell that has the lowest (minimum) value.

This again works just like I explained in the above section, where conditional formatting goes through each cell and checks that cell’s value against the formula.

Only the cell that has the minimum value would return TRUE for that formula and would get highlighted, and all the other cells would remain unaffected

Highlight the Top 3 or Bottom 3 Values in Google Sheets

Now, let’s say, instead of highlighting the highest or the lowest value, you want to highlight the top 3 values or the bottom 3 values.

The process and the logic remain the same.

All you need to do is tweak the formula in such a way that it would return TRUE for the top three values (or bottom three values) and highlight these cells using the specified format.

Suppose you have a data set as shown below and you want to highlight the top three scores in this data.

Below are the steps to do this:

1. Select the range that has the scores
2. Click the Format option in the menu
3. Click on Conditional Formatting
4. In the ‘Conditional format rules’ pane that opens, make sure ‘Single color’ is selected
5. Make sure that the range is correct (if not, you can change it from here)
6. In the Format rules drop-down, select ‘Custom formula is’
7. Enter the following formula in the field:

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

   

8. Specify the color in which you want to highlight the cell (I will go the default green color)

The above steps would highlight the top three scores in Column B.

The LARGE formula allows us to get the Nth largest value. For example, you can use it to get the second-highest value or the third-highest value in a data set.

In our example, since we want to highlight the top three values, I have used the large formula to give me the third-highest score.

The formula now simply checks each cell and see whether the value is greater than or equal to the third-highest value or not. If it is, then conditional formatting highlights the cells, else it remains unaffected.

In case you want to highlight the lowest 3 scores, you can use the below formula in Step 7:

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

And in case you want to highlight the top three scores as well as the bottom three scores, you need to apply conditional formatting twice with both the LARGE and the SMALL formula.

And make sure that the formatting is different (for example, green color for the top three values and orange or yellow color for the bottom 3).

Highlight the Entire Row with Highest/Lowest Value

In the examples covered so far, we have only highlighted the cells that have the scores.

But what if you want to highlight the entire record along with the name of the student.

You can easily do that by modifying the conditional formatting formula a little.

Suppose you have the data set as shown below and you want to highlight the record for the highest score in this data.

Below are the steps to do this:

1. Select the entire dataset (A2:B21 in this example)
2. Click the Format option in the menu
3. Click on Conditional Formatting
4. In the ‘Conditional format rules’ pane that opens, make sure ‘Single color’ is selected
5. Make sure that the range is correct (if not, you can change it from here)
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)

   

The above steps would highlight the entire record for the student that has scored the highest.

How does this formula work?

While this formula works the same way as the rest of the ones covered above, there is one minor difference that you need to understand.

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

In the above formula, instead of using B2, I have used $B2. This is called a mixed reference.

Below are the three kinds of reference that you can use in Google Sheets:

• B2 – this is a relative reference that does not have any $ sign
• B$2 / $B2 – these are mixed references that have one $ (either before the column letter or before the row number)
• $B$2 – this is an absolute reference where both the column letter and the row number has a dollar sign before it

In the above example, we have used $B2, which is a mixed reference.

This means that while conditional formatting is analyzing all the cells in each row when it goes to cell A2, it would use the following formula to check whether it returns a true or false:

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

And when it goes to B2, it again uses the same above formula.

Now, when it goes to cell A3, it uses the below formula:

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

and when it goes to cell B3, it again uses the above formula.

By adding a dollar sign before the column letter, we have ensured that even when the cells in the column are being analyzed the formula still refers to the value in column B. This is done because even when the name has to be highlighted we still need to analyze the score for that person.

So this is how you can use simple conditional formatting formulas to highlight the highest or the lowest value in a data set (or the top N or bottom N values).

I hope you found this tutorial useful.

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 the Cell Color in Google Sheets
• How to Copy Conditional Formatting in Google Sheets
• How to Use Color Scale in Google Sheets
• Apply Conditional Formatting based on Another Cell Value in Google Sheets