Excel Yourself: No. 1 with a bullet chart

Excel Yourself

Is it time to retire gauge charts from your dashboard?

Question
Can you create a dial or gauge type chart in Excel?

Answer
Yes you can, but just because you can, doesn’t mean you should. Dial and gauge type charts, while useful on a car dashboard, don’t work so well on financial dashboards. They take up too much space to be effective for a space-limited dashboard. Creating dial or gauge charts in Excel is also reasonably complex and the effort is not worth the result.

Figure 1

Instead of a gauge chart you might consider using a bullet chart. This type of chart was named and designed by Stephen Few, an author who specialises in data visualisation techniques. He developed the bullet chart as an alternative to gauge charts.

Figure 1 has an example of a bullet chart. The grey column is the feedback score, in this case 75%. It is shown in comparison to three coloured bands. Up to 60% the score is considered “bad” (pink).

Between 60% and 80% it is considered a “good” score (light green). And above 80% is considered an “excellent” score (bright green).
 
Try a sample spreadsheet

Figure 2

The horizontal black line represents the target score (70%). It is easy to look at this chart and see immediately that the feedback score is above the target and in the “good” zone – no further investigation is required. Unfortunately there hasn’t been widespread acceptance of the bullet chart for dashboards. That might be because it looks different and requires some explanation. It is also not a standard Excel chart. Creating a bullet chart requires combining chart types and changing a number of settings. There are a few different ways to create bullet charts in Excel. I find the technique described below is reasonably scalable and flexible.

Figure 3


The layout for the data is straightforward; see Figure 2. But what the figures represent requires some explanation.

Both the score and target values represent their respective values. The bad value represents the highest value considered “bad”. The good value (20%) is the cumulative percentage above the bad percentage that is considered good (60% to 80% is good).

Figure 4

The excellent value (20%) is the percentage above the maximum good percentage that is considered excellent (80% to 100% is excellent). In this case the bad, good and excellent percentages add up to 100%, the maximum possible feedback score.

I find it easier to create a single bullet chart and then expand the range to create other bullet charts. Once you have created a single bullet chart then you can use that structure to build others.

Figure 5

Before we look at the instructions, it’s important to be aware that a chart is not a single thing. A chart is made up of separate parts, typically called objects. These chart objects can be manipulated individually. If you point your mouse to an object on a chart, a small tooltip will display the name of that object.

Read this first!
Here are a couple of chart tips before starting the instructions. To delete anything on a chart, select it and press the Delete key on your keyboard. To modify any part of a chart, right click it and select the format option at the bottom of the menu.

Figure 6

To create a bullet chart, follow these steps in sequence. Completely read each step before following the instructions for that step. (Note: All Excel format options use the US spelling of “color”.)

1. Select the range A1 to B6. Click the Insert ribbon tab, click the drop down for Column and select the Stacked Column option (second icon, top line). See Figure 3

2. With the chart selected, click the Design ribbon tab, click on the Switch the Row/Column icon. See Figure 4
This will create a single stacked column chart of all the percentages.

Figure 7

3. Click on the bottom segment of the column stack, which should be the score data series.

Right click that segment and select Format Data Series and then in the Series Options select the Secondary Axis option and change the Gap Width to 500% as per Figure 5.
Click Close.

4. Delete the right-hand vertical Axis. This will create a separate, thinner column for the score. See Figure 6

Figure 8

5. Right click the target data series (bottom red segment of the stacked column) and select Change Series Chart Type.

Select the Line with Markers icon. See Figure 7
Click OK.

This will insert a single point on the chart; see Figure 8.

This creates the basic structure for the bullet chart. If your chart doesn’t look like Figure 8, it might be best to start from scratch again.

Figure 9

Right click the single point and choose Format Data Series. There are four separate settings you need to change. Click the Marker Options and choose the Built-in option.

From the Type drop-down select the widest horizontal line. Change the Size to 20. Click the Marker Fill option and choose Solid fill and select the black colour in the Fill Color section.

Click the Line Color option and select No line. Click on the Marker Line Color option and select No Line.

Figure 9 shows all four settings. Click Close.

6. Change the colour for the score column (centre column) to dark grey. Change bad to pink, change good to light green and excellent to bright green. You can select each column segment and use the Fill Color option on the Home ribbon tab to make the colour changes.

Figure 10

7. Delete the chart gridlines, Change the highest value on the vertical axis by right clicking the vertical axis and selecting Format Axis.
Change the Maximum option to Fixed and the value to 1.0, then click Close. See Figure 10

8. Finally, reduce the width of the plot area using one of the double-headed arrow icons from around the edge of the plot area; see Figure 11. I prefer to have the horizontal black target line, wider than the grey score column.

Having created a single bullet chart you can easily add extra bullet charts by adding columns to the right of the data and then amending the data series to incorporate the extra columns.

Figure 11

The easy way to do that is to select the chart and note the coloured lines on the cells. See Figure 12

Adjust the width of the plot area (double-headed arrow shown in Figure 10) to change the size of the bullet charts. See Figure 13

This example was based on percentage scores, but bullet charts can also be used to plot revenue $, headcount numbers or any value that is measured against a target and other parameters. The number of parameters is also flexible. For example, you could use four levels: bad, average, good and excellent.

Figure 12

Bullet charts are much more space efficient than dials or gauges. You may need to spend some time educating users how to read them, but I believe in the long run it will be worth the effort.
This technique is based on the one described by Michael Alexander in his excellent book, Excel 2007 Dashboards & Reports for Dummies (Wiley).
 
Author’s Note: I strayed from the bullet chart specification by using colours in the charts. The specification recommends using black with grey as the background and using different grey shades to represent the different parameters. The use of colour was my decision to aid in the description and explanation of the chart.

Figure 13


 
Few's explanation of bullet charts in detail
 
Neale Blackwood CPA is CPA Australia’s resident Excel expert. He runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected]. For videos, worksheets and more visit itbdigital.com.
 
This article is from the July 2014 issue of INTHEBLACK.






How-to articles, sample spreadsheets and more

Excel yourself

Combine annual and monthly data in Excel with these simple steps


Making data relationships work

Easy steps to make data Relationships work in Excel


Excel Yourself

Easy tips for matching salary to job class in Excel