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

Original chart

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

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)

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)

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

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

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 😉

And now, your chart looks like this

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

Finally, your chart looks like this 😀👍