Excel Bullet Chart (Table of Contents)
Introduction to Excel Bullet Chart
We all have seen dashboards at some point in time. Whether in our office or while working for different clients with different technologies. We always come up with dashboards. The main challenge with the dashboard is, we have limited space to present all our analytical insights (maybe only one page sometimes). Most of the dashboards consist of visuals that can be used for giving a simple and crisp idea about the things to the user. If you have seen any dashboards, you must be agreeing with the statement “Visuals consume a lot of space as well”. Because of this issue, we have to be very smart while choosing the visuals for the dashboard. Choosing a right, crisp and compact visual is a need of the dashboard and this is where bullet charts have upper hand over other visuals.
Bullet Chart was introduced towards this world by one of the dashboard expert Stephan few and since it’s inclusion, it is one of the widely used charts when you have to measure performance over the target.
This chart can be used as a KPI speedometer under Dashboard and can compare all the necessary KPI values against the target value in a crisp solo chart that consumes space very lesser than the other charts.
We will see through an example of how to create a bullet chart in Excel.
How to Create Bullet Chart in Excel?
Let’s understand how to Create Bullet Chart in Excel with Some Steps. Suppose we have a data of Employee Performance as shown in the screenshot below:
We have segregated the employee performance under four categories namely Poor, Fair, Good and Excellent. Out of all the employee we have, 55% employee have been performing Poorly, 15% is fair in performance, 20% performing Good and 10% is the Excellent performers. The actual performance value for one particular employee is 72% and Target Value for Employee Performance is 95% which management is expecting to be achieved.
4.8 (3,323 ratings)
View Course
Let’s use this information to create a decent bullet chart under Excel.
There is no direct inclusion of Bullet Chart under Excel and hence we need to do some modifications in the general bar chart which we create. Follow the steps below:
Step 1: Select Data from A1:B7 and click on Insert tab on the Excel ribbon. Navigate towards Charts group inside the Insert menu.
Step 2: Inside the Charts group, select the Insert Column or Bar Chart dropdown and select the Stacked Column Chart under the 2D-Column Charts section.
You should see a column chart as shown in the screenshot below:
We can clearly see that the chart is plotted with each category as a separate data point. We don’t want it in that way. We wanted those categories to be stacked within each other. Therefore, we will follow the step below to make it a stacked bar chart.
Step 3: Click on the Chart. As soon as you click on it you will get new tabs active on the Excel ribbon named Design and Format. Navigate to the Design tab and under Data Group, click on Switch Row/Column option.
This will switch the data rows and columns and the graph layout will look like the one below:
Step 4: Now, right-click on the bar colored in green which is associated with the Target Value and select Change Series Chart Type… option. It will open up a change chart type window.
Step 5: Change the Chart Type for Target Value as Stacked Line with Market and tick the Secondary Axis option.
Now you can see instead of a bar, you have a green dot that represents the Target Value.
Step 6: Now, click on the bar representing Average Value and right-click on it to select the Change Series Chart Type… option.
Step 7: On the Change Chart Type window, just check the Secondary Axis box for the Actual Value series as shown below:
You should now see a chart as in the below screenshot:
Step 8: Right-click on the blue bar that represents the Actual Value series and select the Format Data Series option.
Step 9: Under the Format Data Series window that pops-up at the rightmost corner of the Excel sheet, change the Gap Width to its maximum value (i.e. 500).
You will see that the Actual Value Series is overlapped under the entire Stacked bar chart as shown below:
Step 10: Now, select the Target Value dot and right-click on it to select the Format Data Series option.
Step 11: In the Format Data Series pane that opens up at the rightmost corner, click on Fill & Line > Marker > Marker Options.
Step 12: Change the Marker Option as Built-in and inside it, change the Type as dash from the dropdown and make the size as 20.
Step 13: Navigate towards the Fill section and change the color as Red instead of Green.
Step 14: Under the Border section, change the border as No Line instead of the preselected called Automatic.
I will change the color for each bar to make it look nicer and vibrant in this case. The final bullet chart should look like the one below:
You can add the Chart Title for this chart to make it look nicer. This is by far the Single -KPI bullet chart. Using the same technique you also can produce a Multi-KPI bullet chart and make it a permanent member of your dashboard instead of the Speedometer that consumes a lot of space. Just keep an eye on the formatting of the bullets (bars within). We need to have uniformity throughout the chart for different KPI parameters.
This article ends here and lets us wrap the things for this article with some points to be remembered associated with bullet charts.
Things to Remember About Excel Bullet Chart
- Bullet chart is not already present under Microsoft Excel and is just a need of the hour due to which it came in the picture.
- We need to combine multiple charts (Ex. Stacked Bar Chart, Marker and Line Chart) together in order to get the expected bullet chart done.
- This chart is a need of hour chart. Since the conventional speedometers consume a lot of space while creating a dashboard, in order to reduce that space as well as making the dashboard more compact, this chart was introduced.
Recommended Articles
This is a guide to Bullet Chart in Excel. Here we discuss How to use Bullet Chart in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
