How to Make an Excel Bullet Chart – 2 Easy Examples

Note: This guide on Bullet Chart in Excel is suitable for Excel 2010 and later versions including Office 365.

Excel bullet charts are one of the best ways to represent performance-related data in Excel. Use it to display performance achieved in various parameters and compare it against a set target.

Excel Goal Seek—the Easiest Guide (3 Examples)

Create A Pivot Table In Excel—the Easiest Guide

It comes in very handy when you need to display performance charts in a space-constrained Excel dashboard.

In this guide, I’ll walk you through how to create an Excel bullet chart, step-by-step with illustrations. Download the sample Excel Sheet here to follow along with me.

You’ll learn:

What is an Excel Bullet Chart?
How to Create an Excel Bullet Chart?
How to Add Multiple Columns of Data to Bullet Chart?

What is an Excel Bullet Chart?

A bullet chart is nothing but a stacked column chart that plots an achievement bar inside a comparison range along with a target marker.

As shown in the above image, a bullet chart displays these three things.

Target Marker: It is a marker to highlight and represent the target value.

Achievement Bar: It is a simple column bar chart that represents the actually achieved value. Notice that it is narrower than the comparison range for ease of understanding.

Comparison Range: It is the background of the bullet chart and has a gradient colour theme for all the performance ranges. It represents the ranges or stages of achievement.

It is the perfect tool when you want to compare a single achieved parameter with its target value. Let’s say you want to showcase if the test scores of your employees are hitting their targets are not. Use a bullet chart to effectively represent this data.

The Bullet Chart was conceptualised by Stephen Few and is based on the broad idea of progress bars and thermometer charts. Over the years it has become a very preferred tool in Excel dashboards.

Now, I’ll show you how to create a bullet chart from scratch.

Also Read:

How To Use Excel Countifs: The Best Guide

Excel Conditional Formatting -the Best Guide (Bonus Video)

The Best Excel Project Management Template In 2021

How to Create an Excel Bullet Chart?

To create an Excel bullet chart, follow these steps. It might seem complicated at first, but trust me, it is not. Once you get a hang of it, you will be able to create one in seconds.

Use the sample Excel sheet, if you need to follow along with me.

Organise your data for the Bullet Chart. First, list the range widths for the Comparison Ranges in Ascending Order.

List the Comparison Ranges in sequential ascending order. Add the Achieved Value and Target Value in the end. — *List the **Comparison Ranges** in sequential ascending order. Add the **Achieved Value** and **Target Value** in the end.*

Then, list the Achieved value and Target Value in the same column.

Select your Data with headers and click on the Stacked Column Chart option under the Insert tab. You can find it in the Charts group.

Select your Data Range with headers and click on the Stacked Column Chart option — *Select your Data Range with headers and click on the **Stacked Column Chart** option*

Convert this new chart into a stacked column chart. To do this, select the chart and click on the Switch Rows and Columns option under the Chart Design tab.

Click on the Switch Rows and Columns option to convert it into a Stacked Column Chart — *Click on the **Switch Rows and Columns** option to convert it into a Stacked Column Chart*

The green area depicts the target value. We need to convert this into a line indicator.

To do this right-click on the green bar and click on the Change Series Chart Type option.

Right Click on the Green bar and select the Change Series Chart Type option — *Right Click on the Green bar and select the **Change Series Chart Type** option*

In the Change Series Chart Type menu, locate the Target data series and change the chart type to Stacked line with Markers.

Secondary Axis Change Series Chart Type — *In the **Change Series Chart Type** menu change the chart type of the **Target** data series to **Stacked Line with Markers** and check the plot on the **Secondary axis** tickbox.*

Make sure to check the plot on the secondary axis button. The target marker is visible in the form of a small green dot.

Click and delete the secondary axis to make the scale uniform.

Delete the Secondary Axis to make the scale of the Bullet Chart uniform. — *Delete the **Secondary Axis** to make the scale of the Bullet Chart uniform.*

We need to change the marker type to a line. To do this, right-click on the green dot and click on the Format Data Series option.

In the Format Data Series pane, locate Marker Options and change the marker to line from the Built-in options. Also, change the size of the marker suitably.

Change the Marker Type, Colour, and Size in the Format Data Series Pane — *Change the **Marker Type**, **Colour**, and **Size** in the **Format Data Series** Pane*

You can also change the colour of the marker by picking any colour from here.

Next, we have to plot the achieved-value bar in the secondary axis. To do this right-click on the blue achieved-values bar and select the Change Series Chart Type option

Check the plot on the Secondary axis tickbox for the Value data series in the Change Series Chart Type menu — *Check the plot on the **Secondary axis** tickbox for the Value data series in the **Change Series Chart Type** menu*

Now we need to change the width of the achieved value data bar for better clarity. To do this, click on the blue bar to access the Format Data Point pane.

In the Format Data Point, pane click on the “Series” option” to change the Gap Width and increase it suitably.

Next, we have to change the comparison background of the bullet chart. To do this, just right click on the background bar and select the Format Data Point option.

*Change the Fill Colours of the Comparison Range Data Bars suitably*

In the Format Data Point pane, click on the “Fill” option to change the colour suitably. Repeat the same process for all other background bars but keep decreasing the colour shades to indicate the range difference.

Finally, change the colour of the achievement bar suitably by following the same process.

Change the Fill Colour of the Achieved Value Data Bar suitably — *Change the **Fill** Colour of the **Achieved Value** Data Bar suitably*

Change the size of the bullet chart if needed. Your bullet chart is all set now.

How to Add Multiple Columns of Data to Bullet Chart?

You can also add multiple columns of data to your bullet chart. To do this, just click on the bullet chart to make the data cell selection visible.

Now, drag the blue coloured cell selection to include the other columns in your bullet chart

Drag the Cell selection to include additional Data ranges — *Drag the **Cell selection** to include additional Data ranges*

To remove these unwanted green lines click on them to access the Format Data point pane. Then click on the “No Line” under Line options

*Format the appearance of the Bullet Chart if needed*

To change the colour of these blue bars, click on them to access the Format Data Series pane. Change their colour using the colour picker.

Your Excel bullet Chart with multiple columns is ready.

Dynamic Dropdown Lists In Excel – Top Data Validation Guide

Predict Future Values Using Excel Forecast Sheet – The Best Guide

FAQs

What is the purpose of a bullet chart?

A bullet chart is used to track the performance of a parameter against a target value in a single column. It saves space and is visually intuitive inside a dashboard.

How do I create an Excel bullet chart ?

You can create an Excel bullet chart using the Stacked Column Chart option. Follow these steps.
1. Switch the rows to columns in the stacked column chart
2. Edit the appearance of the achievement value and target value by plotting them on a secondary axis.
3. Finally, format it to get the desired appearance.

Latest Posts:

Let’s Wrap Up

In this guide, I have explained how to make a bullet chart in Excel in a step-by-step manner. We also saw how to add multiple columns to a bullet chart. If you have any doubts regarding bullet charts or any other Excel feature, please let us know in the comments below.

If you need more high-quality Excel guides, please check out our free Excel resources centre.

Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advance Excel courses with in-depth training modules.

Adam Lacey

Adam Lacey is an Excel enthusiast and online learning expert. He combines these two passions at Simon Sez IT where he wears a number of different hats.When Adam isn't fretting about site traffic or Pivot Tables, you'll find him on the tennis court or in the kitchen cooking up a storm.

How to Make an Excel Bullet Chart – 2 Easy Examples

What is an Excel Bullet Chart?

How to Create an Excel Bullet Chart?

How to Add Multiple Columns of Data to Bullet Chart?