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

Hack 55 Hack Together a Simple Thermometer Chart

Excel doesn't provide a
thermometer chart. If you want one, you'll have to
construct it.

By using the Chart Wizard to create a
basic clustered column chart that compares values across categories,
and then manipulating the various chart elements, you can create a
visual, workable thermometer chart with little effort.

Set up some data, such as that shown in Figure 5-20,
and use the Chart Wizard to create a basic clustered column chart,
charting the data in rows. We used the range B3:C4.

Figure 5-20. Series data and creation of basic clustered column chart

Remove the legend and the gridlines
(click them to highlight them, then press Delete), and format the
Temperature Data Series to the Y2 axis. Do this by selecting the
series, right-clicking, selecting Format Data Series Axis,
and then selecting the Secondary Axis option, resulting in the chart
in Figure 5-21.

Figure 5-21. Two series plotted on the Y2 axis

Format both the Y1 (on the left) and Y2
(on the right) axes by highlighting each axis in turn,
right-clicking, and selecting Format Axis Scale. Set the
Minimum to 0, the Maximum to 100, the Major Unit to 10, and the Minor
Unit to 5. You'll see the chart shown in Figure 5-22.

Figure 5-22. Case series on Y1 axis, temperature series on Y2 axis, both axes formatted identically

Format the Case data series to White, format the Temperature series
to Red, and format the Plot Area to White. At this point, the
thermometer chart should be taking shape.

Reduce the Gap Width
property for both columns by right-clicking and selecting Format Data
Series Options. Finally, remove the X axis (highlight the
axis and click Delete) and then size and position to suit.

As Figure 5-23 demonstrates, by fiddling around a
bit with Excel's existing chart features, you can
come up with a thermometer chart that looks great and works
well.

Figure 5-23. Completed thermometer chart

Andy Pope