See all How-To Articles

How to Highlight the Highest Value in Excel & Google Sheets

This tutorial demonstrates how to highlight the highest value in a range in Excel and Google Sheets.

 

highlight highest value final data

 

Highlight the Highest Value

In Excel, you can use Conditional Formatting to highlight the highest value or the top n values in a range. For this example, let’s start with the data below in Column B.

 

highlight highest value initial data

 

Say you want to highlight the cell with the highest value in green. Since 49 is the highest number in Column B, cell B4 should be highlighted.

  1. Select the data range where you want to highlight the highest value. Then in the Ribbon, go to Home > Conditional Formatting > New Rule…

 

highlight highest value conditional formatting

 

  1. In the conditional formatting window, (1) select Format only top or bottom ranked values for Rule Type. Then (2) enter 1 next to Top (as you want one value to be highlighted – the highest), and (3) click Format…

 

highlight highest value conditional formatting 2

 

  1. In the Format Cells window, (1) go to the Fill tab, (2) choose green and (3) click OK.
    Note that you can also go to the Font, Border, and/or Number tabs to adjust other formatting options.

 

highlight highest value conditional formatting 3

 

  1. Back in the New Formatting Rule window, there’s a Preview of the cell formatting this rule will apply. Click OK.

 

highlight highest value conditional formatting 4

 

Now cell B4 is highlighted green, because 49 is the highest value in the range B2:B10.

 

highlight highest value final data

 

Highlight Top n Values

You can also use Conditional Formatting to highlight the top n values in a range. Let’s highlight the top three values in the range B2:B10.

  1. Select the data range for which you want to highlight the top three values and in the Ribbon, go to Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items…

 

highlight top n values

 

  1. The default setting in the pop-up window is to format the top 10 items, but you only want three.
    (1) Enter 3 for the top cells, and (2) click OK, leaving the default formatting (Light Red Fill with Dark Red Text).

 

highlight top n values 2

 

The top three values in the data range (49, 32, and 21) are highlighted.

 

highlight top n values final data

 

If you look back at Step 1, you can see that in the Top/Bottom Rules, there is also an option for Bottom 10 Items. Select that option to highlight the bottom n values in the range.

Highlight the Highest Value in Google Sheets

To highlight the highest value in a range in Google Sheets, follow these steps:

  1. Select the data range for which you want to highlight the highest value and in the Menu, go to Format > Conditional formatting.

 

google sheets highlight highest value 1

 

  1. In the conditional formatting window on the right, (1) choose Custom formula is under Format rules, and (2) enter the formula:

Finally, (3) click OK, leaving the default formatting style (green background fill). This can be changed later by clicking on the Fill color icon.

 

google sheets highlight highest value 2

 

Conditional formatting based on the formula above will be applied if the result of the formula is TRUE. In this case, it’s checking whether each cell in the range is equal to the maximum value in the range (the result of the MAX Function). Since the highest value is in cell B4, the result of the formula for that cell is TRUE, and the B4 cell is highlighted.

 

google sheets highlight highest value final data

 

Highlight the Lowest Value in Google Sheets

You can also adjust the formula in Step 2 to highlight the lowest value in a range. To do this, follow the steps for highlighting the highest value above, but use the MIN Function instead:

 

google sheets highlight lowest value

 

Now, the formula will return TRUE for cell B7, as 2 is the lowest number in the range B2:B10.

 

google sheets highlight lowest value final data