Editor's note: Excel Hacks offers a collection of "100 Industrial-Strength Tips and Tools" that even the most experienced Excel users may never have thought of doing. Take today's hack, for example. Excel's Chart Wizard doesn't offer a speedometer chart, so that's the end of the road, right? Not to Andy Pope, who contributed this hack to the book. Here's how to create a "speedo" chart and have fun while you're doing it. Who says you can't be creative with Excel?!
Excel's Chart Wizard offers many different types of charts, except, unfortunately, a speedometer chart. A speedometer chart provides a slick way to represent data. With the hacks in this section, you can create a speedometer chart as well as add a scrollbar from the Control Toolbox toolbar that will alter the chart and change the data in the worksheet simultaneously.
The first thing you need to do is to set up some data, such as that shown in Figure 5-28, and create a doughnut chart. Doughnut charts work a bit like pie charts, but they can contain multiple series, whereas a pie chart cannot.
Figure 5-28. Data setup for speedometer chart.
Press Alt/c-~ to show the actual formulas on the worksheet. You also can select Tools . Options… . View and check the Formulas option to see the formulas, though that's a longer process.
Now highlight the range B2:B5 and select the Chart Wizard. In Step 1 of the Chart Wizard, select the Standard Types tab (this should be the default anyway). Then, under Chart Type, select the first doughnut. Click the Next button to go to Step 2 of the Wizard and make sure your data is charted in rows. Click the Next button to take you to Step 3. You can make changes in Step 3 if you need to, but they aren't necessary for this hack. Click Next to go to Step 4, and make sure the chart ends up as an object in the current worksheet (again, this is the default). Placing the chart as an object will make it easier to work with as you are setting up the speedometer (see Figure 5-29).
Figure 5-29. Basic doughnut chart.
Highlight the doughnut chart, slowly double-click the largest slice to select it, and then select Format Data Series . Options. Set the angle of this slice to 90 degrees. Click the Patterns tab and set the area and border of this slice to None, then click OK. Slowly double-click to highlight each of the other slices in turn, then double-click to get back to the Format Data Series dialog and color the other three bands as required. The doughnut chart should look like the one in Figure 5-30.
You need to add another series (Series 2) of values to form the slots for the dial labels, so again highlight the chart, right-click, select Source Data, and then select the Series tab. Click the Add button, which will create a new series, and then, under Values, select the range C2:C13. Click the Add button again to add a third series (Series 3) to create the needle, and under Values, select the range E2:E5. Your result should look like Figure 5-31.
At this point, the speedometer is starting to take shape. If you want to add labels to the speedometer, you can download a tool for adding them for free from John Walkenbach's Chart Tools, at http://j-walk.com/ss/excel/files/charttools.htm.
Part of this add-in, which unfortunately works only on Windows, is designed specifically for data labels. It enables you to specify a worksheet range for the data labels for a chart series. John's add-in also contains the features described in the following list.
Figure 5-30. Doughnut chart with 90% angle and no color or border on the first slice.
Figure 5-31. Doughnut chart with multiple series.
Chart Size: Enables you to specify an exact size for a chart, or enables you to make all charts the same size.
Export: Enables you to save charts as .gif, .jpg, .tif, or .png files.
Picture: Converts a chart to a picture (color or grayscale).
Text Size: Freezes the size of all text items in a chart. When the chart is resized, the text elements will not change size.
Chart Report: Generates a summary report for all charts, or a detailed report for a single chart.
Use the add-in to format Series 2 to display data labels using the range D2: D13. Keep Series 2 highlighted, then double-click to bring up the Format Data Series dialog. Go to the Patterns tab, and select None for both the Border and Area. Your chart should look like that shown in Figure 5-32.
Figure 5-32. Improved speedometer chart, with labels added.
Highlight Series 3, then right-click it and select Chart Type. Change this series to the default pie chart. Yes, it looks strange (see Figure 5-33). But rest assured, if the pie chart overlays the doughnut chart, you have done this correctly.
Next you need to reduce the size of the pie chart you just laid over the doughnut. To do this, explode it and reassemble the smaller slices. Select one section of the pie chart (two slow clicks on the desired slice will do this) and drag it outward. This will explode the pie and make it smaller, as shown in Figure 5-34.
Now select the whole pie, double-click it, and then select Format Data Series. Options. Change the Angle of the first slice to 90 degrees. Select each slice of pie in turn, then right-click, go to the Format Data Series dialog, then click the Patterns tab. Select None for the Border and the Area for all slices except the third slice, which needs to have a fill of Black. This will produce the chart shown in Figure 5-35.
Figure 5-33. Speedometer chart overlaid with a pie chart.
Figure 5-34. Pie chart exploded and resized.
If you want to add a legend, highlight the chart, then right-click and select Chart Options . Data Labels. Select Legend Key. This produces the speedometer in Figure 5-36. Now move, size, and edit the chart as required.
Figure 5-35. Speedometer chart with only the third series of pie chart showing color.
Figure 5-36. Speedometer chart showing legend.
Now that the speedometer chart is built, you need to create a scrollbar from the Control Toolbox toolbar and make the scrollbar and chart talk to each other.
To do this, right-click the toolbar area of the screen (the top of the screen where the Standard and Formatting toolbars are located) and select Control Toolbox. Now select the scrollbar tool and draw a scrollbar somewhere on the worksheet.
Select the scrollbar, right-click it, and select Properties. This will display the Properties dialog. Choose cell F3 as the linked cell, and set the Maximum value to 100 and the Minimum value to 0. When you close the Properties dialog and move the scrollbar onto the chart, you'll see something like that shown in Figure 5-37.
Figure 5-37. Final speedometer chart.
Clicking the arrows or dragging the slide bar will alter the speedometer, but remember, this also will change the data on the worksheet to which it is linked.
— Andy Pope
In two weeks, we'll offer two more hacks from Excel Hacks. The first will be on removing phantom workbook links; the second on returning the nth occurrence of specified data.
David Hawley is a professional Microsoft Excel consultant whose company, OzGrid Business Applications, offers services in all aspects of Excel, and VBA for Excel.
Raina Hawley is a professional Microsoft Excel consultant whose company, OzGrid Business Applications, offers services in all aspects of Excel, and VBA for Excel.
Return to WindowsDevCenter.com.
Copyright © 2009 O'Reilly Media, Inc.