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 a 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…
Want to get better at Excel? Download this free E-book from here to learn some of the best tips and tricks.
Steps to Create a Thermometer Chart in Excel
Please follow these simple steps to create a thermometer chart.
- First of all, enter below data in your worksheet.
- After that, select entire data and go to Insert Tab ➜ Charts ➜ 2D-Column Chart and insert this chart.
- From here, right click on your chart and click “Select Data”.
- Now, from the select data window, click on “Switch Row/Column” and click OK.
- Next right click on achievement data bar and open “Format Data Series”.
- And now, go to “Series Options” and select “Secondary Axis” to convert achievement bar into a secondary axis chart.
- At this point, we have two data bars (overlapping each other) with the different axis. But we need to make their axis values same.
- Now from here, right-click on the first axis and select “Format Axis” and add maximum value “100” or the max target values we have. Do the same with the secondary axis as well.
- At this point, our thermometer chart is almost ready the only thing we need to do is a bit of formatting for the final touch.
Hurrah! 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 from here to check my ready to use excel thermometer chart.
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 & you’ll enjoy your final chart. I have used different colors for different levels.
It goes like this.
So, let’s get started...
- Enter following data in your worksheet.
- 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.
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.
- 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.
- Select your chart ➜ Right click ➜ Format chart area.
- Click on the chart options drop down and select Series “Excellent”.
- Change the chart plot to secondary (Do this for excellent, good, and bad bars) .
- Make series overlap 100% (Do this for all four bars).
- Gap width 0% (Do this for all four bars).
Final touch:
- 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 sample file from here to learn more about this.
Conclusion
Creating a basic thermometer chart in Excel is simple.
And, if you want to create this color changing thermometer chart you need some extra steps, I hope you don’t mind for that.
A few days back, I found some people saying that it’s better to use thermometer chart than using a gauge chart.
I have used a thermometer chart in my one page daily sales report 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 your views with me in the comment section, I’d love to hear from you. And, don’t forget to share this tip with your friends.
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 [...]
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