Visiting a friend

Chandoo’s KPI Dashboards revisited – the Box Plots

In summer 2008 my friend and Microsoft Excel MVP Chandoo was kind enough to give me the opportunity of contributing guest posts to his excellent blog Chandoo.org. Actually Chandoo even featured a whole 6 post series on how to create interactive KPI dashboards with Microsoft Excel. Here is a screenshot of the final dashboard:

The last part of the series was about box plots to visualize the distribution of the data (Box Plots Excel Dashboards Tutorial), including average and target values. At that time Chandoo and I decided to apply Occam’s razor and we restricted the tutorial to a simplified version of box plots, working only for data sets with positive values.

This follow-up post on my own blog is about how to create these box plots for all kind of data distribution, i.e. positive and negative values.

The technique of making these box plots applicable to data regardless the sign is pretty much the same as already described in the original post on Chandoo.org:

  • Use a stacked bar chart with one category and several data series
    You need 8 instead of 4 dummy data series for the universal solution
  • Write formulas to calculate the values of the 8 data series
    It took me some time to figure out the correct combination, but the formulas themselves are not too complicated: nested IFs, MIN and MAX (for details see the workbook, download link below)
  • Format the bars according to their position on the stack: invisible, light grey, dark grey and so on
  • Add the average and the target values as additional series to the chart and change the chart type of these new series to XY scatter charts
    (X is the average / target value, Y is a dummy 1)
  • Set the scale of the secondary vertical axis to minimum 0 and maximum 2 in order to position the average and target aligned with the bars
  • Format the average and the target the way you want
    In our example: a cross for the average and a vertical line (using vertical error bars) for the target
  • Get rid of the chart junk
    No fill color and no border for plot or chart area; no line, tick marks etc. for the vertical axes, etc.

Here is a screenshot of the result:

Download the box plot example for free:

Download Simplified Box Plot (Excel 97 – 2003, 101.5K)

The workbook contains a set of possible combinations of values for testing. If you want to see something else, simply delete the formulas in row 16 and type in your own data.

Add a Comment

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