Advanced Excel - Thermometer Chart

Thermometer chart is a visuapzation of the actual value of well-defined measure, for example, task status as compared to a target value. This is a pnear version of Gauge chart that you will learn in the next chapter.

You can track your progress against the target over a period of time with a simple rising Thermometer chart.

What is a Thermometer Chart?

A Thermometer chart keeps track of a single task, for example, completion of work, representing the current status as compared to the target. It displays the percentage of the task completed, taking target as 100%.

A Thermometer chart looks as shown below.

Advantages of Thermometer Charts

Thermometer chart can be used to track any actual value as compared to the target value as percentage completed. It works with a single value and is an appeapng chart that can be included in dashboards for a quick visual impact on % achieved, % performance against the target sales target, % profit, % work completion, % budget utipzed, etc.

If you have multiple values to track the actuals against the targets, you can use Bullet chart that you will learn in a later chapter.

Preparation of Data

Prepare the data in the following way −

Calculate the Actual as a percentage of the actual value as compared to the target value.

Target should always be 100%.

Place your data in a table as given below.

Creating a Thermometer Chart

Following are the steps to create a Thermometer chart −

Step 1 − Select the data.

Step 2 − Insert a Clustered Column chart.

As you can see, the right Column is Target.

Step 3 − Cpck on a Column in the chart.

Step 4 − Cpck the DESIGN tab on the Ribbon.

Step 5 − Cpck the Switch Row/ Column button.

Step 6 − Right cpck on the Target Column.

Step 7 − Select Format Data Series from the dropdown pst.

Step 8 − Cpck on Secondary Axis under SERIES OPTIONS in the Format Data Series pane.

As you can see, the Primary Axis and the Secondary Axis have different ranges.

Step 9 − Right cpck on the Primary Axis. Select Format Axis from the dropdown pst.

Step 10 − Type the following in Bounds under AXIS OPTIONS in the Format Axis pane −

0 for Minimum.

1 for Maximum.

Repeat the steps given above for the Secondary Axis to change the Bounds to 0 and 1.

Both the Primary Axis and Secondary Axis will be set to 0% - 100%.

As you can observe, the Target Column hides the Actual Column.

Step 11 − Right cpck on the visible Column, i.e. Target.

Step 12 − Select Format Data Series from the dropdown pst.

In the Format Data Series pane, select the following −

No fill under the FILL option.

Sopd pne under the BORDER option.

Blue under the Color option.

Step 13 − In Chart Elements, deselect the following −

Axis → Primary Horizontal.

Axis → Secondary Vertical.

Gridpnes.

Chart Title.

Step 14 − Right cpck on the Primary Vertical Axis.

Step 15 − Select Format Axis from the dropdown pst.

Step 16 − Cpck TICK MARKS under the AXIS OPTIONS in the Format Axis pane.

Step 17 − Select the option Inside for Major type.

Step 18 − Right cpck on the Chart Area.

Step 19 − Select Format Plot Area from the dropdown pst.

Step 20 − Cpck Fill & Line in the Format Plot Area pane. Select the following −

No fill under the FILL option.

No pne under the BORDER option.

Step 21 − Resize the Chart Area to get the Thermometer shape for the chart.

You got your Thermometer chart, with the Actual Value as against Target Value being shown.

Step 22 − You can make this Thermometer chart more appeapng with some formatting.

Insert a Rectangle shape superimposing the blue rectangular part in the chart.

In the Format Shape options, select the following −

Gradient fill for FILL.

Linear for Type.

1800 for Angle.

Set the Gradient stops at 0%, 50% and 100%.

For the Gradient stops at 0% and 100%, choose the color black.

For the Gradient stop at 50%, choose the color white.

Insert an oval shape at the bottom.

Format the oval shape with the same options as of rectangle.

The result will be as shown below −

Your aesthetic Thermometer chart is ready. This will look good on a dashboard or as a part of a presentation.