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:
Online Excel Tips Tutorials Training and Videos

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)