How to Create a 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 good example could be while analyzing sales performance of regions or sales rep.

Here is a quick way to create Thermometer Chart in Excel
  • Have the data in adjacent cells (Actual Value and Target Value)
    Thermometer Chart in Excel - Actula and Target Value
  • Select the data points, go to Insert –> Charts –> 2D Clustered Column Chart. This would insert a Cluster chart with 2 bars.Thermometer Chart in Excel - 2D Cluster Chart
  • Go to Design –> Data –> Switch Row/Column
    Thermometer Chart in Excel - Rows Columns Switched
  • Right-click on the second bar (orange in this example) and select format data series.
    Thermometer Chart in Excel - Secondary Axis
  • 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 over each other.
    Thermometer Chart in Excel - Secondary Axis
  • Note that there are 2 vertical axis, with different values. Right-click on the vertical axis on the left and select format axis. In the Format Axis task pane, change the maximum bound value to 100. Repeat the same process for the vertical axis on the right. When you make the upper bound of both the axis as 100, you would only see the target value bar (as the actual value bar is completely hidden by it).
    Thermometer Chart in Excel - Different Axes Value
  • Right-click on the bar visible in the chart, and select format data series. In the format data series, make the following
    • Fill: No Fill
    • Border: Solid Line (better to have it of the same color as that of actual value bar)
      Thermometer Chart in Excel - No fill in Target Value Bar
  • Delete the chart title, grid lines, vertical axis on the right, and horizontal (category) axis, and the legend.
  • Select the vertical axis on the left, right click and select Format Axis. In Format Axis task pane, select Major Tick Mark Type as Inside. (Also resize the chart to make it look like a thermometer)
    Thermometer Chart in Excel - Resized with border
  • Select the chart outline, right click and select Format Chart Area. In the task pane, make the following selection
    • Fill: No Fill
    • Border: No BorderThermometer Chart in Excel - Resized
  • Now go Insert –> Shapes, and insert a circle. Give it the same color as thermometer chart and align it to the bottom.
    Thermometer Chart in Excel - Final
  • Your Thermometer chart is ready to measure!

Download Thermometer Chart Example File
Download File Pic

Related Tutorials:
  • Space Crime

    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.

    • 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

        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.

  • JaxtasticLife

    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.