# How to create a Bullet chart in Excel

Excel
A bullet graph (the name by its creator Stephen Few) or bullet chart (in Microsoft office) is designed to demonstrate the difference between the target value and the actual one. Sometimes there are ranges of performance enabled beside the current value bar – they show something like "low", "medium" and "high" or "poor", "satisfactory", "good" and "excellent" value ranges:

The author of the bullet graph used varying intensities of a single hue for the qualitative ranges, but you can use any colors you like (see rainbow thermometer charts).

To create a bullet chart like one above, do the following:

1.   Prepare the data:

1.1.   To create a bullet graph, you will need several different data ranges:

• The target
• The actual or current value
• The qualitative ranges

For example:

1.2.   Create the qualitative ranges (see rainbow thermometer charts for more details):

For example, using the "poor", "satisfactory", "good", and "excellent" value ranges which are distributed as follows:

2.   Create qualitative ranges:

2.1.   Select the data (in this example, B6:B10).

2.2.   On the Insert tab, in the Charts group, click the Column button:

From the Column dropdown list, select the 100% Stacked Bar chart.

Excel creates the chart with an odd look:

2.3.   Modify the chart:

2.3.1.   Do one of the following:

• Under Chart Tools, on the Design tab, in the Data group, choose Select Data:
• Right-click in the chart area and choose Select Data... in the popup menu:

2.3.2.   In the Select Data Source dialog box, click the Switch Row/Column button:

Add any other adjustment that you find useful to the qualitative ranges:

3.   Add the data with the current value and the Target to the chart:

3.1.   Prepare the new data:

The Target and the Actual (current) value, in Microsoft Excel, can be added in different ways:

• create another chart and place it above this one (see Combining several charts into one chart),
• add the new bar chart which contains both of these values:
• The Target defined at the point of 90%, in the interval between "good" and "excellent" zones. Use 2% to display the Target as a thicker line.
• The Actual (current) value is a percentage of the Target.
• Zone 1 is equal to the Actual value if the Actual value is less than the Target.
• Zone 2 is equal to the difference between the Target and the Actual value if the Actual value is less than the Target.
• Zone 3 is the difference between the Actual value and the Target if the Target is less than the Actual value.

3.2.   Select the newly added values (in this example, D6:E10).

3.3.   Copy then to the Clipboard (by pressing Ctrl+C).

3.4.   Select the chart.

3.5.   On the Home tab, in the Clipboard group, select the Paste dropdown list and then choose the Paste Special...:

3.6.   In the Paste Special dialog box:

• In the Add cells as group, ensure that the New series radio button is selected,
• In the Values (Y) in group, select the Rows radio button,
• Select the Series Names in First Column checkbox,
• Unselect the Categories (X Labels) in First Row checkbox:

Excel updates the chart after adding the new data series, and you need to fix the formatting:

3.7.   Right-click on the chart and choose Change Chart Type... in the popup menu:

3.8.   In the Change Chart Type dialog box:

• Select the Combo tab,
• Check the Secondary Axis checkbox for all newly added data series.

4.   Format new data series:

4.1.   Select any of the new data series and choose Format Data Series... in the popup menu:

4.2.   On the Format Data Series pane:

• On the Series Options tab, set the appropriate value in the Gap Width field to display these data series narrower than the qualitative ranges:
• To hide data series, select them and on the Fill & Line tab, hide this data series:
• In the Fill section, choose the No fill option,
• In the Border section, choose the No line option:

Add any other adjustment.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.