How to create a simple Bullet graph in Excel

Excel 365
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 Column button:

Insert Column or Bar Chart in Excel 2016

From the Column dropdown list, select the Clustered Bar Clustered Bar Chart in Excel 2016 chart (or the Clustered Column Clustered Column Chart in Excel 2016 chart):

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 popup in 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).
  • Click OK.

   1.5.   Do one of the following:

  • Under Chart Tools, 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 Axes in popup 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 under Chart Tools, on the Design tab, in the Chart Layouts group. From the Add Chart Element dropdown list, select Error Bars and then More Error Bars Options...:

More Error Bars Options in Excel 2016

   3.5.   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.