How to create a simple Bullet graph in Excel

Excel 2016
A bullet graph (the name by its inventor Stephen Few) or bullet chart (in Microsoft office) generally is used to display progress towards the specific target:
A simple Bullet graph in Excel 2016

Both values, actual and target, can be shown as a percentage (see How to create a Bullet chart in Excel):

A Bullet graph in Excel 2016

To create a bullet chart in Excel, do the following:

   1.   Create a bar chart.

   1.1.   Select the data.

For example, the total visits for the previous month (the target) vs the visits for the current month:

Data for simple Bullet graph in Excel 2016

   1.2.   On the Insert tab, in the Charts group, click the Insert Column or Bar Chart button:

Insert Column or Bar Chart in Excel 2016

From the Insert Column or Bar Chart dropdown list, select Clustered Bar (or Clustered Column):

Simple bar graph in Excel 2016

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

Change Chart Type in popup menu Excel 2016

   1.4.   In the Change Chart Type dialog box:

  • Select the Combo tab,
  • Check the Secondary Axis checkbox for the Previous period data series and select the Scatter type for this data series,
  • Re-select for the Current period data series the Clustered bar chart type (Excel changes it after choosing the Combo tab):
    The Change Chart Type dialog box in Excel 2016
  • Click OK.

   1.5.   Do one of the following:

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

   1.6.   In the Select Data Source dialog box:

  • Select the Previous period data series (the target),
  • Under Legend Entries (Series), click the Edit button:
Select Data Source dialog box in Excel 2016

   1.7.   In the Edit Series dialog box:

  • Choose cells with the correct values for this data series (Series X values),
  • Type = {1,2,3,4,5} for the Series Y values:
    Edit Series dialog box in Excel 2016
  • Click OK twice:
Combination graph in Excel 2016

   2.   Change the Secondary Axis:

   2.1.   Right-click the secondary horizontal axis and choose Format Axis in the popup menu:

Format Axis in popup menu Excel 2016

   2.2.   On the Format Axis pane, on the Axis Options tab, in the Axis Options section:

  • In the Bounds Minimum field, type 0.5 to positioning the target points in the middle of each bar:
    Format Axis pane in Excel 2016
  • Ensure that Excel calculates the Bounds Maximum as 5.5:
Formatted Combination graph in Excel 2016

   2.3.   Remove the secondary axis.

   3.   Create the target markers:

   3.1.   Select the Previous period data series (the target).

   3.2.   Right-click on the data series and choose Format Data Series... in the popup menu:

Format Data Series in popup menu Excel 2016

   3.3.   On the Format Data Series pane, on the Fill & Line tab, under Marker:

  • In the Marker Options section, select the Built-in option, the appropriate Type and Size:
    Marker Options in Format Data Series pane Excel 2016

    Note: If you create a column chart, it is enough to select the rectangular marker, and the size about 20. After that, you can skip other customization for the target markers.

  • In the Fill section, select the Solid fill option and the color you prefer:
    Solid fill in Format Data Series pane Excel 2016
  • In the Border section, select the No line option.

Excel shows the target data series as markers you selected:

Combination graph with targets in Excel 2016

   3.4.   To create the target markers more visible, select this data series, and then do the following:

On the Design tab, in the Chart Layouts group, click the Add Chart Element button:

Add Chart Element button in Excel 2016

From the Add Chart Element dropdown list, select Error Bars and then More Error Bars Options...:

More Error Bars Options in Excel 2016

On the Format Error Bars pane:

  • On the Error Bars Options tab, in the Vertical Error Bar section:
    • In the End Style group, select No Cap option,
    • In the Error Amount group, select the Fixed value option and then type the value you want:
    Error Bars Options in Format Error Bars pane Excel 2016
  • On the Fill & Line tab:
    • Select the Solid line option, choose the Color and Width as you have chosen for target markers,
    • Choose the Round item from the Cap type drop-down list:
    Fill & Line in Format Error Bars pane Excel 2016

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