How to create a Bullet graph using excel charts and within an excel cell

Bullet graphs were invented by Stephen Few to improve upon standard bar charts and gauges.  Instead of just showing the one value, you can compare that value to target values, previous values and objectives.

For example:bullet_graphs_simple

  • The current value is shown by the black bar in the centre (1100).
  • The range is shown in colour: Very poor = dark grey, Poor = light grey, OK = dark blue
  • Previous year’s value (comparative value) is shown as the white line (desired outcome should also be above this)

You can see how this is more desirable than say just a gauge chart, which doesn’t have any comparison to any other data (for example previous year):gaugeThere are three main methods for recreating bullet charts (either single or multiple).  Please note that this is not all my own work.  A lot of this is from extensive reading on the chandoo site: http://chandoo.org/

  1. Use a standard column chart (with additional line chart) bullet chart1
  2. Use a bar chart:Horizontal_bullet_graphs
  3. Create the bullet graph within a cell using a formula

Picture1

There are definite advantages and disadvantages to the three methods, so I’ll go through each.

Use a standard column chart

Advantages:

  • No calculations needed
  • Can show percentages or real values if single line chart

Disadvantages:

  • Can have comparisons to more than one year
  • Multiple line charts can only show percentage completion or normalised data

Method (single line)

  1. Open an excel data sheet (see example bullet_graphs)
  2. Paste in the data shown belowdata1
  3. Select all of the cells except for the YTD column
  4. Insert a 2D clustered column chart
  5. Right click on the chart and choose the select data option
  6. Click on the switch row and column button
  7. Rearrange the series until they are in the order shown below2d clustered column
  8. Press the OK button to close
  9. Select the first series (purple column above) and press Ctrl+1
  10. Adjust the series options so that they are overlapped with no gap and press the close button
  11. Delete the legend and the bottom horizontal axis.
  12. Double-click on the vertical axis.
  13. Adjust the minimum and maximum to show 0 and 1600 and press close
  14. Right-click on the green column (2012 series)
  15. Select change series chart type
  16. Choose a line with markers type of graph
  17. Adjust the fill colour and marker type to show a white line as a marker.  Choose to have no line.
  18. Reduce the width of the overall graphbullet_graph
  19. Highlight the YTD column of data (2 cells)
  20. Click on the graph and press Ctrl+V to add this series into the chart
  21. Excel will automatically plot this as another line chart.
  22. Right click the new point and choose change series chart type again
  23. Choose the 2D column type again.  All of the formatting we did in point 10 will disappear.
  24. With the new series selected, click on Ctrl+1.
  25. Choose to plot this series on the secondary axis and give it a gap width of 130% and press the close button
  26. Left click on the new vertical axis on the right and adjust it the same as the left (repeat points 12+13)
  27. Then delete the new vertical axis
  28. Right click once more on the purple YTD series and repeat steps 9 + 10.bullet_graph2
  29. All that remains now is to change the fill of the series as you wish
  30. If you wish to compare the current value (Blue bar above) to more than one set of data, simply add them in before step 19.
  31. Choose a different colour or marker shape for each value and ensure they are plotted as lines.

Note: If you want to add multiple lines, simply add rows below your data and ensure that all of the series plotted on the first vertical axis have a slight gap (less than the gap of the YTD figure plotted on the secondary axis).

Use a bar chart

This is a very similar procedure to the first method, but you need to do some calculations first. Once the calculations are done, the method to produce this type of graph is slightly easier, though you still need to use a secondary vertical axis.

Advantages

  • Slightly more beautiful (in my opinion)
  • Slightly easier to create the charts
  • Can show percentages or real values if single line chart

Disadvantages

  • Some calculations needed
  • Multiple line charts can only show percentage completion or normalised data

Method (multiple lines)

  1. Copy and paste the data below into an excel spreadsheet multiple lines data
  2. Create five new columns with the titles as shown:new titles
  3. The calculation for the YTD column is as follows:
      =B28/$F28 where column B is the original YTD column and column F is the original Max column.  28 is the current line.  Note the $ symbol before the column F.  This prevents Excel from transposing the column when we copy and paste the formula to the other rows.
  4. Copy and paste this formula until it fills each row of the new YTD column.
  5. Copy and paste the formula again into the new Poor, OK and Max columns.
  6. In the new column 2012 simply type in 0.3% and paste it for each row
  7. In the new column Max, type in the formula below, where column K is the original max column, column J is the original 2012 column and column P is the new 2012 column.  Again 28 is the current line.
      =K28-J28-P28
  8. Copy and paste this formula until it fills each row of the new Max column.
  9. Highlight all of the new columns except for the new YTD column.
  10. Insert a 100% stacked column bar chart.
  11. Adjust the vertical axis to show minimum 0% and maximum 100%.
  12. 2d stacked bar chartDelete the legend.
  13. Select the new YTD column and copy it.
  14. Click on the graph and paste it in.
  15. Right click on the new YTD series and press Ctrl+1.
  16. Choose to plot this series on the secondary axis.
  17. Ensure the gap width is set to 25o%.
  18. With the format series box still open, click ont one of the other series and change the gap to 50%.
  19. Close the formatting window.2d stacked bar chart 2
  20. Adjust the second vertical axis as per point 11, then delete it.
  21. Right click on the YTD series and choose change series chart type.  Select the standard clustered 2D bar.2d stacked bar chart 3
  22. Right click on the YTD column again and choose select data
  23. Click on the button to edit the horizontal data and select the column with the Data 1, Data 2,… labels.  (If the horizontal axis doesn’t update, you assigned the horizontal axis of the other columns not the YTD column.)
  24. In the graph above, you can just see some purple lines near the end of the graph.  These are the target lines.  Adjust the size of these by altering the value in the new target column, for example change 0.3% to 0.5% and the line will become thicker.
  25. Format the other series as you wish.
  26. The last two series (green and cyan above) should be formatted the same as they both represent the Max area.2d stacked bar chart 4

