Log in
x
or
x
x
Register
x

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


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

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

Kutools for Excel

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

gold star1 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,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.