When working in Google Sheets there may be times where you want to quickly find what the highest value is in a range or column.
To make this value stand out, what might be best is to highlight it. This can easily be done with conditional formatting in Google Sheets.
In this tutorial, I will show you how to highlight the highest value in Google Sheets using conditional formatting. I will also show variations of this, such as highlighting the top values and lowest values which will be a pretty similar process.
Table of Contents
Highlight Highest Value
Highlighting the highest value in a range or column is a pretty simple process.
To show you how this is done, I will be highlighting the highest value in this example dataset using conditional formatting:
Here are the steps to do this:
1. Highlight the range for which you want to highlight the highest value in
2. In the top menu select Format
3. From the drop-down menu select Conditional formatting
4. Under the Format rules section, set the “Format cells if” area to Custom formula is
5. Next we need to enter our custom formula that will highlight the max value in our range. The formula for my example data is =B2=MAX($B$2:$B$8)
6. Select the color that you want the highest value to highlight to, and then select the Done button when you are finished
7. You should now see the highest value within your range has been highlighted the color that you chose in the previous setp
Highlight Lowest Value
Now that you know the process for highlighting the highest value, essentially you also know how to highlight the lowest value.
The process is the exact same, except the custom formula we enter changes to use MIN instead of MAX.
Here is the new formula that we enter into the conditional formatting settings:
=B2=MIN($B$2:$B$8)
After entering this formula, the lowest value is now highlighted instead of the highest:
Highlight Top 5 Values
You have a ton of other options for highlighting values that you can do by changing the formula in the Custom formula is setting.
For example, if you want to highlight the top 5 values in your range, you can do so by using this formula:
=B2>=LARGE($B$2:$B$8,5)
Here is how this looks in my spreadsheet:
Notice how this time the top 5 values are highlighted:
If you want to change how many values you are highlighted, you would change the last argument in the custom formula from 5 to whatever number you wanted to highlight.
Highlight 5 Lowest Values
You can also highlight the lowest values by making some small changes to your formula. Here is the formula I will use to highlight the 5 lowest values in my dataset:
=B2<=SMALL($B$2:$B$8,5)
Again you can set this to be whatever number you want instead of 5 by changing the last argument to a different number.
Here is how this looks in my spreadsheet:
Closing Thoughts
By using conditional formatting in combination with custom formulas, there are many different ways you can highlight your data to make certain numbers stand out.
Whether you want to highlight the max value, minimum value, top values, or lowest values, all of this can be done by learning to use custom formula settings.
Try following along with the steps outlined in this tutorial in your own spreadsheet so that you can learn how to implement these in your own sheets in the future.
More Google Sheets Tutorials:
How to Color Code
How to Make Negative Numbers Red
How to Color a Drop-Down List