How to Create a Thermometer Chart in Excel

It happens sometimes that when you need to track a goal with its achievement.

For this, it’s important to have the right chart.

That chart should be able to present your data in an understandable way

excel thermometer chart looks like a thermometer

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.

For this, in this post, I've share simple steps to create a thermometer chart in Excel.

So let’s get started…

Steps to Create a Thermometer Chart in Excel​

Hurrah! You did it.

final step to create thermometer chart in excel

Your 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).

Dynamic Thermometer Chart with Different Colors

​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 and you’ll love your final chart. I have used different colors for different levels.

It goes like this:

learn how to create a thermometer chart in excel

Value

Color

Up to 40

Red

Less than 70 and more than 40

Yellow

More than 70

Green

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’m using 100 here) and in the achievement cell insert the actual achievement value.
  • After that, we have to insert three simple formulas in rest of the three cells.
  1. From Excellent: =IF(F4>=70,F4,””)
  2. For Good: =IF(AND(F4>=40,F4<70),F4,””)
  3. For Bad: =IF(F4<40,F4,””)

Note: These formulas will show achievement value if it is falling in their range.

  • Now select the target, excellent, good, and bad cells and insert a column chart.
  • At this point, we have a chart like below with four bars but two out them have no value.
insert column chart to create thermometer chart in excel different colors
  • After that, change “Switch Row/Column”.
  • Now we need to change three bars (Excellent, Good, and 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.
  1. Select your chart ➜ Right click ➜ Format chart area.
  2. Click on the chart options drop down and select Series “Excellent”.
  3. Change the chart plot to secondary (Do this for excellent, good, and bad bars) .
  4. Make series overlap 100% (Do this for all four bars).
  5. Gap width 0% (Do this for all four bars).
insert column chart to create thermometer chart in excel different colors

Final touch:

  1. Match axis unit values for both of the axes.
  2. Delete chart title.
  3. Delete secondary vertical axis and horizontal axis.
  4. Add tick marks.

Bang! here is your thermometer chart.

final steps to create a thermometer chart in excel different colors

Conclusion

Must Read Excel Tutorials

  • Bullet Chart in Excel: It's just like a thermometer chart with a bullet like line as a target line and different...
  • Waffle Chart in Excel: It's a set of hundred small square boxes to present one box as 1% of achievement...
  • Pictograph in Excel: It's a normal chart where you can use an image or icon to present data line or column...
  • Population Pyramid in Excel: A specific chart to present population in different age groups using a comparison with previous period...
  • Pivot Chart in Excel: It's a graphical representation of a pivot table. You can use all kind of charts as a pivot chart but... 

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

2 thoughts

Leave a Comment

Your email address will not be published.

  1. I love the thermometer chart. The only thing that’s giving me trouble is that the left horizontal axis on the chart changes from a normal 0 – 100 to showing 84 – 100 when I enter a value of 90 – 99 in the achievement box so it appears that the bar drops. Are there any solutions for this?

  2. how do I get the colors to change on the bar? I have all the information in but the color is one solid color and I can’t figure out how you made >70 green etc