How to Make a Vertical Bullet Chart

Anatomy of a Bullet Chart

Vertical bullet chart 01

How to Make a Vertical Bullet Graph

Vertical bullet chart 02

Making the Chart

Step 1: Create your chart. Select cells E5 through G6 and select the “Insert” tab and in the “Charts Section” click the down arrow under “Column” and select the first selection under 2-D bar for “Clustered Column” chart type. Please see example in attached Excel worksheet.

Vertical bullet chart 03

Step 2: Delete the legend. Click the legend and press delete.

Vertical bullet chart 04

Step 3: Format rows and columns. Click the chart and select from “Chart Tools” tab and click “Switch Row/Column”.

Vertical bullet chart 05

Step 4: Overlap the data. Click the green bar and right click – a pop up box “Format Data Series” appears. Select “Series Options” (left side of the box) and in the right hand side under “Series Overlap” section move the arrow over to 100% overlapped and in the “Gap Width” section move the arrow over to “No Gap”. Click “Close”.

Vertical bullet chart 06

Step 5: Rearrange the bars. Click the green bar and under chart tools tab select “Select data”. Click the entry (Poor, Satisfactory, and Good) and use the up and down arrows located in the top right section on the “Legend Entries” box to move the entry up or down until you have “Good” on the top, “Satisfactory” and “Poor” at the bottom of the list. Click “OK”.

Vertical bullet chart 07

Step 6: Change the color of each of the bar sections to the same color in graduated sections. Right click on the blue section and select “Format Data Series”. Click on “Fill” in the left hand box inside the pop up window and then select “Solid Fill” in the right hand box and go down to the color icon and select the down arrow and select the darkest blue. Click “Close”. Repeat this step with the next two sections (red then green) selecting a lighter shade of blue each time to create a graduated color scheme.

Vertical bullet chart 08

Step 7A: Add Revenue Budget data (above in cells C5 and C6). Click the chart and then click the Chart Tools” tab at the top of the screen. Click “Select Data” and click the “Add” under “Legend Entries” to add National Average data. Under “Series Name” click the “spreadsheet icon” and then click on cell C5 above and click on the spreadsheet icon on the right hand side of the box and under “Series value” click on the “spreadsheet icon” and click cell C6 above and then click on the spreadsheet icon on the right hand corner of the box and then click on ‘OK”.

Vertical bullet chart 09

Step 7B: Add Revenue Actual data (above in cells B5 and B6). Click the chart and then click the Chart Tools” tab at the top of the screen. Click “Select Data” and click the “Add” under “Legend Entries” click “Add” and under “Series Name” click the “spreadsheet icon” and then click cell B5 (Actual) above and click on the spreadsheet icon on the right corner of the box and under “Series value” click the “spreadsheet icon” and then cell B6 above. Click on the spreadsheet icon on the right corner of the box. Click “OK”.

Vertical bullet chart 10

Step 8: Click the blue section of the graph and right click. A pop box will come up then select “Change Series Chart Type”. Select “XY Scatter” in the left hand section of the pop-up box. Select “Scatter with only markers”, which is the first selection under “XY Scatter” in the right hand section of the pop-up box. Click “OK”.

Vertical bullet chart 11

Step 9: Click on the purple section of the graph and right click. A pop box will come up, select “Change Series Chart Type”, select “XY Scatter” in the left hand section of the pop-up box, select “Scatter with only markers.” , which is the first selection under “XY Scatter” in the right hand section of the pop-up box. Click “OK”. You will end up with two X’s on the left hand axis of the graph.

Vertical bullet chart 12

Step 10: Format into Bullet Graph. Click the first data point (225 value), select “Layout tab”, select “Error Bars” and select “Error Bars with Percentage”. You will end up with something that looks like a cross over the “X”.

Vertical bullet chart 13

Step 11: Create Vertical Line. Click the horizontal part of the cross and press ‘delete’ which will leave you with vertical line over the “X”.

Vertical bullet chart 14

Step 12: Click the chart and then click the “Layout Tab” in the first section click the down arrow next to “Chart Area” and select “Series “Revenue Budget” Y Error Bars”. Click “Format Selection” and a pop up box appears. In the left hand side of the pop up box click “Vertical Error Bars” and in the right hand section, under “Display…Direction” click “Minus” and “Display…End Style”, click “No Cap”. Under ‘Error Amount’ click “Percentage” and change percentage to 100. Click “Line Style” in the left hand side under “Width” use the up arrow to change the “Width” to 3pt. to make a nice heavy line. Click “Close”.

Vertical bullet chart 15

Step 13: Create Horizontal Line. Click the second data point, select Layout tab, select “Error Bars” and select “Error Bars with percentage”. You will end up with a cross over the “X”.

Vertical bullet chart 16

Step 14: Click the vertical part of the cross and press ‘delete’ which will leave you with horizontal line over the “X”.

Vertical bullet chart 17

Step 15: Click the chart and click on the Layout Tab under “Current Section” Click the down arrow next to “Chart Area” and select “Series “Revenue Actual X Error Bars” then click on ‘Format Selection” a pop up box comes up a In the left hand side of the pop up box click on “Horizontal Error Bars” and in the right hand section, under “Direction” click on “Both”. Under “End Style”, click on “No Cap”. Click on “Line Style” in the left hand side under “Width” use the up arrow to change the “Width” to 5 pt. to make a nice heavy line. Click “Close”.

Vertical bullet chart 18

Step 16: Clean up Chart Junk. Select Markers from each of the lines by clicking the marker, right click and select “Format Data Series”. Select “Marker Options” and select None. Click “Close”. Click the Y axis and right click. Select “Format Axis”. Set the “Minimum” to 0 and “Maximum” to 300. Right click the x axis and press delete. Right click the chart and select “Format Chart Area” and select “Border Color” and click “No Line”. Click “Close”. To resize chart to desired size click the chart and drag the handles to the desired size.

Vertical bullet chart 19

Step 17: Add a Title. Click on the chart and select “Layout Tab”. Click “Axis Titles”. Select “Primary Horizontal Axis Title” and click “Title Below Axis”.

Vertical bullet chart 20

Step 18: Edit Title. Click on “Chart Title” and highlight the title and type Revenue (1,000s). Change the font size if necessary.

Vertical bullet chart 21

Step 19: Reposition Title to Top of Chart. Click on title and drag title to the top.

Vertical bullet chart 22

Share
This entry was posted in Excel Tips & Tricks, Graphs. Bookmark the permalink.

One Response to How to Make a Vertical Bullet Chart

  1. Srinivasan says:

    Please let us know how to create vertical bullet chart with multiple bars

Leave a Reply

Your email address will not be published. Required fields are marked *


3 − two =