Bullet Graph is a great data visualization in Excel 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. Next, 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%
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. Next, go to the ribbon, select the Chart Design, and apply the ‘Switch Row / Column’ command.
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.
The first four-part is the grade section; the fifth is the target value.
The last one will be showing the actual value.
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: We’ll work on a single data point (target value) in this phase. Don’t forget: the bullet graph contains six different elements.
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. First, you have to see a filled circle as a target value.
Format the Actual Value Bar
In the picture below, we selected the actual value row. Next, select the secondary axis checkbox!
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.)
We will change the ‘Gap width’ from default to 450% in the example. Using this small transformation will set the width of the chart.
We have set the small marker as a Target Value, as we mentioned above. 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:
Locate the Fill & Line tab. Select ‘Marker Options’ and choose a line type marker. Because the indicator is slightly thin by default, increase the built-in marker size up to 25 or 30. Now the bullet graph looks better.
It looks like we are ready! We have only two simple steps left. First, 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 pick a color scheme that is perfect for your concept. We recommend using the classic KPI color scale from dark red to green.
Here is an example:
Final thoughts
Thanks for being us! Download the workbook and stay tuned.
Additional resources:
