Making your Min Max chart in Excel truly shine


EXCEL UNPLUGGED

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…

View original post 123 more words