How-to Make an Excel Bullet Chart

Executives and managers love to see gauge charts in their Excel Dashboards.  However, gauge charts may confuse or mislead the dashboard readers.  For instance, if you have a gauge range from 1-100, what do you do when the data exceeds 100?

My speedometer gauge in my car stops at 160 MPH or 240 KPH.  But is it possible to go faster than this?  If so, then I won’t know how fast I am going if the gauge needle is stopped at the maximum value.  Maybe a gauge chart is not the best solution.Speedometer Gauge

Well Edward Tufte came up with a great solution for these issues.  He called it a Bullet Chart.  It is still a gauge type of chart, but fixes the maximum gauge value issue.  Here is what a bullet graph looks like:image

Notice here that I have increased revenue above the green “good” threshold.  Normally you would hit the end of your normal gauge and with a Bullet Chart as you can see below, we can break through the threshold and exceed our goals.image

The Breakdown





1) Set Up Chart Data

2) Create Stacked Column Chart

3) Switch Rows/Columns for the Chart

4) Change Gauge Threshold Colors

5) Move Actual Series to the Secondary Axis and Increase Gap Width



6) Change Target Series Chart Type to Line with Markers

7) Change Target Series to No Line and Change Marker Option and Color

8) Delete Legend Items and 2nd Vertical Axis

 

Step-by-Step





1) Set Up Chart Data

First we need to setup the data for our chart.  In this case we will need to add values for each performance threshold (Red, Yellow and Green).  Also, we will need to add values for our Actual performance and our Target performance.

Our data will look like the following: image

 

2) Create Stacked Column Chart



Now that we have our data setup, we can create our chart.  For our specific data, we want to select the range of A1:D6.  Then go to your Insert ribbon and select a Stacked Column Chart. image

Your chart will now look like this:image

 

3) Switch Rows/Columns for the Chart

You may or may not have to do this step.  It depends on the number of rows versus columns in your chart data.  In our case we will need to Switch Rows/Columns in the chart.  To do this, select your chart, then select the Design ribbon.  Then press the Switch Row/Column Button:image





 

If you want to learn more about this, check out this post:

Why Does Excel Switch Rows/Columns in My Chart?

Your chart will now look like this:image

 



4) Change Gauge Threshold Colors

The chart is taking shape, so at this stage I recommend changing the Bad “Red” Series, the Poor “Yellow” Series and the Good “Green Series to their respective colors.  To do this, right click on the Bad “Red” series and select “Format Data Series…” from the popup menu:image

Then change the Fill Color to Solid Fill and a Color of Red.image

Repeat this step for the Poor “Yellow” series and also for the Good “Green” series.  Your chart should now look like this:image

If you are having problems selecting the right data series, check out these posts:





The Quickest Way to Select an Data Series in an Excel Chart

 

How-to Select Data Series in an Excel Chart when they are Un-selectable?

 

5) Move Actual Series to the Secondary Axis and Increase Gap Width

Now we need to move the Actual Series to the 2nd axis so that we can have it float over our performance metric thresholds.  To do this, right click on the Actual series in the chart and then select “Format Data Series…”image

Then select the Series Options and the Secondary Axis radio button to move the Actual series to the secondary axis.  At the same time, change the Gap Width to 500%.image



This brings up an interesting side note.  I got a lot of negative comments on the Youtube video toward Microsoft that the developers were dumb to overlap the series when moving to the Secondary Axis.  I guess this is the use case for that feature. Smile  However, I tend to agree that it should be a choice instead of tricking Excel.  You can check out the post here:

Stop Excel From Overlapping the Columns When Moving a Data Series to the Second Axis

And the Youtube Comments:

How-to Stop Excel Charts from Overlapping Second Axis Columns or Bars

 





Your chart should now look like this:image

 

6) Change Target Series Chart Type to Line with Markers

In this step we will create the horizontal line that represents the target for each metric.  To do this, right click on the Target Chart Series and then select “Change Series Chart Type…” from the Pop-up menu:image

Then change the chart type of the Target data series to a Line with Markers Chart Type like you see here.image



Your chart should now look like this:image

 

 

7) Change Target Series to No Line and Change Marker Option and Color

We are using the the Line with Markers chart type but we don’t want to show the line.  So right click on the Target Line and select “Format Data Series…”image





Then from the Line and Markers Options, select Line options and then choose No Line: image

Then select the Marker options and choose Built-in with a size of 25 and a type of a Line.  Also, the Fill to Solid Fill with a color of black and also change the Border to No Line:image

Your chart should now look like this:image

 

8) Delete Legend Items and 2nd Vertical Axis

Your Bullet Chart is almost done.  Notice in the graph above that the Primary and Secondary Axes do not match?  That is throwing off our chart.  So to get the chart we want, we need to align these axes.  We can quickly do that by deleting the secondary axis.  Simply select it and press your delete key.  Also, you may want to delete some of the legend entries.  I suggest deleting the Red, Yellow and Green legend entries.  You can do this by selecting the chart, then selecting the legend and then select one of the legend entries and press your delete key.

Here is what the final chart looks like:image

 

Video Tutorial





 

Free Bullet Chart Download Template

 Free-Bullet-Graph-Sample-Excel-Chart-Template.xlsx

 

Do you like the Bullet Chart?  Let me know your thoughts about this chart in the comments below.

 

Steve=True





2 COMMENTS

  1. I really love this Bullet Chart view and will work to find a spot to use it. However, the colors for the cost view are reversed. Cost, unlike Revenue and Net is better when the number is smaller. So using the numbers in the example, an actual cost of 13 compared to a target of 15 should be Green or actually VERY green because it is better than the target.
    1- Is there a way to adjust for that in this same chart view or must another Chart view handle expense?
    2 – Also, (an unrelated to #1) is there a way to include say, Contribution Margin % in the same view? That would require a secondary axis ONLY for that column.

    Just found your blog about a week ago and love it.
    Thank you!
    Peter

LEAVE A REPLY

Please enter your comment!
Please enter your name here