Home ยป Charts

Thermometer Chart in Excel – Making, Usage, Formatting

The Thermometer Chart in Excel is not unbuilt. But we can plot this chart type by using the 3D Clustered Column Chart in Excel. Let us see the usage ๐Ÿ˜Ž

What is Thermometer Chart in Excel?

A Thermometer Chart helps us to study the completion level or the percentage of target achieved for sales. This chart type lets us visualize the completeness of any project. It has the following components:-

  • Horizontal Axis – This axis represents the Labels or Categories.
  • Total Data Series – This data series would represent the total of the target that has to be achieved. It will always be 100%.
  • Actual Data Series – This data series will let us know how much percentage of the total data series has been achieved.
  • Primary Vertical Axis – It has the scale for either Total Data Series.
  • Secondary Vertical Axis – It has the scale for Actual Data Series
example of a thermometer chart in excel

Limitation of Thermometer Chart in Excel

There is an important exception to understand about the Thermometer Charts. In any case, if the value of the Actual Data Point (102%) exceeds the Total Data Point (100%) then the chart will show that the Actual data point has reached the Total Data Point. On the Chart, the Actual will not exceed the total (100%). The reading of the thermometer cannot go beyond 100% (Total ). The Actual Data Point will represent 100% on the chart.

limitation of thermometer charts in Excel

As you can see in the chart, the target (100%) has been achieved for Feb (108%) and April (135%) but the maximum representation would be up to 100%.

Making a Thermometer Chart in Excel

Let us say we want to make a monthly expenditure warning chart, that lets us know whether the limit for monthly expenses has been crossed. For that, we need to assume a Monthly Expense Limit. Let us assume it as ₹7500. Below is the data.

We can calculate the Percentage of Money spent (Actual Data Series ) by using the formula in cell D7.

=B8/D4

This will return the percentage of money spent as follows. Make sure to set the Number Format of Cells D7 and E7 as Percentages.

making a thermometer chart in excel raw data calculation

We will insert a 2D Clustered Column Chart for the range of cells D6:E7.

Correcting the Chart Data Series

The chart inserted in the above section is as follows.

correcting the data series in a thermometer chart

As you can see that both vertical columns are plotted as one data series. We need two separate data series for each of Actual and Total Percentage. To do so:-

  • Right click on chart and choose the option Select Data.
  • The Select Data Source Dialog box opens. Select the Series1 and Click on edit button.
editing the data series in a chart in excel
  • Enter “Expense” in the Series Name and Select the cell D7 as Series Value.
editing the data series in a chart in excel step 2
  • Hit OK button in the Edit Series Dialog box.
  • Before the Edit Series button, there is an Add button. Click on it and add a new series for Total Percentage.
  • Enter the Series Name as Total and select the cell E7 as the series value.

This updates the data series in the chart.

correcting the data series in a thermometer chart result
infographics Thermometer chart in Excel

Changing the Chart Type

The second step is to change the chart type to a Combo chart. To do so:-

  • Right Click on the chart and click on Change Chart Type option.
  • Select the Combo Chart and Choose Clustered Column for both Data Series.
  • Mark the Secondary Axis for “Expense”
changing the chart type in excel

Fixing the Maximum and Minimum Bounds of Vertical Axes

By now the Maximum and Minimum Value of Axis Labels is based on the Source Data of the Chart. It is automatic and updates with changes in source data. We need to fix the Axis to lie in between 0% to 100%. In order to do that:-

  • Select the Primary Vertical Axis on the Chart.
  • Press Ctrl+1
  • This opens the Format Axis Pane. Navigate to Axis Options
  • Set the Minimum and Maximum bound to 0 and 1.
changing minimum and maximum axis value in chart

Similarly, fix the bounds for the Secondary Vertical Axis as well. This is how the chart looks.

results

Adding Thermometer to the Chart

We need to insert a Rectangle Shape and a Circle from the Shapes button in the Insert tab on Ribbon. To make a thermometer, adjust the position of both shapes like this:-

shape of thermometer

Select both the oval and rectangle ( hold the shift key while selecting ). Go to the Shape Format tab. In the Align group, click on Group. This will group both the shapes as one.

You can change the fill color from the Shape Format tab and even remove the outline in the Shape Styles Group. Once the shape is formatted, select as a whole like this:-

step 2

You will not be able to format the shape once it is inserted into the chart.

Press ctrl+c to copy this shape. Now Click on the Blue Column on the Chart and press ctrl+v. This will replace the bar with the thermometer on the chart like this:-

step 3

Make another thermometer shape with no fill color. Apply the outline of the same color as that of the thermometer we inserted above. The shape should look like this once grouped as one.

Copy and paste this shape to the orange bar as we did for the blue bar. Consequently, the chart looks like this now:-

Correcting the Stretched Thermometers

You can see that both the thermometers have stretched them as per the magnitude of percentage they are representing. To avoid this to happen, Double click on the thermometer on the chart and mark this checkbox from the Format Data Series pane.

avoid stretching of data series in chart in excel

Moreover, you can remove the Gridlines and Vertical Axes from the Chart by simply selecting and pressing the delete key.

Adding Data Labels to the Chart

Select the chart. From the + button at the top right corner of the chart, mark the data labels and then go to more options as shown below.

In the Format Data Labels pane, navigate to label options and mark the checkbox for Value From Cells. Select cell D7 and then unmark the check box for Value.

formatting data labels

As a result, we have got the final chart.

result for thermometer chart in excel

This brings us to end. Thank you for reading.

Leave a Comment