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.
Related:
How To Find Duplicates In Excel? The Best Guide
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.
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.
- 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.
- 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.
- In the Change Series Chart Type menu, locate the Target data series and change the chart type to Stacked line with Markers.
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.
- 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.
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
- 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.
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 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
- 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
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.
Suggested Reads:
Create An Excel Dashboard In 5 Minutes – The Best Guide
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:
- Excel Formulas: Basics and Beyond – Recorded Webinar
- Mastering Excel Lookups: VLOOKUP, HLOOKUP and XLOOKUP – Recorded Webinar
- How to Mail Merge in Microsoft Word: Microsoft Word Tutorial
- How to Print Envelopes in Microsoft Word: Microsoft Word Tutorial
- Data Models, PivotTables, and Power Query in Excel – Recorded Webinar
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.