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.

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.

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.

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.

Andy Pope