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. You can easily do that using Conditional Formatting. It’s an easy way to apply a format to a cell based on its value.
In this tutorial, I will show you how to use Conditional Formatting in Google Sheets to highlight the highest value (max value), the lowest value (min value), and the top three or bottom three values in the data set. So let’s get started!
Table of Contents
How To Use Google Sheets to Highlight the Highest Value
Suppose I have a data set with students’ names and 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:
- Select the range that has the scores.
- Click the Format option in the menu.
- Click on Conditional Formatting.
- In the “Conditional format rules” pane that opens, make sure the “Single color” option is selected.
- Ensure the range is correct (if not, you can change it here).
- In the “Format” rules drop-down, select “Custom formula is.”
- Enter the following formula in the field:
=B2=MAX($B$2:$B$21)
- Specify the color in which you want to highlight the cell (I will go with 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. It goes through each cell in the specified range and analyzes its value using the formula you specified.
If the result of the formula is TRUE, then Conditional Formatting will 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, analyzes the score, and checks whether the score is equal to the maximum score in the list or not. Only the cell with 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 this case, if multiple cells have the maximum value, all those cells would be highlighted.
How To Use Google Sheets to Highlight the Lowest Value
Just like I have highlighted Google Sheets’ 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 want to highlight the lowest score in column B.
Below are the steps to do this:
- Select the range that has the scores.
- Click the “Format” option in the menu.
- Click on “Conditional Formatting.”
- In the “Conditional format rules” pane, make sure “Single color” is selected.
- Ensure the range is correct (if not, you can change it).
- In the “Format rules” drop-down, select “Custom formula is.”
- Enter the following formula in the field:
=B2=MIN($B$2:$B$21)
- 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 with 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
How To Highlight the Top or Bottom Three Values
Now, let’s say instead of highlighting the highest or lowest value, you want to highlight the top or bottom three 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 want to highlight the top three scores in this data.
Below are the steps to do this:
- Select the range that has the scores.
- Click the “Format” option in the menu.
- Click on “Conditional Formatting.”
- In the “Conditional format rules” pane, make sure “Single color” is selected.
- Ensure the range is correct (if not, you can change it here).
- In the “Format rules” drop-down, select “Custom formula is.”
- Enter the following formula in the field:
=B2>=LARGE($B$2:$B$21,3)
- Specify the color in which you want to highlight the cell (I will go with 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 checks each cell and sees whether the value is greater than or equal to the third-highest value or not. If it is, then Conditional Formatting highlights the cells, or else, it remains unaffected.
In case you want to highlight the lowest three scores, you can use the below formula in Step 7:
=B2<=SMALL($B$2:$B$21,3)
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. Just make sure that the formatting is different (for example, green color for the top three values and orange or yellow color for the bottom three).
How To Highlight the Entire Row with the Highest or 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 and the student’s name? You can easily do that by modifying the Conditional Formatting formula.
Suppose you have the data set as shown below and want to highlight the record for the highest score in this data.
Below are the steps to do this:
- Select the entire dataset (A2:B21 in this example).
- Click the “Format” option in the menu.
- Click on “Conditional Formatting.”
- In the “Conditional format rules” pane, select the “Single color” option.
- Ensure the range is correct (if not, you can change it here).
- In the “Format” rules drop-down, select “Custom formula is.”
- 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 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 references that you can use in Google Sheets:
- B2 – This is a relative reference that does not have any dollar signs ($).
- B$2 / $B2 – These are mixed references that have one dollar sign ($), either before the column letter or before the row number.
- $B$2 – This is an absolute reference where the column letter and the row number have a dollar sign ($) before it.
In the above example, we have used $B2, which is a mixed reference. This means that while Conditional Formatting analyzes 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 formula above.
Now, when it goes to cell A3, it uses the below formula, and when it goes to cell B3, it again uses the above formula:
=$B3=MAX($B$2:$B$21)
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.
Wrapping Up
So, this is how you can use simple Conditional Formatting formulas in Google Sheets to highlight the highest or lowest value in a data set (or the top N or bottom N values). I hope you found this tutorial useful. Feel free to leave your comments below!
Other Google Sheets tutorials you may also like:
