How To Create Excel Bullet Chart Horizontally And Vertically

This article will tell you how to create an excel bullet chart horizontally and vertically with examples. The example data is one student’s examination score. It contains the actual score, target score, and 3 different level scores. And we will use those scores to create an excel horizontal and vertical bullet chart.

1. Excel Bullet Chart Example Data.

1. Below is the example data for this article.
   

   	A	B	C	D
   1	Score	English	Math	Computer
   2	Actual	65	89	96
   3	Bad	60	60	60
   4	Good	20	20	20
   5	Best	20	20	20
   6	Target	90	85	95

2. How To Create Vertical Excel Bullet Chart.

2.1 General Idea.

1. The general idea is that create a combo chart composed of Stacked Columns and Line with Markers.
2. Then hide the lines between the markers, resize the width of the markers, and refill the color of the markers.
3. Then decrease the Actual value column width by increasing the gaps between different actual value columns, below is the detailed steps.

2.2 Create The Combo Chart.

1. Select the cell range A1:D6.
2. Then click the Insert tab —> Insert Column or Bar Chart ( in Charts group ) —> Stacked Column item in the 2-D Column area.
3. Double-click the chart title and change the title text to Jerry Examine Scores.
4. Click to select the chart, and then click the Chart Design tab —> Switch Row/Column item in the Data group.
5. Then it will switch the row and column in the chart graph.
6. Select the chart again, then click the Chart Design —> Change Chart Type item in the Type group to open the Change Chart Type dialog window.
7. Select the All Charts tab, then select the Combo item on the tab’s left-side bottom corner.
8. Select the Stacked Column chart type from the Chart Type drop-down list for the series Actual, Bad, Good, and Best.
9. Select the chart type Line with Markers for the series Target and check the checkbox Secondary Axis in this data series.
   
10. Click the OK button to save the settings.

2.3 Hide The Lines Between Makers.

1. Right-click to select the blue marker line in the chart, then click the Format Data Series… item in the popup menu list, it will show the Format Data Series pane on excel’s right side.
2. Click the first Fill & Line item in the Format Data Series pane.
3. Click the Line item and check the No line radio button under it to hide the lines between markers.

2.4 Change The Marker’s Width, Color, And Border.

1. Click the Marker item, and expand the Marker Options item.
2. Select the Built-in radio button, and then select the short line from the Type drop-down list.
3. Input number 15 in the Size text box to change the marker’s width to 15.
4. Expand the Fill option, and select the red color from the Color drop-down list.
5. If you find there is a blue border around the marker, you can expand the Border option and select the No line radio button under the Border option to hide the marker’s border.

2.5 Decrease The Actual Column Width.

1. Right-click the Actual column ( blue column ) in the chart.
2. Then click the Format Data Series… item in the popup menu list.
3. It will show the Format Data Series pane on the excel right side.
4. Click the Series Options ( the third column item ) in the Format Data Series pane.
5. Select the Secondary Axis radio button under the Plot Series On option.
6. Change the value in the Gap Width text box to a bigger number.

2.6 Remove The Secondary Axis.

1. Click the secondary axis ( on the chart’s right side ) and press the Delete key to remove it.

2.7 Change The Primary Axis Number Range.

1. Right-click the primary axis ( on the chart’s left side ), then click the Format Axis… menu item in the popup menu list.
2. Then it will show the Format Axis pane on excel’s right side.
3. Click the Axis Options item on the pane top area to show the Axis Options settings.
4. Click the Axis Options —> Bounds —> Maximum text box.
5. Change the Maximum number from 120 to 100.
6. Then you can get the below excel vertical bullet chart.
   

3. How To Create Horizontal Excel Bullet Chart.

3.1 General Idea.

1. Create a 2-D Bar chart.
2. Change the Actual, and Target data series to Scatter chart style.
3. Add the Horizontal Error Bar to the Actual data series.
4. Modify the horizontal error bar’s horizontal line to create the Actual data series bars.
5. Draw a vertical line shape and replace the Target data series with the vertical line shape.

3.2 Add The Helper Data Row.

1. Add a helper data row in the above data table as below.
   

   	A	B	C	D
   1	Score	English	Math	Computer
   2	Actual	65	89	96
   3	Bad	60	60	60
   4	Good	20	20	20
   5	Best	20	20	20
   6	Target	90	85	95
   7	Helper Data	2.5	1.5	0.5

3.2 Create The 2D-Bar Chart.

1. Select the cell range A1:D6.
2. Then click the Insert tab —> Insert Column or Bar Chart ( in Charts group ) —> Stacked Bar item in the 2-D Bar area.
3. Double-click the chart title and change the title text to Jerry Examine Scores Horizontal Bullet Chart.
4. Click to select the chart, then click the Chart Design tab —> Switch Row/Column item in the Data group, then it will display the Computer, Math, and English 2D-bar chart on the vertical axis.

3.3 Reorder The Vertical Axis Categories.

1. If you want to reorder the vertical axis label, you can click to select the vertical axis, then right-click it and click the Format Axis… item in the popup menu list.
2. Then it will show the Format Axis pane on excel’s right side.
3. Click and expand the Axis Options item, then check the checkbox Categories in reverse order. Then you can get the below chart, you can see the legend for each data series.
   

3.4 Change The Actual Data Series Chart Type.

1. Click to select the Actual data series ( the 3 dark blue bars) in the chart.
2. Right-click it and click the Change Series Chart Type… item in the popup menu list to open the Change Chart Type dialog window.
3. Click the Combo item in the bottom left corner of the popup dialog window All Charts tab.
4. Then click the Custom Combination item on the dialog window’s right side.
5. Go to the Choose the chart type and axis for your data series area and find the Actual data series.
6. Select the Scatter chart type ( in the X Y (Scatter) section) from the Chart Type drop-down list, and it will also check the Secondary Axis checkbox by default.
7. Click the OK button to save the settings, you will find the actual data series bar has been changed to dark blue points, and it will add a secondary axis on the chart’s right side.
8. Click the second axis on the chart’s right side, and press the delete key to remove it.

3.5 Select The Data Source For The Actual Data Series.

1. Right-click the chart, then click the menu item Select Data… from the popup menu list.
2. Then it will pop up the Select Data Source dialog window.
3. Select the Actual Seire in the left Legend Entries (Series) list.
4. Then click the Edit button to pop up the Edit Series dialog window.
5. Clear the content in the Series X values text box and input the Actual score row values in the text box by clicking cell B2 and Shift + D2.
6. The content in the Series X values text box is something like =’Excel Chart’!$B$2:$D$2.
7. And it will show the data number list like = 65, 89, 96 after the above text box in the same row.
8. If you just see one number such as =65, you can click the OK button to close the Edit Series dialog and then click the Edit button to edit it again.
9. Clear the Series Y values text box content, then input the Helper Data row range in the text box by clicking cells B7 and Shift + D7 in order.
10. You can find the Series Y values text box content is something like =’Excel Chart’!$B$7:$D$7.
11. It will show the data number list like = 2.5, 1.5, 0.5 after the above text box in the same row.
12. Click the OK button to save the settings.
13. Click the OK button in the Select Data Source dialog again, then you will see the below picture.
14. You will find the actual data series has been changed to 3 dark blue points in the chart.
    

3.6 Change The Target Data Series Chart Type.

1. Click to select the Target data series ( the 3 light blue bars) in the chart.
2. Right-click it and click the Change Series Chart Type… item in the popup menu list to open the Change Chart Type dialog window.
3. Click the Combo item in the bottom left corner of the popup dialog window All Charts tab.
4. Then click the Custom Combination item on the dialog window’s right side.
5. Go to Choose the chart type and axis for your data series area and find the Target data series.
6. Select the Scatter chart type ( in the X Y (Scatter) section) from the Chart Type drop-down list, and it will also check the Secondary Axis checkbox by default.
7. Click the OK button to save the settings, you will find the target data series bar has been changed to light blue points.

3.7 Select The Data Source For The Target Data Series.

