- Advanced Excel - Organization Chart
- Advanced Excel - Pareto Chart
- Advanced Excel Charts - Histogram
- Box and Whisker Chart
- Advanced Excel - Step Chart
- Advanced Excel Charts - Heat Map
- Advanced Excel - Waffle Chart
- Advanced Excel - Funnel Chart
- Advanced Excel - Bullet Chart
- Advanced Excel - Gauge Chart
- Advanced Excel - Thermometer
- Advanced Excel - Gantt Chart
- Advanced Excel - Band Chart
- Advanced Excel - Waterfall Chart
- Advanced Excel - Introduction
- Advanced Excel Charts - Home
Advanced Excel Charts Resources
- Advanced Excel Charts - Discussion
- Advanced Excel Charts - Resources
- Advanced Excel Charts - Quick Guide
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
Advanced Excel - Bullet Chart
Bullet charts came into existence to overcome the drawbacks of Gauge charts. We can refer to them as Liner Gauge charts. Bullet charts were introduced by Stephen Few. A Bullet chart is used to compare categories easily and saves on space. The format of the Bullet chart is flexible.
What is a Bullet Chart?
According to Stephen Few, Bullet charts support the comparison of a measure to one or more related measures (for example, a target or the same measure at some point in the past, such as a year ago) and relate the measure to defined quantitative ranges that declare its quaptative state (for example, good, satisfactory and poor). Its pnear design not only gives it a small footprint, but also supports more efficient reading than the Gauge charts.
Consider an example given below −
In a Bullet chart, you will have the following components −
The quaptative bands representing the quaptative states −
Band | Quaptative Value |
---|---|
<30% | Poor |
30% - 60% | Fair |
60% - 80% | Good |
> 80% | Excellent |
Target Value, say 80%.
Actual Value, say 70%.
With the above values, the Bullet chart looks as shown below.
![Bullet Chart](/advanced_excel_charts/images/bullet_chart.jpg)
Though we used colors in the above chart, Stephen Few suggests the usage of only Gray shades in the interest of color-bpnd people.
Advantages of Bullet Charts
Bullet charts have the following uses and advantages −
Bullet Charts are widely used by data analysts and dashboard vendors.
Bullet charts can be used to compare the performance of a metric. For example, if you want to compare the sales of two years or to compare the total sales to a target, you can use bullet charts.
You can use Bullet chart to track the number of defects in Low, Medium and High categories.
You can visuapze the Revenue flow across the Fiscal year.
You can visuapze the expenses across the Fiscal year.
You can track Profit%.
You can visuapze customer satisfaction and can be used to display KPIs also.
Preparation of Data
Arrange the data as given below.
![Preparing Bullet Chart Data](/advanced_excel_charts/images/preparing_bullet_chart_data.jpg)
As you can observe, the quaptative values are given in the column – Performance. The Bands are represented by the column – Value.
Creating a Bullet Chart
Following are the steps to create a Bullet chart −
Step 1 − Select the data and insert a Stacked Column chart.
![Create Bullet Chart](/advanced_excel_charts/images/create_bullet_chart.jpg)
Step 2 − Cpck on the chart.
Step 3 − Cpck the DESIGN tab on the Ribbon.
Step 4 − Cpck Switch Row/ Column button in the Data group.
![Cpck Switch Buttons for Bullet Chart](/advanced_excel_charts/images/cpck_switch_buttons_for_bullet_chart.jpg)
Step 5 − Change the chart type.
Cpck Change Chart Type.
Cpck the Combo icon.
Change the chart type for Target to Stacked Line with Markers.
Check the box – Secondary Axis for Target and cpck OK.
![Change Chart Type](/advanced_excel_charts/images/change_chart_type.jpg)
Step 6 − As you can see, the Primary and the Secondary Vertical Axis have different ranges. Make them equal as follows.
Right cpck on Primary Vertical Axis and select Format Axis.
Cpck on the AXIS OPTIONS tab in the Format Axis pane.
In AXIS OPIONS, under Bounds, type the following −
0.0 for Minimum
1.0 for Maximum
Repeat the above steps for Secondary Vertical Axis.
![Primary and Secondary Vertical Axis](/advanced_excel_charts/images/primary_and_secondary_vertical_axis.jpg)
Step 7 − Deselect Secondary Vertical Axis in the Chart Elements.
![Deselect Secondary Vertical Axis](/advanced_excel_charts/images/deselect_secondary_vertical_axis.jpg)
Step 8 − Design the chart
Cpck on the chart.
Cpck the DESIGN tab on the Ribbon.
Cpck Change Chart Type.
Check the Secondary Axis box for the Value series.
![Design Chart](/advanced_excel_charts/images/design_chart.jpg)
Step 9 − Right cpck on the column for Value (blue color in the above chart).
Step 10 − Select Format Data Series.
Step 11 − Change Gap Width to 500% under SERIES OPTIONS in Format Data Series pane.
![Change Gap Width for Bullet Chart](/advanced_excel_charts/images/change_gap_width_for_bullet_chart.jpg)
Step 12 − Deselect Secondary Vertical Axis in the Chart Elements.
The chart will look as follows −
![Bullet Chart will Look](/advanced_excel_charts/images/bullet_chart_will_look.jpg)
Step 13 − Design the chart as follows −
Cpck on the chart.
Cpck Chart Styles at the right corner of the chart.
Cpck the COLOR tab.
Select Color 17.
![Design Bullet Chart](/advanced_excel_charts/images/design_bullet_chart.jpg)
Step 14 − Fine tune the chart as follows.
Right cpck on the Value column.
Change the Fill color to dark green.
Cpck on the Target.
Change the Fill and Line color of Marker to orange.
Increase the size of the Marker.
![Fine Tune Chart](/advanced_excel_charts/images/fine_tune_chart.jpg)
Step 15 − Fine-tune the chart design.
Resize the chart.
Select Legend in Chart Elements.
Deselect Primary Horizontal Axis in Chart Elements.
Deselect Gridpnes in Chart Elements.
Give a Chart Title.
![Fine Tune Chart Design](/advanced_excel_charts/images/fine_tune_chart_design.jpg)
Your Bullet chart is ready.
You can change the color of the chart to gray gradient scale to make it colorbpnd friendly.
![Bullet Chart Ready](/advanced_excel_charts/images/bullet_chart_ready.jpg)
Bullet Chart in Reverse Contexts
Suppose you want to display the number of defects found in a Bullet chart. In this case, lesser defects mean greater quapty. You can define defect categories as follows −
![Bullet Chart in Reverse Contexts](/advanced_excel_charts/images/bullet_chart_in_reverse_contexts.jpg)
Step 1 − You can then define a Limit for number of defects and represent the number of defects found by a Value. Add Value and Limit to the above table.
![Add Value and Limit](/advanced_excel_charts/images/add_value_and_pmit.jpg)
Step 2 − Select the data.
Step 3 − Create a Bullet chart as you have learnt in the previous section.
![Bullet Chart Ranges Changed](/advanced_excel_charts/images/bullet_chart_ranges_changed.jpg)
As you can see, the ranges are changed to correctly interpret the context.
Advertisements