This article will explain how to highlight the min and max in an Excel chart with a different color.
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:
Step 3: Formula to return the maximum
In the same way, we will build another test in another column to return the maximum value.
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
- Double-click one of the series bars to open the options panel
- Change the overlay value to 100% overlap
- 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.
- Select the series Min from the dropdown list Series Options.
- Select the Min Series
- Change the color
- Repro the steps for the Max series
Finally, your chart looks like this 😀👍