1. Right-click the chart, then click the menu item Select Data… from the popup menu list.
2. Then it will pop up the Select Data Source dialog window.
3. Select the Target Seire in the left Legend Entries (Series) list.
4. Then click the Edit button to pop up the Edit Series dialog window.
5. Clear the content in the Series X values text box and input the Target score row values in the text box by clicking cell B6 and Shift + D6.
6. The content in the Series X values text box is something like =’Excel Chart’!$B$2:$D$2.
7. It will show the data number list like = 90, 85, 95 after the above text box in the same row.
8. If you just see one number such as =90, you can click the OK button to close the Edit Series dialog and then click the Edit button to edit it again.
9. Clear the Series Y values text box content, then input the Helper Data row values in the text box by clicking cells B7 and Shift + D7 in order.
10. You can find the Series Y values text box content is something like =’Excel Chart’!$B$7:$D$7.
11. It will show the data number list like = 2.5, 1.5, 0.5 after the above text box in the same row.
12. Click the OK button to save the settings.
13. Click the OK button in the Select Data Source dialog again, then you will see the below picture.
14. You will find the actual data series has been changed to 3 light blue points in the chart.
    

3.8 Add Horizontal Error Bar To The Actual Data Series.

1. Click to select the Actual data series ( the 3 dark blue dots ) in the above chart.
2. Click the Chart Design tab —> Add Chart Element ( in the Chart Layouts group ).
3. Click the Error Bars —> Standard Error menu item in the popup menu list.
4. Then it will add the cross-error lines on the 3 dark blue dots.
5. Click to select the 3 horizontal lines in the above cross-error lines.
6. Then right-click the above 3 horizontal lines and click the Format Error Bars… item in the pop-up menu list.
7. Then it will show the Format Error Bars pane on the excel right side.
8. Click the Error Bar Options item in the Format Error Bars pane.
9. Expand the Horizontal Error Bar option and select the Minus radio button in the Direction section.
10. Select the No Cap radio button in the End Style section.
11. Select the Custom radio button in the Error Amount section, then you should click the Specify Value button to open the Custom Error Bars dialog.
12. Clear the input text box Negative Error Value, and input the Actual cell range in the text box by clicking cell B2 and Shift + D2.
13. The Negative Error Value input text box data should be like =’Excel Chart’!$B$2:$D$2.
14. Click the OK button to save the above settings.
15. Select the 3 vertical error lines in the 3 error bars and click the delete key to remove the 3 vertical error lines.
    

3.9 Change The Horizontal Error Bar Horizontal Line Color & Size To Show The Actual Data Bar.

1. Click the above 3 horizontal lines in the horizontal error bar, then it will display the Format Error Bars pane on the excel window’s right side.
2. Click the first item Fill & Line in the Error Bar Options.
3. Change the line color by selecting a color ( such as green color ) from the Color drop-down list.
4. Change the line height by editing the Width text box value to 10pt.
5. Then you can get the below chart picture.
   

3.10 Hide The Actual Data Series Markers.

1. Click on a dark blue dot to select the Actual data series ( the 3 dark blue dots ) in the above chart.
2. Right-click the above Actual data series ( right-click one of the 3 dark blue dots ).
3. Then click the item Format Data Series… in the popup menu list to show the Format Data Series pane on the excel window’s right side.
4. Click the Fill & Line option on the Format Data Series pane.
5. Then click the Marker option and expand the Marker Options item.
6. Select the None radio button under the Marker Options item.
7. Then the dark blue marker dots will be hidden.

3.11 Draw The Vertical Target Data Series Line.

1. Click the excel Insert tab —> Illustrations group —> Shapes —> Lines —> Line.
2. Then draw a vertical line on the chart, it will display the Format Shape pane on the excel window’s right side.
3. Change the vertical line color to red on the Format Shape pane.
4. Change the vertical line’s Width to 3pt.
5. Click to select the red vertical line and press the Ctrl + C key combination.
6. Click to select the Target data series ( the 3 light blue dots ), then press the Ctrl + V key combination to use the red vertical line to replace the light blue dots.
7. Click the original red vertical line and press the delete key to remove it.
8. Right-click the horizontal axis on the top of the chart, then click the Format Axis… menu item in the popup menu list.
9. Then it will show the Format Axis pane on the excel window’s right side.
10. Click the Axis Options item ( the fourth item from the left ) on the pane top area.
11. Expand the Axis Options and change the Bounds —> Maximum input text box value to 100.0
12. Then you will get the below horizontal bullet chart in excel.