Excel Bullet Graphs to Replace Gauges

Executive dashboards are now common in business. A dashboard provides key data that is concise, easy to read and to interpret. To do this, one of the common tools used are gauges.

Although it is widely used, gauges have one big flaw - huge space is needed for implementation. As a replacement, Stephen Few (Perceptual Edge) suggested a new bar graph variation called bullet graphs. Stephen created a good document discussing the bullet graph specification so I will not be discussing it here.

Instead, I will discuss how to implement the last variation of the bullet graph that Stephen Few discussed in the specification document. This version of the bullet graph will not only tell you how far you are from your target but will also show whether you are on track in meeting this target.

For demonstration, I will be re-creating in Excel the example bullet graph shown below.



Chart Data Preparation

First we need to prepare the data to be used by the chart. To create the graph, the appropriate values are provided in the cell range A3:E3. These values are used in controlling the information in the Chart Data.

Set cells A6, B6, A8 and B8 to zero. Cell A7 contains the Current value so this should point to A3. Cells C6 and C8 contain the Poor limit so these are set to C3. Cell E6 is pointing to the Target value in E3.

For the other cells, specify the following formula:

• =MAX(B3-A7,0) in cell B7
  
• =MAX(C6-SUM(A7:B7),0) in cell C7
• =D3-C6 in cells D6 and D8
• =MAX(SUM(C6:D6)-SUM(A7:C7),0) in cell D7
  

Creating the initial Stacked bar graph

To create the initial bullet graph, follow these steps.

1. Highlight the range A6:D8 then select Insert>Chart from the menu.
2. On the Standard Types tab in the Chart Wizard, select Bar from the Chart type and Stacked Bar in the Chart sub-type. Click Next.
3. Choose Columns in the Series in option. Click Next.
4. Uncheck all options in the Gridlines tab, the Show legend option in the Legend tab. Click Next then click Finish to show the stacked bar. It should look something like this.
   

Updating format of bullet graph

Now, we would need to change the color and presentation of the columns.

1. Right click any data point in the graph and choose Format Data Series. Click the Options tab and set the Gap width to 0. This will remove the gaps between each stacked bars.
   
2. For each data series, update formatting to match the one shown in the example. Remove all borders for each data series and change the color appropriately. Series 1 and 2 are both blue with series 2 set to a shade lighter. Series 3 and 4 are both gray with series 4 a bit lighter.
   
3. Remove the border from the Plot area.
4. Right click the X axis to format it. Click the Font tab and uncheck the Auto scale option. Click the Scale tab and uncheck Auto option for the Minimum, Maximum and Major unit . fields and set them to the desired value (0, 300, 50). I advised this step so that you control the scaling.
   

After these steps, you should something similar to this.

Adding the Target line.

The target line is added through the steps below.

1. Right click in any area of the chart and choose Source Data. Click the Series tab then click the Add button to introduce a new data series named Series 5. In the Values field, point it to cell E6. Click OK
2. Click the new data series added and update the formula from =SERIES(,,Sheet3!$E$6,5) to =SERIES(,Sheet3!$E$6,2,5).
3. Again, right click Series 5 and choose Chart type. In the Chart type, choose XY(Scatter). Click OK.
   
4. Right click the secondary X axis (the one on top) to format it. Click the Scale tab and uncheck Auto option for the Minimum, Maximum and Major unit . fields and set them to the desired value (0, 300, 50). This is done to ensure consistency of the scale.
5. Right click the secondary Y axis (the one on the right) to change the scale. Click the Scale tab and uncheck Auto option for the Minimum, Maximum and Major unit . fields and set them to the desired value (1,2, 1). This is needed to position the line in the middle.
   
6. Again, right click Series 5 and choose Format Data Series. In the Pattern tab, set the Marker to None. Click the Y Error Bars and Both for the Display, and set the Fixed value to .7. Click OK.
7. Right Click the Error bar for Series 5 and choose Format Error Bars. In the Patterns tab, change the appropriate color, weight and marker. Click OK.

Some more tidying up.

1. Right click the Chart area and choose Chart options. In the Axes tab, uncheck the all options except for Value (Y) axis of the Primary axis. Click OK.
   
2. Right click the Chart area and choose Format Chart area. Set the Border to none in the Patterns tab. Click OK.
   
3. Resize the bullet graph as desired and add the needed text. I recommend that the text should be in a cell instead of using the Title option in the chart.

After all these hard work, you should see something like this.

Bridging the automation gap

Process re-engineering is back on the management agenda. Pressured by the global economic downturn, big and small outfits alike are pursuing this to cut costs and return to profitability.

For mainframe development teams, process re-engineering is synonymous to automation. The management from the companies I currently and previously work understand that this is the way to go to improve the efficiency of the way deliver software.

To promote this, my previous employer required all developers to implement two productivity tools for the year. My current employer on the other hand has a similar but less direct approach - they pass on the responsibility to the team leads to pursue.

On the surface, both approaches looks feasible. By giving everyone the opportunity to pursue automation, you would expect a greater number of tools to be created. This should be easy as developers offers the same type of service to other non-IT departments. Right?

But the truth is, this is not always the case. In most teams I joined, there is no indication that automation is part of the development process. I ended building the team to be able to pursue this through mentoring and coaching.

How could it be that the IT personnel, the very people whose primary responsibility is closely linked to innovation, does not do the same in their everyday work?

Simple. Most mainframe developers does not have the necessary skills for automation. The facilities needed are different from the development tools used for delivering solutions to the client. Seldom do developers have the opportunity to learn to work with these tools in spite of their availability.

Part of the reason for this is our sole focus to solutions delivery that kept us from looking at our own processes. We are exhausted trying to deliver the next release that we have no time left to take a step back and discover how we can do our own tasks better.

To succeed, company perception needs to changed. Most often, automation is pursued by companies on a brink of disaster. Costs are running high and automation is deemed to be the quick fix.

Automation should be part of a long-term mainframe strategy. Focus should first be on training to enable developers to pursue this. Better results will follow if you have a good team behind it.