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.

Please follow these simple steps to create a thermometer chart.

• Enter below data in your worksheet.

• Now, select the entire data table and insert a column chart. Go to -> Insert Tab -> Charts -> 2D-Column Chart.

• Right click on your chart and click “Select Data”.

• In your select data window, click on “Switch Row/Column” and click OK.

• Now, right click on achievement data bar & open “Format Data Series”.

• In your format pane, go to “Series Options” & select “Secondary Axis” to convert achievement bar into a secondary axis.

• Now, we have two data bars with the different axis, next we need to make their axis values same.

• 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.

• 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.

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.

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:

So, let’s get started.

• Enter following data in your worksheet.

• Enter target value (I am using 100 here). And, in achievement cell insert your actual achievement (You can also automate this if you need).

• 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.

• 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”.

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

• 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. Download this file from here check how I have created my thermometer chart in excel.

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.

Must Read Next

1. How Create a Waffle Chart in Excel
2. How To Create a Bullet Chart in Excel
3. Add a Horizontal Line in a Chart in Excel
4. Top 10 Advanced Excel Charts
5. Interactive Charts in Excel