Skip to Content

How to Highlight Highest Value in Google Sheets

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.

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:

highlight-highest-example-data

Here are the steps to do this:

1. Highlight the range for which you want to highlight the highest value in

highlight-highest-1

2. In the top menu select Format

highlight-highest-2

3. From the drop-down menu select Conditional formatting

highlight-highest-3

4. Under the Format rules section, set the “Format cells if” area to Custom formula is

highlight-highest-4

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)

highlight-highest-5

6. Select the color that you want the highest value to highlight to, and then select the Done button when you are finished

highlight-highest-6

7. You should now see the highest value within your range has been highlighted the color that you chose in the previous setp

highlight-highest-7

 

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)

highlight-lowest-1

After entering this formula, the lowest value is now highlighted instead of the highest:

highlight-lowest-2

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:

highlight-top-values

Notice how this time the top 5 values are highlighted:

highlight-top-values-2

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:

highlight-lowest-values

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