Bullet Graphs in Excel
I recently picked up a copy of Stephen Few’s outstanding book Information Dashboard Design. First of all…wow! If your reports include any type of chart or graph, you owe it to yourself to pick up a copy of this incredible resource. Stephen developed a graph called a Bullet Graph, and in this post, I’ll walk through the steps for creating both vertical and horizontal bullet graphs in Excel 2013 for Windows.
Before we get started, let’s take a look at our objective. We would like to create a single bullet graph in Excel, and we would like to be able to perform the same steps to create vertical and horizontal graphs. Additionally, we’d like the steps to be easy to remember and fairly quick to implement.
If you are not familiar with a bullet graph, in summary, it provides a measure along with context in a compact format that can be read at-a-glance. For example, let’s consider year-to-date revenue. Let’s say actual revenue is $280,000. Presenting this figure alone is not as meaningful as providing it with context…we need to advise the user if it is bad, satisfactory, or good, and if we are on target. Let’s assume that bad performance is $200,000, satisfactory performance is $250,000, good performance is $300,000, and that the revenue target is $270,000. All of this information is presented in the following bullet graph.
That is the idea, albeit, at a high level. For the details, please review the bullet graph specifications which are available on Stephen Few’s website at the link provided below.
Since we are going to build this in Excel, there are of course many options, and a Google or YouTube search yields many alternatives. The solution presented below is only one such option, and if you have a different approach that you prefer, please share by posting a comment below. Thanks to Jorge Camoes at excelcharts.com for opening my mind to this approach!
We first need a convenient place to store or compute the key values. The key values are the bad, satisfactory, and good qualitative ranges, the target comparative measure, and the actual performance measure. I’ve set up a user input area in the worksheet, as pictured below.
It is now time to prepare the worksheet range that Excel will use to create the graph. We are going to ask Excel to draw 5 vertical lines, and we’ll adjust the line widths as needed. Ignoring the horizontal (x) axis for a moment, and just thinking about the vertical (y) axis, each line can be defined by the start and end points, for example, the bad range is 0 to 200, the satisfactory range is 200 to 250, and the good range is 250 to 300. As you can imagine, writing Excel formulas to compute the start and end points based on the input cells is fairly easy. The line for revenue will span from 0 to 280, and the line for the target amount will span from 270 to 271.
Now that we have visualized the vertical (y) axis, let’s think about the horizontal (x) axis. Since we are creating lines, the x axis values for all lines will be 1.
After writing some easy formulas, the chart’s plot area is shown below.
Since the plot area is created with formulas, the values will automatically update when the input cell values change.
Now, on to the graph making.
We’ll start with the vertically oriented graph pictured above.
1) First, we select the plot range, excluding the header row, and then click the Insert > Chart > Scatter with Smooth Lines command icon. Excel produces the chart shown below.
Believe it or not, with a few simple formatting tricks, we’ll have this looking like a bullet graph in no time!
2) Next, we need to properly identify the series values, so we click the Chart Tools > Design > Switch Row/Column command icon. The updated chart is below.
At this point, Excel is plotting the desired values! All we need to do is make a few formatting updates, namely, updating the line widths and colors, changing the chart title, and removing the legend, gridlines, chart area border, and horizontal axis.
3) Next, let’s remove the X axis labels, gridlines, and legend. I am using Excel 2013, so, this step is accomplished by activating the chart, clicking the Chart Elements icon (the green plus button), and then unchecking the Gridlines, Legend, and Primary Horizontal Axis checkboxes. The updated chart is shown below.
4) Next, for each line, we need to specify the width, color, and cap type. This is done by selecting each series on the graph one at a time, and setting the desired format. For example, select the Actual series, and then change the line Width to say 8 pt, the line Cap type to Flat, and the line color to Black.
Once that series is done, proceed to the next one. Depending on the data, it may be hard to click each series on the graph, so, you can simply click any series on the graph and then use the Up and Down Arrow keys on your keyboard to cycle through each chart item. For example, click the Actual series and then press the Down arrow key to select the Target series. Set the formatting of the Target series as needed, for example, line Width of 15, Cap type Flat, and line color Black.
Click any series on the chart again, and then use the arrow keys to get to the next one, such as the Good series. Format it with something like a Width of 20, Cap type Flat, and light gray line color. Select the next series, Satisfactory, and go with Width of 20, Cap type Flat, and a medium gray line color. Select the next series, Bad, and go with Width of 20, Cap type Flat, and a light gray color. The updated chart is looking good, as shown below.
5) Lastly, we update the chart title, remove the chart border, and resize the chart area as desired. The updated version is shown below.
The same steps used to create the vertical bullet graph can be used to create the horizontal version, but there is one additional step.
Just like the vertical version, we select the plot area excluding the header and use the Insert > Chart > Scatter with Smooth Lines command icon. Just like above, we click the Switch Row/Column icon.
Now here is the additional step. We need to switch the X and Y values for each data series. To do this, we click the Chart Tools > Design > Select Data icon to open the Select Data Source dialog as shown below.
Then, we select each series, click the Edit button, and switch the X and Y range references. For example, the Bad series contains the X and Y values show below.
We just switch the X and Y range references, either by selecting the correct range on the worksheet or by typing the updated row references. Please note: hitting F2 while in the X or Y values dialog fields may make it easier to edit these values.
The updated dialog values are shown below.
After you switch the X and Y range references for each series, your updated graph should look like the one below.
From here, we just use all of the formatting tricks from the vertical graph, including setting the line widths, colors, and cap types. We also position the plot area and chart title to the desired locations by clicking and dragging. The resulting horizontal bullet graph is shown below.
Dynamic Color Based on Target
Now, for one fun little twist. If we had several bullet graphs and wanted the reader to quickly determine if a given measure has missed the target, then we could consider using dynamic colors. For example, a red color could indicate that action needs to be taken and could draw attention to the given measure.
To accomplish this, we would actually plot two series lines for the actual measure, in this case revenue. The series used when the measure meets the target is colored normally, for example, black. The series used when the measure misses the target is colored to distinguish it, for example, red. To do this, we simply add an additional series and color it with the desired highlight color. To add the series, activate the chart and use the Chart Tools > Design > Select Data icon and then Add a new series.
Here is an updated screenshot that shows the additional series values when the target has been met.
And, here’s how it looks when the target has not been met.
By using the IF() and NA() functions, we update the plot formulas for the original series (Actual Hit Target) to return values when revenue is greater than or equal to the target and to return #N/A when it is not. The #N/A error removes the series from the graph. The formula for the new series (Actual Miss Target) returns values when revenue is less than the target and #N/A otherwise. The formulas are provided in the sample workbook.
The resulting graph is shown below.
This is one possible implementation of the Bullet Graph in Excel. Thanks to Stephen Few and Jorge Camoes!
And lastly…you’ll want to buy a copy of Information Dashboard Design from Amazon…I’ve included a link below for reference!
- Sample workbook: BulletGraphs
- Stephen Few’s Webpage: Information Dashboard Design
- Bullet Graph Design Specifications: Bullet Graph PDF
- Stephen Few’s Website: Perceptual Edge
- Amazon book page: Information Dashboard Design