#### 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. A bullet size chart was developed by Stephen Few to overcome the issues of gauges and meters as they require too much space and display too little information.

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.

Table of Contents

## How to Create a Bullet Chart?

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

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

Now you will get a chart like this:

Select the legend (the section that contains Excellent, Satisfactory, and Poor) and press the Delete button. Now your screen will look like this:

See also  How to Create a Stiff Diagram in Excel

## Formatting of a bullet chart

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

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

Click the Close button. Now your screen will look like this:

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

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.

Select the Edit button below the Horizontal (Category) Axis Labels to get a new window like this:

Click the spreadsheet icon and select cells from A3 to A6. Your window should look like this:

Click OK to get a window like this:

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

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.

Click the Close button. Repeat the same for brown and green sections and choose lighter versions of the same dark color you selected previously. Now your chart will look like this (the color could be different).

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. Now the window will look like this:

See also  How to Create Product Quality Dashboard

Click OK. 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. Now the window will look like this:

Click OK, and again click OK.

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

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.

Your chart will look like this:

Select the purple section and repeat step 8. Now your chart will look like this:

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. Now the window will look like this:

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. Now the window will look like this:

Click OK button and click OK button again. Now your screen will look like this:

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.

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

See also  Chart that Ignore N/A! Errors and Blank Cells

Select the horizontal line and delete it so that you will get a screen like this:

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

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. Now your screen will look like this:

Select Line Style from left and enter 3 pt in the Width combo box. Now your screen will look like this:

Click Close button to get a chart like this:

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. Now your screen will look like this:

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.

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