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.
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.
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.
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.
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.
Click the spreadsheet icon and select cells from A3 to A6.
Click OK.
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.
Repeat the same for brown and green sections and choose lighter versions of the same dark color you selected previously.
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.
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.
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 previous steps.
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.
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.
Click OK button and click OK button again.
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.
Select the horizontal line and delete it.
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.
Select Line Style from left and enter 3 pt in the Width combo box.
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.
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.
Leave a Reply