Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 57 Create a Speedometer Chart

You can create a really impressive, workable
speedometer (or "speedo") chart,
complete with moving needle, by using a combination of doughnuts and
pie charts. The added touch is that you can control the speedometer
via a scrollbar.

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 set up for speedometer chart

Press Alt/-~ 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.

Figure 5-30. Doughnut chart with 90% angle and no color or border on the first slice

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.

Figure 5-31. Doughnut chart with multiple series

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//image/library/english/10052_charttools.

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.

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.

Figure 5-33. Speedometer chart overlaid with a pie chart

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.

Figure 5-34. Pie chart exploded and resized

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-35. Speedometer chart with only the third series of pie chart showing color

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-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