How to Make a Bullet Chart in Excel

A bullet chart is a great data visualization tool that can be used to compare the performance of a metric. For example, if you want to compare the sales of two years or to compare the total sales to a target, you can use bullet charts.

Bullet Chart Excel

The bullet graph features a main measure, compares it to a target, and displays the performance as worst, average, and best, or poor, satisfactory, and good, etc. Bullet graphs can be horizontal or vertical. Bullet charts are easy to read and understand. However, there is no easy way to create bullet charts in Excel. In other words, unlike other types of charts, bullet charts cannot be directly inserted into Excel.

A bullet chart is a kind of chart in Excel that shows values that are built upon other values. For example, you can show here how you are going with your sales plans. It could look hard to understand, but the example below will make it more clear.

How to Create a Bullet Chart?

Open Excel and save your file as bullet.xlsx. Enter details as in the following image.

Bullet Chart Table Data

Select cells from B2 to F6 and go to Insert (main menu), Bar (in the Charts group) and select the first chart type from the 2-D Bar section (circled in red).

Bullet Chart 2D Bar Graph

Now, you will get a chart like this:

Bullet Chart Chart Inserted

Select the legend (the section that contains Excellent, Satisfactory, and Poor) and press the Delete button.

Bullet Chart Legend Removed

Formatting of a bullet chart

Right-click any of the green bars and click the Format Data Series option.

See also  How to Make a Pareto Chart in Excel

Bullet Chart Format Data Series

From the new window, select Series Options from the left. In the textbox under Series Overlap, enter 100%, and in the textbox under Gap Width, enter 50%.

Bullet Chart Series Options

Click the Close button.

Bullet Chart Series Formatted

Right-click on any of the green bars and click the Select Data option.

Bullet Chart Series Formatted

In the new window, click the item Excellent and using the up arrow move it upwards. Using the up and down arrows (next to Remove button) correctly, position the item Satisfactory in the middle and the item Poor in the last.

Bullet Chart Select Data Source

Select the Edit button below the Horizontal (Category) Axis Labels.

Bullet Chart Axis Labels

Click the spreadsheet icon and select cells from A3 to A6.

Bullet Chart Axis Labels Formula

Click OK.

Bullet Chart Select Data Source Edit

Again click OK and you will get a screen like this:

Bullet Chart Bars Formatted

Right-click the blue section and click Format Data Series option. Click Fill from the left and select Solid Fill from the right, and from the Color drop down select any dark color.

Bullet Chart Fill

Repeat the same for brown and green sections and choose lighter versions of the same dark color you selected previously.

Bullet Chart Blue Bars

Right-click any of the blue bar and click Select Data. In the new window, click the Add button and you will get a new window. Click in the Series Name: textbox and click the cell B1. Click in the Series Values: textbox and select cells from B3 to B6.

Bullet Chart Edit Series

Again click the Add button. Click in the Series Name: textbox and click the cell C1. Click in the Series Values: textbox and select cells from C3 to C6.

Bullet Chart Edit Series Formulas

Right-click the blue section and click Change Series Chart Type option.

See also  Chart with combo box

Bullet Chart Change Series Chart Type

You will get a new window and select XY (Scatter) from the left hand side. From the right, select the first chart type below the XY (Scatter) section.

Bullet Chart XY Chart

Your chart will look like this:

Bullet Chart Violet Bars

Select the purple section and repeat previous steps.

Bullet Chart Bars and Points

Right-click any purple cross mark and click Select Data option. From the left, select 2013 and click Edit button. You will get a new window. Click in the Series name: textbox and click the cell B1. Click in the Series X values: textbox and select cells from B3 to B6. Enter the values 10,30,50,70 in the Series Y values: textbox.

Bullet Chart Series Values

Click the OK button. Select a target and click the Edit button. Click in the Series name textbox and click the cell C1. Click in the Series X values textbox and select cells from C3 to C6. Enter the values 10, 30, 50, and 70 in the Series Y values textbox.

Bullet Chart Edit Series Target

Click OK button and click OK button again.

Bullet Chart Points Moved

Click any of the blue cross mark and go to Layout (main menu), Error bars (from the Analysis group) and select Error Bars with Percentage.

Bullet Chart Errors Bars with Percentage

Now you will see a horizontal and vertical line over all the four blue cross marks.

Bullet Chart cross marks

Select the horizontal line and delete it.

Bullet Chart cross marks horizontal lines deleted

Go to Layout (main menu) and click the Chart Area dropdown (from Current Selection group).

Bullet Chart Chart Area

Select Series Target Y Error Bars from the dropdown and click Format Selection just below the dropdown. You will get a new window. Select Vertical Error Bars from the left-hand side. Select both from the Direction section and no cap from the End Style section. Enter 7 in the Percentage textbox under the Error Amount section.

See also  How to Shrink a Chart to Fit on a Page in Excel

Bullet Chart Vertical Error Bars

Select Line Style from left and enter 3 pt in the Width combo box.

Bullet Chart Line Style

Click Close button to get a chart like this:

Bullet Chart Lines Formatted

Click on any purple cross mark and repeat steps 10 and 11. Delete the vertical line instead of the horizontal line. Select Series 2013 X Error Bars from the dropdown and click Format Selection just below the dropdown. You will get a new window. Select Horizontal Error Bars from the left-hand side.

Select Minus from the Direction section and No Cap from the End Style section. Enter 100 in the Percentage textbox under the Error Amount section.

Bullet Chart Horizontal Error Bars

Select Line Style from the left-hand side and enter 6 points in the Width combo box. Select the Close option.

Right click on any of the cross marks and click the Format Data Series option. In the new window, select Marker Options from the left and click None from the right.

Bullet Chart Marker Options

Click Close button and your bullet size chart is ready now.

Bullet Chart Ready Sample to Download