How to Create a Thermometer Chart in Excel

Watch Video – Creating Thermometer Chart in Excel

Thermometer chart in Excel could be a good way to represent data when you have the actual value and the target value.

A few scenarios when where it can be used is when analyzing sales performance of regions or sales rep, or employee satisfaction ratings vs the target value.

Thermometer Chart in Excel - Final Product

In this tutorial, I will show you the exact steps you need to follow to create a thermometer chart in Excel.

Click here to download the example file and follow along.

Creating Thermometer Chart in Excel

Suppose you have the data as shown below for which you want to create a chart to show the actual value as well as where it stands as compared with the target value.

Dataset for Thermometer graph in Excel

In the above data, the Achieved% is calculated using the Total and Target values (Total/Target). Note that the Target percentage would always be 100%.

Here are the steps to create a thermometer chart in Excel:

  1. Select the data pointsSelect the data points to create a thermometer chart.
  2. Click the Insert tab.Thermometer Chart in Excel - Insert Tab
  3. In the Charts group, click on the ‘Insert Column or Bar chart’ icon.Thermometer Chart in Excel - Insert Column or Bar chart
  4. In the drop-down, click the ‘2D Clustered Column’ chart. This would insert a Cluster chart with 2 bars (as shown below).Thermometer Goal Chart in Excel - inserting a 2D column chart
  5. With the chart selected, click the Design tab.Thermometer Chart in Excel - Design Tab
  6. Click on Switch Row/Column option. This will give you the resulting chart as shown below.Thermometer Chart in Excel - Switch row column with chart
  7. Right-click on the second column (orange column in this example) and select format data series.Thermometer Chart in Excel - Format Data Series target column
  8. In the Format Data Series task pane (or dialog box if you are using Excel 2010 or 2007), select Secondary Axis in Series Options. This will make both the bars align with each other.Thermometer Chart in Excel - secondary Axis with chart
  9. Note that there are two vertical axes (left and right), with different values. Right-click on the vertical axis on the left and select format axis. Thermometer Chart in Excel - format axis left side one
  10. In the Format Axis task pane, change the maximum bound value to 1 and minimum bound value to 0. Note that even if the value is already 0 and 1, you should still manually change this (so that you see the Reset button on the right).Minimum and Maximum bound for left axis - Thermometer goal chart
  11. Delete the axis on the right (select it and hit the delete key).
  12. Right-click on the column visible in the chart, and select format data series.Thermometer Chart in Excel - Format Data Series Fill and Border settings
  13. In the format data series, make the following
    • Fill: No Fill
    • Border: Solid Line (choose the same color as that of actual value bar)Fill and Border color for the thermometer goal chart generator
  14. Delete the chart title, grid lines, the vertical axis on the right, and horizontal (category) axis, and the legend. Also, resize the chart to make it look like a thermometer.Thermometer Chart in Excel - ready to measure goals
  15. Select the vertical axis on the left, right-click on it and select ‘Format Axis’.Format Axis in left vertical axis tick marks
  16. In Format Axis task pane, select Major Tick Mark Type as Inside.Major tick mark type as inside
  17. Select the chart outline, right-click and select Format Chart Area.Format Chart Area for a thermometer goal graph in Excel
  18. In the task pane, make the following selection
    • Fill: No Fill
    • Border: No LineFormat Chart Area for a thermometer graph in Excel - fill and border
  19. Now click the Insert tab and insert a circle from the Shapes drop-down. Give it the same color as thermometer chart and align it to the bottom.Oval Shape for thermometer bottom

That’s it! Your Thermometer chart is ready to measure.

Download Thermometer Chart Example File
Download File Pic

Note: The thermometer chart is useful when you have one actual value and target value set. In case you have multiple such datasets, you either need to create multiple such thermometer charts, or need to use a different chart type (such as the Bullet chart or the Actual Vs. Target charts).

You May Also Like the Following Excel Tutorials:

  • MS says:

    Thank You.I created a thermometer chart by reading your tutorial and in our office they are still using this daily for tracking teams score.Thank You so much.

  • JaxtasticLife says:

    Thank you for this great tutorial! I was able to create the thermometer for tracking out team’s giving. Thank you for providing a great resource.

  • Space Crime says:

    Hi Sumit,

    I am attempting to modify this and changed the axis range to 7000. Every month, for the next 36 months I will be adding approx. 200. By the end of 36 months the thermometer should reach the target. I want be able to enter a value (that I am adding monthly) in a cell and also on another cell to show a cumulative running total of how much I’ve added. How do I do this?

    Thanks,
    Jay.

    • Sumit Bansal says:

      Hello.. In this case, make the target value (in B3) 7000 and for actual value (in A3) use the formula =SUM(A5:A41) – assuming that the you’ll be entering the monthly values in A5:A41. Now, the cumulative sum would be reflected in A3 and the thermometer chart would work.

      • Space Crime says:

        That was not the way I quite wanted, however I figured out a fancier way from searching the web for tips.

        The data entry part is in a separate tab sheet where I have a table with the date, amt. paid, and sum. (A1 to C1). In another cell, G2 I have an array formula =OFFSET(Data,ROWS(Data)-1,2), which gives the cummulative total from the table. I have a defined name data for the table to automatically expand as I enter data: =OFFSET(Data!$A$1:$C$1,0,0,COUNT(Data!$A:$A)+1) .

        The actual value in the chart sheet (A3) is a named referenced cell to the data sheet G2.

        Works like a charm!

        Jay.

  • >