ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Create a Bullet Chart in Excel

Updated on April 13, 2011

Bullet charts are a progression from gauges and thermometer charts. Bullet charts feature a primary measure such as a sales total and compares it against other values such as a target.

They can also be displayed in qualitative ranges such as poor, average, good and great. Or maybe using previous sales totals like the example used in this article. They provide a lot of information in a small uncluttered graph making them perfect for dashboard sheets.

Let's look at creating a bullet chart from a range of data showing the sales of different spices. There is a target value, last year's sales and this year's sales.

Bullet chart data range
Bullet chart data range
Inserted stacked column chart
Inserted stacked column chart

Create the Initial Column Chart

  1. Select the data range that you want to use
  2. Click the Insert tab on the Ribbon
  3. Click the Column Chart button and select the Stacked Column

Using a secondary axis
Using a secondary axis

Format the Target Series

  1. Right click on the Target series and select Format Data Series from the shortcut menu
  2. Select the Secondary Axis option from the Series Options category
  3. Click the Close button
  4. Select the Secondary Vertical Axis and press the Delete key to remove it from the chart
  5. Right click on the Target data series and select Change Series Chart Type
  6. Click on Line and then select the Line with Markers from the Change Chart Type dialogue box and click Ok
  7. Right click on the Target data series and select Format Data Series
  8. Select the Line Color category and click the No Line option
  9. Select the Marker Options category and click the Built-in option
  10. Click the Type drop list arrow and choose the line marker
  11. Click in the Size drop list arrow and enter 14
  12. Click the Close button

Change the gap width of a column in a bullet chart
Change the gap width of a column in a bullet chart

Format the Sales Series

  1. Right click the Sales data series and select Format Data Series
  2. Change the Gap Width to 300%

Completed bullet chart in Excel
Completed bullet chart in Excel

The finished bullet chart uses a dash marker to indicate the target value. The number of sales are shown inside a column representing last years sales.

The finished product is a compact graph that can fit into any dashboard that shows how this years sales compare to last years, and the target set at the start of the year.

New Guestbook

    0 of 8192 characters used
    Post Comment

    • almurray lm profile image
      Author

      almurray lm 6 years ago

      @ArizonaHydroFuel: The charts in the lens are added by taking a screenshot of it in Excel and then inserting it into a text module. There is not much scope for manipulating its size.

    • profile image

      ArizonaHydroFuel 6 years ago

      Hi -- Like your lens! I'm building a new lens and need to post a legible excel chart, maybe more than one. The only way I've been able to manage is to photograph the printed chart and post it as a gif file in the text module. I'm hoping there's a better way! Can you help? Also, being able to maximize or at least manipulate the size of the chart within the module would be nice.