Sunday 22 November 2015

[ ::: ♥Keep_Mailing♥ ::: ]™ A Step-by-step Guide on Creating a BULLET Chart in Excel



One of the challenges while creating a dashboard is to present the analysis in a limited screen space (preferably a single screen). Hence, it is important to make smart choices while creating the right chart. And here is where Bullet Charts score over others.

Bullet charts were designed by the dashboard expert Stephen Few, and since then it has been widely accepted as one of the best charting representations where you need to show performance against a target.

One of the best things about bullet charts is that it is power-packed with information and takes little space in your report or dashboards.

Here is an example of a Bullet Chart in Excel:Bullet Chart in Excel - Example

This single bar chart is power-packed with analysis:

  • Qualitative Bands: These bands help in identifying the performance level. For example, 0-60% is Poor performance (shown as a dark blue band), 60-75% is Fair, 75-90% is Good and 90-100% is Excellent.
  • Target Performance Marker: This shows the target value. For example, here in the above case, 90% is the target value.
  • Actual Performance Marker: This bar shows the actual performance. In the above example, the black bar indicates that the performance is good (based on its position in the qualitative bands), but it doesn't meet the target.

Download Bullet Chart TemplateDownload File Pic


Now, let me show you how to create a bullet chart in Excel.


Download Bar for posts


Creating a Single KPI Bullet Chart in Excel

  1. Create the data set
    • Arrange the data so that you have the band values (poor, fair, good, and excellent) together, along with the actual value and the target value
  2. Bullet Chart in Excel - Data Set Select the entire data set (A1:B7), go to Insert –> Charts –> 2D Column –> Stacked ColumnBullet Chart in Excel - Insert Chart
  3. In the above step, you will get a chart where all the data points have been plotted as separate columns. To combine these into one, select the chart and go to Design –> Data –> Switch Row/Column. This will give you one column with all the data points (the first four colors are the qualitative bands, the fifth one is actual value and the top most is target value).Bullet Chart in Excel - Switch Row Columns
  4. Click on Target Value bar (the orange color bar at the top). Right-click and select change series chart type.Bullet Chart in Excel - change series chart type
  5. In the Change Chart Type dialog box, change the Target Value chart type to Stacked Line with Markers, and put it on the secondary axis. Now, there would be a dot in the middle of the bar.Bullet Chart in Excel - chart type as line chart
  6. You would notice that the primary and secondary vertical axis are different. To make it same, select the secondary axis and delete it.Bullet Chart in Excel - delete secondary axis
  7. Select the Actual Value bar, right-click and select Change Chart Type. In the Change Chart Type dialog box, put Actual Value on the secondary axis.Bullet Chart in Excel - Actual Value on Secondary Series
  8. Select the Value bar, right-click and select Format Data Series (or press Control + 1).
  9. In the Format Data Series pane, change the Gap Width to 350% (you can change it based on how you want your chart to look). Bullet Chart in Excel - Actual Value Bar Gap Width
  10. Select the Target value marker dot, right click and select format data series (or press Control +1).
  11. In the Format Data Series dialogue box, select Fill & Line –> Marker –>Marker Options –> Built-in (and make the following chages):
    • Type: Select dash from the drop down
    • Size: 15 (change it according to your chart)Bullet Chart in Excel - Marker Type and Size
  12. Also change the Marker fill to red and remove the borderBullet Chart in Excel - Marker Color and Border
  13. Now you are all set! Just change the color of the bands to look like a gradient (gray and blue look better)Single KPI Bullet Chart in Excel

Download Bullet Chart TemplateDownload File Pic


Creating Multi KPI Bullet Chart in Excel

You can extend the same technique to create a multi-KPI bullet chart in Excel. Here are the steps:

  1. Get the data in place (as shown below)
  2. Create a single KPI bullet chart as shown above
  3. Select the chart and drag the blue outline to include additional data pointsMulti-KPI Bullet Chart in Excel

Note: Creating Multi-KPI bullet chart technique works well if the axis is same for all the KPIs (for example here all the KPIs are scores in percentage varying from 0 to 100%). You can extend this to margins – for example comparing Net Income margin, EBITDA margin, Gross profit margin, etc. If the scales are different, you would need to create separate bullet charts.

Watch Video Tutorial


While I am a big fan of bullet charts, I believe a single-KPI bullet chart is not always the best visualization. I often gravitate towards a speedometer/gauge chart in case of a single KPI. Let me know what you think by leaving a comment below.

http://trumpexcel.com/2015/05/bullet-chart-in-excel/


--
You received this message because you are subscribed to the Google Groups "Keep_Mailing" group.
To unsubscribe from this group and stop receiving emails from it, send an email to keep_mailing+unsubscribe@googlegroups.com.
To post to this group, send email to keep_mailing@googlegroups.com.
Visit this group at http://groups.google.com/group/keep_mailing.
To view this discussion on the web visit https://groups.google.com/d/msgid/keep_mailing/CAG%3DbiTuwKUoJim8Z5JW9gPJmBbydhbjmSHOrsDb7KQg8O7Ksjg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

No comments:

Post a Comment