How to Highlight Min and Max values in Excel

How to Highlight Min and Max values in Excel
Last Updated on 14/09/2023
Reading time: 3 minutes

How to highlight the Min and the Max with color in Excel? And what happens when you update your values? Do the min and the max will change? The conditional formatting tool of Excel does exactly this job.

Highlight the Min and the Max with color in Excel

  1. Create a conditional formatting rule to highlight the maximum

    Select the option to highlight the 10 highest items and change the settings

  2. Create a conditional formatting rule to highlight the minimum

    Select the option to highlight the 10 lowest items and change the settings

What is conditional formatting?

In Excel, if you want your colors to be displayed according to a ruler, you must use the conditional formatting tool.

You can find conditional formatting options on the Home ribbon.

Menu Conditional formatting

The tool is extremely powerful and allows you to elaborate formatting like

How to add a color for the maximum?

In this document, we have the sales of the week. How to highlight the maximum of the sales?

How to highlight the min and the max
  1. Select the range of cells containing the values where to apply color for the maximum; here H4:H11
  2. Enable the Conditional Formatting menu > Top/Bottom Rules > Top 10 Items.
Menu to hightlight the top 10 items
  1. In the dialog box, replace 10 with 1 and choose the format Green.
Parameter to highlight the maximum in green
  1. When you press OK, the maximum value has now a specific color instantly 😀👍
The Max value is highlighted

Change the color for the minimum.

The method is the same except for step 2. There, you must select the option Bottom 10 items.

Menu to hightlight the bottom 10 items

Again, in the dialog box change the default value 10 by 1 and select the Red format.

The Min value is Highlighted

What is the impact of new values on the color of the minimum and maximum?

Let's add the values of Saturday to our table. The Total of the week has changed for each employee.

  • Without any action from our side, Excel changes the color of the minimum and maximum automatically.
  • This is the very principle of conditional formatting, coloring according to a ruler.
New values for the min and the max after the update

Customize color

You can customize the format by selecting the Custom menu from the drop-down menu.

Option custom format for the minimum or maximum

And then change the color and font settings.

Customize any part of a cell

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *

How to Highlight Min and Max values in Excel

Reading time: 3 minutes
Last Updated on 14/09/2023

How to highlight the Min and the Max with color in Excel? And what happens when you update your values? Do the min and the max will change? The conditional formatting tool of Excel does exactly this job.

Highlight the Min and the Max with color in Excel

  1. Create a conditional formatting rule to highlight the maximum

    Select the option to highlight the 10 highest items and change the settings

  2. Create a conditional formatting rule to highlight the minimum

    Select the option to highlight the 10 lowest items and change the settings

What is conditional formatting?

In Excel, if you want your colors to be displayed according to a ruler, you must use the conditional formatting tool.

You can find conditional formatting options on the Home ribbon.

Menu Conditional formatting

The tool is extremely powerful and allows you to elaborate formatting like

How to add a color for the maximum?

In this document, we have the sales of the week. How to highlight the maximum of the sales?

How to highlight the min and the max
  1. Select the range of cells containing the values where to apply color for the maximum; here H4:H11
  2. Enable the Conditional Formatting menu > Top/Bottom Rules > Top 10 Items.
Menu to hightlight the top 10 items
  1. In the dialog box, replace 10 with 1 and choose the format Green.
Parameter to highlight the maximum in green
  1. When you press OK, the maximum value has now a specific color instantly 😀👍
The Max value is highlighted

Change the color for the minimum.

The method is the same except for step 2. There, you must select the option Bottom 10 items.

Menu to hightlight the bottom 10 items

Again, in the dialog box change the default value 10 by 1 and select the Red format.

The Min value is Highlighted

What is the impact of new values on the color of the minimum and maximum?

Let's add the values of Saturday to our table. The Total of the week has changed for each employee.

  • Without any action from our side, Excel changes the color of the minimum and maximum automatically.
  • This is the very principle of conditional formatting, coloring according to a ruler.
New values for the min and the max after the update

Customize color

You can customize the format by selecting the Custom menu from the drop-down menu.

Option custom format for the minimum or maximum

And then change the color and font settings.

Customize any part of a cell

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *