How to display Min and Max in an Excel chart?

How to display Min and Max in an Excel chart?
Last Updated on 09/07/2023
Reading time: 2 minutes

This article will explain how to highlight the min and max in an Excel chart with a different color.

Chart with Min and Max

Original chart

Let's start with this chart. Here, we have the attendance of a restaurant for breakfast.

Attendance of a restaurant

Step 1: Add a Min and Max column

The first thing to do is to add 2 new columns

  • One to display the minimum
  • Another one to display the maximum

Step 2: Formula for the Minimum

Here, we need to create a logical test if the value of each cell is equal to the minimum of the range. The formula is:

=IF(B2=MIN($B$2:$B$31); B2;0)

Formula to return the minimum of the series

Step 3: Formula to return the maximum

In the same way, we will build another test in another column to return the maximum value.

=IF(B2=MAX($B$2:$B$31); B2;0)

Formula to return the maximum of the series

Step 4: Add the series to the chart

Then, we add the min and max series into the Excel chart

  • You can do this by adding the series from the menu Select Data
Menu select data
  • Or, use the following technique by copying and pasting for each of the series

But there is a problem! The 3 series are next to each other by default.

The bar arent overlapped

Step 5: Overlay the bars

So, we must overlap the three series, and it's easy to do

  1. Double-click one of the series bars to open the options panel
  2. Change the overlay value to 100% overlap
  3. And also, change the Gap Width to have bigger bars 😉
Change series overlap and gap width

And now, your chart looks like this

Min and Max bar with the standard color

Step 6: Change the color

To finish, we must change the color for the min and max.

  1. Select the series Min from the dropdown list Series Options.
  2. Select the Min Series
  3. Change the color
  4. Repro the steps for the Max series
Series selected from the drop down list

Finally, your chart looks like this 😀👍

Chart with Min and Max

Leave a Reply

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

How to display Min and Max in an Excel chart?

Reading time: 2 minutes
Last Updated on 09/07/2023

This article will explain how to highlight the min and max in an Excel chart with a different color.

Chart with Min and Max

Original chart

Let's start with this chart. Here, we have the attendance of a restaurant for breakfast.

Attendance of a restaurant

Step 1: Add a Min and Max column

The first thing to do is to add 2 new columns

  • One to display the minimum
  • Another one to display the maximum

Step 2: Formula for the Minimum

Here, we need to create a logical test if the value of each cell is equal to the minimum of the range. The formula is:

=IF(B2=MIN($B$2:$B$31); B2;0)

Formula to return the minimum of the series

Step 3: Formula to return the maximum

In the same way, we will build another test in another column to return the maximum value.

=IF(B2=MAX($B$2:$B$31); B2;0)

Formula to return the maximum of the series

Step 4: Add the series to the chart

Then, we add the min and max series into the Excel chart

  • You can do this by adding the series from the menu Select Data
Menu select data
  • Or, use the following technique by copying and pasting for each of the series

But there is a problem! The 3 series are next to each other by default.

The bar arent overlapped

Step 5: Overlay the bars

So, we must overlap the three series, and it's easy to do

  1. Double-click one of the series bars to open the options panel
  2. Change the overlay value to 100% overlap
  3. And also, change the Gap Width to have bigger bars 😉
Change series overlap and gap width

And now, your chart looks like this

Min and Max bar with the standard color

Step 6: Change the color

To finish, we must change the color for the min and max.

  1. Select the series Min from the dropdown list Series Options.
  2. Select the Min Series
  3. Change the color
  4. Repro the steps for the Max series
Series selected from the drop down list

Finally, your chart looks like this 😀👍

Chart with Min and Max

Leave a Reply

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