A guest post by Matt Grams discussing an alternative solution of creating bullet graphs with Microsoft Excel
We proudly present the first guest post here on Clearly and Simply: Matt Grams describes a very interesting alternative approach of creating bullet graphs in Microsoft Excel without using VBA.
When you need one or more horizontal bullet graphs in an Excel spreadsheet, what do you do if…
- you work exclusively in Excel 2003 or earlier,
- you don’t want to use a 3rd party add-in,
- you want your spreadsheet to be free of VBA, and
- your bullet graph must have a professional appearance?
Faced with this scenario of apparently limited options, you’re sure to come across Charley Kyd’s tutorial at ExcelUser. Attempting to build a bullet graph with this method was a useful exercise for me, but the approach left me flustered at the complexity of the data arrangement and chart set-up. If making just one bullet graph was that hard, what are you going to do when you have multiple bullet graphs to implement? Furthermore, not having the bullet graph data values in a single row was far from ideal.
This post describes an alternative and simpler approach of how to create bullet graphs with Microsoft Excel, including step-by-step tutorials and an example workbook for free download.
Chandoo’s sports dashboard tutorial
A breakthrough for me in seeing the potential Excel has as a visualization tool was reading Chandoo’s tutorial regarding bar charts; it’s a simple technique, yet full of possibilities. Might not a similar method be used for bullet graphs?
The first go I had at this approach resulted in the creation of two charts, one dropped on the other by means of dragging and aligning one chart over a second. First, a stacked bar chart had the “good/average/poor” values, and used a thin sliver in the data to indicate the red “target” line. Next, a second bar chart added the “actual” values, and was dragged to fit over the stacked bar chart. The end result met the initial criteria, but the method lacked a certain degree of elegance.
Stacked Bar Chart with a Secondary Axis
Playing with the Excel’s charting engine produced another realization: if you put the “actual” value of the bullet graph on the Secondary Axis, you can have your bullet graph contained in a single chart.
Here are the download links to two detailed step by step tutorials of how to create a single and a multiple row bullet graph:
Download How to create a bullet graph in a single row with Excel (pdf, 185.9K)
Download How to create a bullet graph in multiple rows with Excel (pdf, 291.7K)
Robert bulletproofs the bullet graph data set-up
The data I worked with to generate the bullet graph values involved a fixed set of criteria where I had no concerns about having to make certain data parameters flexible. After reviewing this bullet graph method, Robert applied his insight and skills, so that if your bullet graph demands a “more general” approach, the graph has a series of calculated “dummy data” to ensure maximum flexibility for the graph.
Here is a workbook for free download, including examples of the discussed techniques:
Download Bullet Graphs Simple (Microsoft Excel 2003, 89.5K)
Much more could be said on the nuances of the subject, but this should be enough to get you started.
Robert: thanks for the opportunity to post on your blog and for making the graph more robust. There’s still so much to learn from your previous posts, but please keep coming up with new ones!
Robert’s note:
Matt, many thanks for all our interesting and inspiring discussions regarding this topic, for taking the time to write this post and how-to tutorials and – last, but not least - for sharing your ideas and work with us. I would be more than happy to feature further guest posts from you here on Clearly and Simply.