Bullet Graph

Bullet Graph is a great data visualization in Excel if you want to show key performance indicators using a quality scale. We will show you how to build a bullet graph in Excel using a few steps.

If you need to build a dynamic dashboard in Excel, this chart is yours!

How to build a bullet graph in Excel

Let’s arrange the data to create the initial data set! The first four rows will show the quality scale. Insert a new row and add the actual value, finally, place the target value in the sixth row.

One more important thing: the sum of the first column must be equal to 100%

initial data table for bullet graph

Select range C2:D8 and insert a stacked column chart.

Swap rows and columns

The result is a simple, stacked column chart. We need to swap data over the axis. The data on the X-axis will move to the Y-axis and vice versa.

To do that, select the chart. Go to the ribbon and select the Chart Design Tab. Apply the ‘Switch Row / Column’ command.

insert a 2d stacked bar chart

This function merges the bar charts into one! From now on, all data points are shown on a combo chart. OK, now we have six different areas.

how to switch row column

The first four-part is the grade section; the fifth value is the target value.

The last one will be showing the actual value.

format the bullet graph

Highlight the target value bar

Let’s see how to highlight the target value section. You can find it at the top of the bar chart. Right-click on the top section and select the ‘Change Series Chart Type’ from the menu.

Tip: In this phase, we’ll work on a single data point (target value). Don’t forget: the bullet graph contains six different elements.

highlight the target value bar

We need to change the chart type, so use the ‘Stacked Line with Markers’ type on the right-side pane.

Click on the secondary axis checkbox; now it’s active. You have to see a filled circle as a target value.

apply line chart with markers

Format the Actual Value Bar

In the picture below, we selected the actual value row. Select the secondary axis checkbox!

format the actual value bar on the bullet graph

Click OK to close the window.

Modify the Bullet Graph structure

OK, we’ll apply a simple trick: right-click on the bullet graph area. Now select ‘Format Data Series.’ (If you prefer this method, use the Ctrl + 1 shortcut.)

In the example, we will change the ‘Gap width’ from default to 450%. Using this small transformation will set the width of the chart.

increase the gap width

As we mentioned above, we have set the small marker as a Target Value. It’s time to create a real bullet graph marker from a single data point.

Select the data point. After that, right-click and choose ‘Format Data Series.’ To increase the size of the point, apply these steps below:

apply a marker

Locate the ‘Fill & Line’ tab. Select ‘Marker Options’ and choose a line type marker. Because the indicator is a little bit thin by default, increase the built-in marker size up to 25 or 30. Now the bullet graph looks better, is it not?

It looks like we are ready! We have only two simple steps left. Clean up the chart area and remove all unnecessary chart elements.

Because we want to display only one axis, delete the axis on the right side of the bullet graph! Select the axis and press the ‘Delete’ key.

Finally, you should have to pick a color scheme that is perfect for your concept. We recommend you using the classic KPI color scale from dark red to green.

Here is an example:

bullet graph example and tutorial

Final thoughts

Thanks for being us! Download the workbook and stay tuned.

Additional resources: