Use a Thermometer-Style Chart in Your Excel Dashboards

By Michael Alexander

Sometimes you need to display performance against a target in your Excel dashboards and reports. The target could be anything from a certain amount of revenue to a number of boxes shipped or to phone calls made. The business world is full of targets and goals. Your job is to find effective ways to represent performance against those targets.

A thermometer-style chart offers a unique way to view performance against a goal. As the name implies, the data points shown in this type of chart resemble a thermometer. Each performance value and its corresponding target are stacked on top of one another, giving an appearance similar to that of mercury rising in a thermometer. In this figure, you see an example of a thermometer-style chart.

image0.jpg

To create this type of chart, follow these steps:

  1. Starting with a table that contains revenue and target data, plot the data into a new column chart.

  2. Right-click the Revenue data series and choose Format Data Series.

  3. In the Format Data Series dialog box, select Secondary Axis.

  4. Go back to your chart and delete the new vertical axis that was added; it’s the vertical axis to the right of the chart.

  5. Right-click the Target series and choose Format Data Series.

  6. In the dialog box, adjust the Gap Width property so that the Target series is slightly wider than the Revenue series — between 45% and 55% is typically fine.