How to create a Bullet chart in Excel

Excel 2016
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:
A Bullet graph in Excel 365

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 the 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:

Data for a Bullet graph in Excel 365

   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:

qualitative ranges for a Bullet graph in Excel 365

   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 Insert Column or Bar Chart button:

Insert Bar or Column Chart in Excel 365

From the Insert Column or Bar Chart dropdown list, select 100% Stacked Bar:

100% Stacked Column in Excel 365

Excel creates the chart with an odd look:

100% Stacked Column graph in Excel 365

   2.3.   Modify the chart:

   2.3.1.   Do one of the following:

  • On the Chart Design tab, in the Data group, choose Select Data:
    Select Data in Excel 365
  • Right-click in the chart area and choose Select Data... in the popup menu:
    Select Data in popup menu Excel 365

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

Select Data Source dialog box in Excel 365

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

100% Stacked Column graph in Excel 365

   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:
    Data for the bullet graph in Excel 365
    • 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...:

Paste Special into the chart from Clipboard in Excel 365

   3.6.   In the Paste Special dialog box:

  • In the Add cells as group, ensure that the New series option is selected,
  • In the Values (Y) in group, select the Rows option,
  • Select the Series Names in First Column checkbox,
  • Unselect the Categories (X Labels) in First Row checkbox:
Paste Special dialog box in Excel 365

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

Stacked Column graph with 2 data series in Excel 365

   3.7.   On the Chart Design tab, in the Type group, click the Change Chart Type button:

Change Chart Type button in Excel 365

   3.8.   In the Change Chart Type dialog box:

  • Select the Combo tab,
  • Check the Secondary Axis checkbox for all newly added data series:
The Change Chart Type dialog box in Excel 365

   4.   Format new data series:

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

Format Data Series in popup menu Excel 365

   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:
    Series Options in Format Data Series pane Excel 365
  • 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:
    No fill and No line in Format Data Series Excel 365

Add any other adjustment.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.