# 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 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.

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.

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

- 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…**

- 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.

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

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

## 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.

- 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…**

- 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).

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

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:

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

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

`=B2=MAX($B$2:$B$10)`

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

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.

## 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:

`=B2=MIN($B$2:$B$10)`

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