Excel Unplugged

Making your Min Max chart in Excel truly shine

Just so we know where this is heading, this is the end result

Min Max chart in Excel

And here is the file with the end result, you can download it and play around with pressing F9.

Here’s how we make it work.

We start with the data. Now we need to add two columns that will only show the min or max value and have blanks on other occasions.

Min Max chart in Excel

The two formulas will be

MIN: =IF(B2=MIN($B$2:$B$13),B2,””) or if you write a table formula

MIN(Table): =IF([@Number]=MIN([Number]),[@Number],””)

MAX: =IF(B2=MAX($B$2:$B$13),B2,””) or if you write a table formula

MAX(Table): =IF([@Number]=MAX([Number]),[@Number],””)

 

Min Max chart in Excel

Now we create a basic Column Chart from this data and we get

Min Max chart in Excel

The order of the series is important, it should be

Min Max chart in Excel

The first thing we need to do is to change colors of the MIN and MAX series.

Min Max chart in Excel

Next, we change the Overlap and Gap Width properties of any series so that the columns overlap and are wider.

Min Max chart in Excel

Now this is a personal preference but I also removed the gridlines.

Min Max chart in Excel

Afterward we add the data labels for the MIN and MAX series

Min Max chart in Excel

And

Min Max chart in Excel

Now all that stands in our way as we stride towards eternal happiness is those zero values at the bottom. We will sort them out by fixing the MIN and MAX series Number format

Min Max chart in Excel

We change that into

0;-0;;@

And press Add. After we do this for both the MIN and the MAX series, all the zero values disappear. Further explanation of this format can be found in the Custom Cell Format You Must Know post.

You can hide the MIN and Max columns in the data table and the chart will be even more inspiring.

Min Max chart in Excel