Tip: Other languages are Google-Translated. You can visit the English version of this link.
or

Register

or
0
0
0
s2smodern

## How to highlight or conditional formatting the max or min value in Google sheet?

Normally, you can quickly highlight the largest or smallest value by using Conditional Formatting in Microsoft Excel. But, you may not find a similar feature in Google sheet. Here, I will introduce how to highlight the max or min value in Google sheet?

Highlight the largest or smallest value with Conditional Formatting in Google sheet

Highlight the top n or bottom n values with Conditional Formatting in Google sheet

#### Highlight the largest or smallest value with Conditional Formatting in Google sheet

###### Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

To highlight the max or min value from a list in Google sheet, please do as follows:

1. Click Format > Conditional formatting, see screenshot:

2. In the Conditional format rules pane, under the Single color tab, do the following operations:

(1.) Click button under the Apply to range section to select the data column that you want to highlight the max or min value from;

(2.) Then choose Custom formula is from the Format cells if drop down list, and then enter this formula: =\$B:\$B=max(B:B) into the text box to highlight the largest value from column B;

Note: To highlight the smallest value, please enter this formula: =\$B:\$B=min(B:B).

(3.) And then choose one formatting style to highlight the cell from the Formatting style section as you need.

3. And the largest or smallest value has been highlighted at once, then click Done button to close the pane.

#### Highlight the top n or bottom n values with Conditional Formatting in Google sheet

If you need to highlight the top / bottom 3 or 5 or n values, the following steps may help you.

1. Go to the Conditional format rules pane by clicking Format > Conditional formatting.

2. In the Conditional format rules pane, do the following options:

(1.) Click button under the Apply to range section to select the data column that you want to highlight the top / bottom n values from;

(2.) Then choose Custom formula is from the Format cells if drop down list, and then enter this formula: =\$B2>=large(\$B\$2:\$B,3) into the text box to highlight the top three values from column B;

Note: To highlight the bottom three values, please enter this formula: =\$B2<=small(\$B\$2:\$B,3).

(3.) And then choose one formatting to highlight the cells from the Formatting style section as you need.

3. And the top or bottom 3 values have been highlighted immediately, then click Done button to close the pane as you need.

### Recommended Productivity Tools

#### Office Tab

Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

#### Kutools for Excel

Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

200 New Features for Excel, Make Excel Much Easy and Powerful:

• Merge Cell/Rows/Columns without Losing Data.
• Combine and Consolidate Multiple Sheets and Workbooks.
• Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
• Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,

Say something here...
symbols left.