Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

10000+ Copies Already Downloaded

Thermometer Chart in Excel

It happens sometimes when we need to track a single point target vs. its achievement. And for this, it’s important to have a specific chart which can help you to present your data in an understandable way.

I believe that thermometer chart is one of the best charts for single point target. It’s easy to create (simple steps) and easy to understand for a user.

A thermometer chart looks like a thermometer where entire chart presents the target and filled part is the achievement. The filled part goes up as the achievement increase.

In Excel, there is no default option to create a thermometer chart but you can create it by using simple steps.

So today, in this post, we will learn how to create a thermometer chart in Excel. And, apart for this, we will also learn to create a dynamic thermometer chart.

So let’s get started.

Steps To Create a Thermometer Chart in Excel

Please follow these simple steps to create a thermometer chart.

  • Enter below data in your worksheet.
data to create your first thermometer chart in excel
  • Now, select the entire data table and insert a column chart. Go to -> Insert Tab -> Charts -> 2D-Column Chart.
insert column chart to create thermometer chart in excel
  • Right click on your chart and click “Select Data”.
right click on chart to create thermometer chart in excel
  • In your select data window, click on “Switch Row/Column” and click OK.
switch row column to create thermometer chart in excel
  • Now, right click on achievement data bar & open “Format Data Series”.
open format data series to create thermometer chart in excel
apply secondary axis to create thermometer chart in excel
  • Now, we have two data bars with the different axis, next we need to make their axis values same.
need to change axis value to create thermometer chart in excel
  • Right, click on your first axis & select “Format Axis”. Add maximum value for the axis “100” or as per the target you have. Do the same with your secondary axis.
change chart axis values to create thermometer chart in excel
  • And, our excel thermometer chart is almost ready. We just need little formatting touch.
    1. For target bar, use no fill for color & a solid line color border.
    2. Use the same color for achievement bar (Fill & Border) which you have used for target bar border.
    3. Delete chart title, Horizontal Axis, and right vertical axis.
    4. One final thing, select left axis -> go to formatting pane -> Axis Options -> Tick Marks -> add outside tick marks for major and minor.
final step to create thermometer chart in excel

Hurray! We did it. Our thermometer chart is ready to rock.

You can also add a shape below your chart to make it looks like a real thermometer. (I don’t like to add but, you can). Download this file to check my ready to use excel thermometer chart.

Using Different Colors in a Thermometer Chart

When I was writing this post, one of my friends who was sitting beside me, asked.

“Hey, why don’t you use different colors for different levels in this thermometer chart.”

So, I did.

This one is a little bit tricky but worth to learn & you’ll enjoy your final chart. I have used different colors for different levels. It goes like this:

It goes like this:

  • Up to 40 – Red color bar.
  • Less than 70 & more than 40 – Yellow color bar.
  • More than 70 – Green color bar.
learn how to create a thermometer chart in excel

So, let’s get started.

  • Enter following data in your worksheet.
blank data table to create thermometer chart in excel different colors
  • Enter target value (I am using 100 here). And, in achievement cell insert your actual achievement (You can also automate this if you need).
final steps to create a thermometer chart in excel different colors
  • Now, we have to insert three simple formulas in rest of the three cell.
    1. =IF(F4>=70,F4,””) for excellent.
    2. =IF(AND(F4>=40,F4<70),F4,””) for good.
    3. =IF(F4<40,F4,””) for bad.
  • These all formulas will show achievement value if it is falling in their range (Here I have achievement value in cell F4).
  • Select target cell, excellent, good & bad cell. Like this & insert a column chart.
  • Now, you have a chart like this. And, in this chart, we have four bars but two out them have no value right now.
insert column chart to create thermometer chart in excel different colors
  • Do the basics first, change “Switch Row/Column” (You know that how to).
  • Next, you have to change three bars (Excellent, Good, & Bad) into secondary axis.
  • This is little tricky because you can’t select bars easily which have zero value. But I have a good solution for that.
  • Select your chart – > Right click -> Format chart area.
    • Click on the chart options drop down & select Series “Excellent”.
select data bar to create thermometer chart in excel different colors
    • Change the chart plot to secondary. (Do this for all three bars).
    • Make series overlap 100%. (Do this for all four bars).
    • Gap width 0%. (Do this for all four bars).
change to secondary axis to create thermometer chart in excel different colors
  • Now next is basics.
    • Match axis unit values for both of the axes.
    • Delete chart title.
    • Delete secondary vertical axis and horizontal axis.
    • Add tick marks.

Bang! here is your thermometer chart.

final steps to create a thermometer chart in excel different colors

Download this file check how I have created my thermometer chart in excel.

Conclusion

Creating a basic thermometer chart in excel is easy & simple. And, if you want to create this color changing thermometer chart you need some extra steps. Hope you don’t mind for that. 🙂

Few days back, I found some people saying that it’s better to use thermometer chart than using a gauge chart.

I really want to you play with this thermometer chart. And, I hope you’ll find it perfect for of your dashboards. I have use it in this daily sales template.

Now tell me one thing. Have you ever tried to create a thermometer chart in Excel? What do you think about its application in your dashboards? Please share with me in the comment section, I would love to hear from you.

And, please don't forget to share this tips with your friends.