Create a Bullet Chart in Excel
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.
Create the Initial Column Chart
- Select the data range that you want to use
- Click the Insert tab on the Ribbon
- Click the Column Chart button and select the Stacked Column
Format the Target Series
- Right click on the Target series and select Format Data Series from the shortcut menu
- Select the Secondary Axis option from the Series Options category
- Click the Close button
- Select the Secondary Vertical Axis and press the Delete key to remove it from the chart
- Right click on the Target data series and select Change Series Chart Type
- Click on Line and then select the Line with Markers from the Change Chart Type dialogue box and click Ok
- Right click on the Target data series and select Format Data Series
- Select the Line Color category and click the No Line option
- Select the Marker Options category and click the Built-in option
- Click the Type drop list arrow and choose the line marker
- Click in the Size drop list arrow and enter 14
- Click the Close button
Format the Sales Series
- Right click the Sales data series and select Format Data Series
- Change the Gap Width to 300%
Useful Excel Chart Links
- Advantages of Using Microsoft Excel in Creating a Bubble Chart
One of the biggest strengths of Microsoft Excel is the ease in creating good looking charts for fantastic visual data representation. This articles looks at how to create a Bubble chart in Microsoft Excel and why you would choose to use one.
- How to Create a Pareto Chart Using Microsoft Excel
Microsoft Excel makes it easy to create all types of chart. One type of chart is Pareto chart. This article looks at what Pareto chart is and why you would use it.
- Use Excel Chart Templates
You can save your chart including the layout and formatting as a template. You can then use the template when creating charts in the future.
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.