Easy way to make bullet charts and boxplots in Excel

[fusion_builder_container hundred_percent=”yes” overflow=”visible”][fusion_builder_row][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”][Update: I wrote a tutorial on how to make bullet charts]
I am sure I’m missing something here, but I don’t understand why making bullet charts and boxplots have to be so overly complex in Excel. Instead of messing around with bars and scales and secondary axes, a 1D scatterplot can be used to make both charts. I’ve been using this (obvious?) solution for a long time. I’m sure it is not original (I’m not that good…), but I though I could share it, since I couldn’t find anyone sharing it.

The bullet chart comes with a bonus:

bullet chart in Excel

And this is a regular boxplot, including the outliers:

boxplot in Excel

In both cases you can have multiple charts using the same scatterplot.

And here is the file for you to explore. It was made in Excel 2013. If you think there are easier ways to make these charts in Excel please share them in the comments below.

 [/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]

8 thoughts on “Easy way to make bullet charts and boxplots in Excel

  1. Very interesting. I investigated these approaches years ago, but too many people were still using 2003 and earlier, which did not provide such flexible (and wide) line widths. Obviously it’s time to revisit these techniques.

  2. Very nicely done!

    I’ve been using a join-the-dots / loop-the-loop XY scatter plot as a faux boxplot (with lots of manual changes to marker types), but much prefer the ‘fill’ effect of the thicker lines and the separate series for the component parts.

    One (very) minor improvement would be to set the Cap Type to Flat on the whisker lines on the Boxplot as they’re currently set to round and overlap the interquartile range boxes.

  3. Very nice – this is by far the easiest method I have seen. It also serves as a good illustration of the versatility of the scatterplot.
    No need of messing around with stacked bars and combo charts.
    Thanks a lot for sharing!

  4. I’m very surprised to see what it is possible to do with charts in Excel. I have always been using “classic” charts but most of the time, it doesn’t meet the need. Thanks you for sharing the file!

  5. Thanks for sharing this example. This is by far the simplest approach I have seen. Is there a similar solution for vertical charts?

  6. Late to this party. The only downfall I see to this method (unless I’m missing something) is that it is a lot of work to create a series of bullet charts.

    Do you know of an easy way to do that? I’ve found that a bullet chart is possible with little effort using horizontal bars, but adding the target in that option is difficult.

  7. Hi

    Its simply awesome, I was using lot of calculations to fix the quantitative scales and comparative measures. I am so happy to see this approach .

    Thanks
    Sreekhosh

Comments are closed.