How to Create a Thermometer Chart in ExcelComprehensive Guide with Screenshots

Written By:
Lisa Borga

There are many ways to track data in Excel, from line graphs to pie charts, and one popular example is the thermometer chart.

Thermometer charts represent the status of a current value in relation to a target value.

This can be great for showing how much of a goal has been achieved, such as in fundraising or sales targets.

Fortunately, Excel makes it easy to make this type of chart, and here we will look at exactly how you can make one.

How To Create a Thermometer Chart in Excel

In order to understand how to create a temperature chart, consider this data set.

In this data set, sales totals for the period are being tracked against a targeted goal.

We have both the current sales total as well as the target listed.

In addition, we have the percentage of the targeted goal, which is the current total divided by the target formatted as a percent as well as the target percentage, which is 100%.

Each of these basic elements will allow us to create a thermometer chart in Excel. Simply follow these steps.

  1. Ensure that the Total, Goal, Achieved, and Target rows are present in your worksheet, and then select the two percentage values.

  1. Navigate to the “Insert” tab, and within the “Charts” group, select the “Insert Column or Bar Chart” option.

  1. Within the drop-down menu, select “2D Clustered Column,” which will enter a Cluster-type chart containing two bars.

  1. Ensure the chart is selected and navigate to the “Design” tab. Here within the “Data” group, select “Switch Row/Column.”

  1. This will alter the formatting of the chart like so.

  1. Now right-click on the second column, and on the drop-down list, select “Format Data Point….”

  1. In the “Format Data Series” pane under “Series Options,” click on “Secondary Axis.” This will result in the two columns visually overlapping.

  1. Though the chart now has an appearance resembling what we are looking for, there are still considerable improvements to be made. One of them is correcting the vertical axes on the left and right of the chart. Right-click the left axis, and on the drop-down list, select “Format Axis.”

  1. This will open the Format Axis pane, and here manually set the minimum bound to 1 and the maximum to 1. This should be done even if the values are already set this way.

  1. Next, select the right axis and press delete on your keyboard to remove it.
  2. Right-click the column on your chart, and on the drop-down list, click on “Format Data Series.”

  1. In the “Format Data Series” pane, set “Fill” to No Fill and “Border” to Solid line with a color matching that of the bar itself.

  1. Now remove the chart’s title, right vertical axis, horizontal axis, grid lines, and legend. You can also adjust the size of the chart to make it resemble a thermometer.

  1. Select the left axis, right-click, and once again, select “Format Axis” from the list.
  2. Within the “Format Axis” pane, navigate to the “Tick Marks” options, and within the “Major type option, select Inside.

  1. Click on the chart outline, right-click, and choose Format Chart Area. Within the pane, select the following options. For “Fill,” choose No Fill and for “Border,” choose No Line.

  1. For a last step to give the chart an appearance like that of a thermometer, navigate to the Insert tab and insert an oval from the Shapes menu. Align it with the bottom of the bar, remove the outline, and adjust its color to match.

Final Thoughts

Now your thermometer chart is complete. It should look just like a thermometer and is ready to measure your values.

You can adjust its color and shape further to make its appearance however you like to make it suit your taste and needs.