Here are three charts with data for Sales, Manufacturing, and Profit data that we want to track;
Here are the corresponding bullet graphs:
In each case there are set percentages for poor, fair, and good performance (red, yellow, and green areas), a target to hit (red line) and the current value for each (black bar). The steps to accomplish these are numerous, but not too difficult. We’ll take the sales data as an example and create our bullet chart. For the others, it is just a matter of copying the chart and changing the source data. Here we go!
Highlight the Category and Sales Data, including column headings, and from the Insert tab, select Stack Column from the 2D Column Chart option:
You will then need to right click on the chart, click on Select Data, and switch the rows and columns:
You should end up with something like this:
Next, right click on each block in the stack, select “Format Data Series => Fill => Solid fill and select the colors you want for each section:
I ended up with this:
Now, right click on the black section (value) and format the data bar to be on the secondary axis. It might totally hide the other sections on the primary axis:
Before you click on Close, change the Gap Width to a very high number. Let’s say around 400%. The larger the gap width, the narrower the bar. Now click on one of the other colored sections and change the gap width of those to a very small number (around 100%) and those will get wider:
Right click on the “Target” block and choose Change Series Chart Type:
And choose X/Y (Scatter). That data point will now be a floating “X”:
I’ve since right clicked on the marker and changed the color to red. OK, we’re getting close. Only 50 more steps to go! (just kidding ;-).
Right click on any of the numbers on the right (secondary) axis and delete it. Right click on any of the numbers on the left (primary) axis and fix the Maximum at 1.:
Right click on the legend and delete it. You should have this:
We’re so close! Next, right click again on the left axis data and select Format Axis and set all these values to None:
Now, right click on any of the grid lines, select Format Grid lines and choose No line. Click on your chart and resize it as you see fit. You should have something like this:
You could leave it as is. The only step left is to change the mark for the target to an “error bar”. Click on the mark and go to the Layout tab for the Chart Tools and choose More Error Bar Options from the Error Bars button:
This sets the Fixed Value to 1.0 and inserts a vertical and horizontal line through the mark. Right click on the vertical line and select delete:
Right click again on the Error Bar, select Format Error Bars… and do the following:
– set the Fixes value to 0.25
– change the End Style to No Cap
– for Line Color, choose Solid line and pick red
– for Line Style, change the weight to 1.5
The last step is to right click on the marker, choose Format Data Series, and under Marker Options choose None
And there you have it:
It seems like a pretty complex process, but do it a few times and you will get the hange of it. Some of the last steps may be easier if the graph is wider. You can narrow the graph as the last step if you’d like.
Like many of the other tips I’ve posted, this works best with practice and repetition. I hope it can work for you.