The Bullet Chart is one of the best usable chart type in Excel. It is a solution for one of the challenges while creating a dashboard to present the analysis preferably on a single screen. Bullet chart is the answer for this. Besides the gauge this is one of the best data visualization tool.
Currently gauge chart is the market leader dashboard tool. Is this simple little tool able to compete with it?
It will turn out by the end of the article!
Bullet chart is a widely accepted tool the best possible choice for displaying the differences between plan / actual values.
As we have talked about in our previous articles the one-page dashboard rule makes designers’ lives a little harder because they have to portray on one single page all the important key performance indicators. Fortunately the chart type introduced today is a great choice if we need to create a spectacular representation and it takes up little space of your dashboard.
Before we start today’s tutorial, let’s take a look at this already done bullet chart!
In this article we will create step by step the graph on the picture above.
This single bar chart is power-packed with analysis, very small but effective, isn’t it? Take a look at its most important elements. At first glance it is a simple bar chart. Let’s examine it closer!
The Key Elements of Bullet Chart
Now we will take a look at those elements that are playing an important role of the creation of plan / actual type of reports.
Qualitative Parts: These sectors helping us to identify the performance levels at a glance. For example on the picture between 0% and 45% the poor performance level can be seen (marked by red color).
The performance is fair between 45% and 65%. Between 65% and 85% good and from 85% to 100% is excellent.
But what if the performance indicator is not between 0% and 100% that we have to display? Can the bullet chart be still used? At first we would think not.
Target Performance: This represents the required performance level we would like to achieve. In this example we marked the required performance level with a red line that is at least at 88%.
Actual Performance: After the plans let’s talk about the actual facts! For most everywhere the most important indicator is the variance.
The actual performance shows the performance that we have actually achieved in the examined time period. This value is indicated now by a thin black bar chart.
When we take a look at the figure we can easily interpret the given result. We can say that the plan wasn’t realized because we were expecting a performance above 88%. And according to the chart the actual performance is only 80%.
So now we know all about the operation of the bullet chart, let’s see the detailed Excel tutorial!
Differences between Graphs and Charts
How to create bullet chart in Excel?
The tutorial is designed that beginner users can also use it effectively.
Let’s start the work! Here are the steps to create bullet charts in Excel.
1. On a blank worksheet arrange the following data. The header of the second column will be customer satisfaction a very popular key performance indicator. Let’s arrange the data as can be seen on the figure below.
The first four rows will show the appropriate performance levels (poor, fair, good, excellent). The fifth row contains the actual value and the sixth one the planned value.
Very important that the sum of the first four rows be 100%. Not mentioned in many tutorials but we do!
2. Select the entire data set (B2; C8). From the ribbon choose Insert, Charts, 2D Column, Stacked Column.
3. The result we got is not exactly the one we wanted! The bar charts representing the given values are displayed separately. How can we merge them? Fortunately nothing is impossible in Excel!
Highlight the chart than follow the next steps: Design Tab > Data > Switch Row/Column.
With the help of this merging all of our data points will be displayed on one single bar chart. We can easily see that we have used the right method. There are six separated stripes can be seen on the completed chart.
Four of them responsible for the performance levels, the fifth one is the value of the plan and the sixth one is the actual performance value.
4. Highlight the target value bar. This is the upper section of the bar chart as you can see on the picture below. Right click and select the “Change Series Chart Type” option.
Explanation: What will happen now? We have only highlighted one part of the chart containing of six elements. We only change the type of this one.
5. The appearing dialog box is the “Change Chart Type.” To change Target Value chart type you have to do the following. First use the “Stacked Line with Markers” type. After this mark the secondary axis checkbox. If we followed all the steps properly in the place of the target value we will see a dot.
6. We only going to need one axis we need to delete the other one. Highlight the secondary axis as can be seen on the picture below and delete it.
7. Select the actual value bar, we will change the type of this one also. In the Change Chart Type dialog box select the secondary axis checkbox. Here we leave the chart type unchanged, the Stacked Column Chart will remain.
8. Let’s see the most interesting part! Select the Value bar, right click and select Format Data Series or press Ctrl + 1.
9. In the Format Data Series pane, change the Gap Width to 400%. You can of course set any value you like. This will set the width of the stacked column chart.
10. Earlier we have set the point marking the Target Value. Now we will set this point in a few easy step to act as a real marker. Select the Target Value marker dot, right click and select the “Format Data Series” option.
11. In the Format Data Series dialog box select the followings: Fill & Line > Marker > Marker Options > Built-In.
Select the dash and set the size of the marker to 17.
12. We still have two little things to be changed regarding the marker. Change the Marker fill to red and remove the border.
We are done and we do have a good looking chart! Naturally the selected color combination depends on the kind of dashboard you need to create. You can use the one from the example but we believe the KPI markers combination, red-yellow-green.
KPI Chart Tools and automated solutions
Have to admit that some patience is needed to create the chart. But what if the available time is very limited but we still want to create a professional looking dashboard.
We have the solution for this, and this is an excel chart add-in. Add-ins are very great tool to expand the capabilities of Excel! Add-in operational functions usually appear on the ribbon. Let’s see an actual example.
In this tutorial you could see that it was rather difficult to set colors and stripes. Now we’ll show a solution can be unbeatable by manually created templates.
Here is the sample that can help create a bullet chart by a click of a button. The data table is of only three columns.
In the first one we list the qualitative categories and the plan / actual values. In the second column we list the stripes belonging to them. And the third column is really interesting. We can pre-set the color of the given stripes so the possibility of the combinations are endless.
And after only one click this is the end result. Do you like it? This was created in only a few seconds. And as a teaser let’s see a little data visualization:
In our opinion it is worth to take VBA programming seriously! What do you think? If you would like to know more about the add-in, click here!
Conclusion: Should I use Gauges to replace bullet charts?
At the end of the article it is timely to ask this question. Let’s see the advantages and disadvantages on both sides.
The bullet chart is a versatile tool (as you could see) but it has its limits. The biggest of them all is that we mostly can use it only between a 0% and 100% range. This fact greatly effects its utility. Advantage of it the small space it requires. Naturally the chart add-ins have such benefits that the experienced report developers can fully take advantage of.
As experts we recommend the gauge chart if the task is the creation of a KPI dashboard. The gauge chart presents an outstanding user experience which can’t be similar to other charts. We don’t really understand why it not a default chart type in Excel. 8 out of 10 CEO asked would vote for the speedometer type of display. We’ll leave it up to you, we readily use both types. Download the free chart template!