Create the bullet graph within a cell using a formula

Advantages:

  • Final file size will be much smaller
  • Much easier to adjust data and add new lines
  • Much easier to create

Disadvantages:

  • Not quite as beautiful
  • Can only have the comparison with one line of data

Method

  1. Copy and paste the data below into an excel spreadsheet (see the cell version tab of bullet_graphs).multiple lines data 2

  2. Please note there is no Max column!
  3. Add the new columns as shown below.new titles 2
  4. In the first row of the new YTD column paste the following formula, where column B is the original YTD column and column D is the Target column:
      =ROUND(B6/$D6*20,0)
  5. In the first row of the new 2012 column paste the following formula, where column C is the original 2012 column and column D is the Target column:
      =ROUND(C6/$D6*20,0)
  6. In the first row of the new Target column simply type in the number 20.
  7. In the first row of the new Poor column paste the following formula, where column E is the original Poor column and column D is the Target column:
      =ROUND(E6/$D6*$I6,0)
  8. In the first row of the new OK column paste the following formula, where column F is the original OK column and column D is the Target column:
      =ROUND(F6/$D6*$I6,0)
  9. Copy and paste this row of formula down to all of the other rows of data.
  10. We now need to create the bullet graph.
  11. You will need 20 columns, reduced in size to 0.75.
  12. If you have 8 rows of data in excel displayed in rows 6 to 13, then in the first column where you want to put the bullet chart in row 14 type the number 1.
  13. The next column should be number 2 and so on until you reach 20.
  14. In the row below this, type in the three symbols “─ ┼ │”, one in each cell.  We will use these to create the current status line.
  15. In the first row, first cell, type in the formula below:
        =IF(M$14=$H6,IF(M$14<=$G6,$AD$15,$AE$15),IF(M$14<=$G6,$AC$15,””))

    formula

    • Column M is the first column of the bullet graph we are creating, line 14 is the line of numbers we created in steps 12 and 13.
    • Column H is the values shown in the new 2012 column.
    • Column G is the values shown in the new YTD column.
    • Columns AC, AD and AE are where in line 15 we put the three symbols “─ ┼ │”.
    • Be very careful with the $ symbol.
  16. Copy and paste this formula across the 20 columns and down the rows of data.
  17. Your bullet graph should now look something like this:formula 2
  18. Highlight the cells of the bullet graph and click on conditional formatting.
  19. Set up the three rules as shown below:conditional formatting
  20. Hide rows 14 and 15 and your chart is done!in cell

Alesandra Blakeston

Advertisements

13 thoughts on “How to create a Bullet graph using excel charts and within an excel cell

  1. Hello Alesandra,

    I really like this post but there is an issue with your Bullet graph using a bar chart. The issue is the calcualtions will not work if the “Target” (Black Line) exceeds “Last years value” (White line).

    Sorry to bring bad news.

  2. I liked all the ways that the data was displayed totally awesome. I am looking for a software tool, and have been looking for month,, for software to be able to produce a wall chart that is similar to this link:

    http://en.wikipedia.org/wiki/File:United_States_Frequency_Allocations_Chart_2011_-_The_Radio_Spectrum.pdf

    The different lines of course cover a wider range of frequencies so what I would like to do is to produce a chart by using each line as a different graphic and then pasting these into a graphics tool and printing the wall chart after getting all the charts lined up.

    Please help in my search

    I can call you to explain further through Skype. I work for the US Army as a spectrum manager and want to produce a chart like this for our local area.

    Thanks

    Kenneth Johnson
    kjinga@Yahoo.com

      1. Alesandra…thanks so much, I am in touch with the developer now and we are working out a solution. I would really love to get my hands on something the European Spectrum Managers use it is called EFIS and the link is http://www.efis.dk/views2/graphTool.jsp?bands=3+-+30+kHz&user=1&languages=English&searchOption=applications I am also in touch with them on how to create this. I was asked to send a spreadsheet and they can put it in the type of format I need, I am inquiring as to what tools, code, they used to do this. Believe it or not there is a market for this type of tool…if you asked me to guess I would say 1000 would be about accurate, for FCC, FAA, DOD, Army, Navy, AF, USMC, geographic commands, foreign entities etc. It has to be simple and customizable but I know you smart folks out there can make it happen thanks look forward to you next release of information.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s