0

How do I build a Tachometer Chart

Creating ‘tachometer’ charts in Excel
(There are some references to charts - pictures, which are inserted in the original article - If you would like the full article please email me at malcolma@jetreprots.com and I will foward it to you)

If you have a desire to put some WOW in your reports try adding some “analog” gauges to your report. These are similar to a speedometer or tachometer in the automotive world. A tachometer can be created using Excel Doughnut charts, and works great for showing things like percentages: revenue as percent of budget, sales as % of forecast, cost Vs budget, etc. It’s simple to create these using Excel, and with Excel 2007 you can add 3-d formatting effects to make them really cool.
The basic principle is to create two doughnut charts, one inside the other. The first (outer) chart will contain the “scale” of the gauge. So for example the third of the chart to the left is “Red”, middle third is “Yellow”, and right third is “Green”. The inner doughnut chart contains the indicator, or the “needle” in tachometer language.
First setup a series of data to represent how you want the outer doughnut to be segregated – 3 sections or 4 sections, or more. For a 3 section scale (red, yellow, green) where each section is of equal size, use 33, 34, 33, and 100. The 100 is the sum of the first three numbers, and will be used to create the “hidden” half of the doughnut. So here is the series to start with.
Scale data series
33
34
33
100

Next select the data series and then in Excel: Insert->Other charts-> Doughnut (use the “closed” doughnut not the exploded one). This should create a default formatted doughnut chart like this one.

Next I create the second doughnut. This one will display the value of your report, turned into a percentage. So to represent a needle pointing at 75% of budget on a scale of 0-100%, we want the needle to point at 45° (That’s about 2:30 on the clock face). To achieve this in a doughnut chart (remember the hidden bottom half) the chart data would be (75, 25, 100). The 100 in this series is the “bottom half” of the doughnut and we use formatting to make this half “disappear”.
Inner data series
75
25
100

I select the first chart (outer ring) and use copy and paste to place it around the second doughnut. The result is below


Next format the inner doughnut to create the “tachometer needle”. Select the doughnut and right click and pick “format data series”. The Format Data Series form top option is “Series Options”. Select this. Now rotate the doughnut to 270. Next grab the slider on “Doughnut Hole Size” and slide all the way to the right, and then back to the left. This makes the inner hole small. Now select “Fill” and click “No fill”. Select “Border Color” and then select “Solid line”. The result should look like the example below.

Select one segment of the outer ring by double clicking. Set the desired fill color for that segment. Use “no fill” for the bottom segment. Here is the result so far


Double click the lower segment of the inner doughnut and set the border color to “no line” for just this section and you get the result below.

You can add 3-D formatting, labels or other details at this point to get the desired look. To use this on a report connect the cells with the two numbers creating the segments of the inner doughnut (75, 25 in my example) with a ratio of two elements of the report. Data series is (ratio, 100-ratio, 100).

0 comments

Please sign in to leave a comment.