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
For this, in this post, I've share simple steps to create a thermometer chart in Excel.
Steps to Create a Thermometer Chart in Excel
Hurrah! You did it.
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). 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 and you’ll love your final chart. I have used different colors for different levels. It goes like this:
Up to 40
Less than 70 and more than 40
More than 70
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).
- 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.
- How to Add a Horizontal Line in a Chart in Excel
- How to Add a Vertical Line in a Chart in Excel
- How to Create a Bullet Chart in Excel
- How to Create a Dynamic Chart Range in Excel
- How to Create a Dynamic Chart Title in Excel
- How to Create Interactive Charts In Excel
- How to Create a Sales Funnel Chart in Excel
- How to Create a HEAT MAP in Excel
- How to Create a HISTOGRAM in Excel
- How to Create a Pictograph in Excel
- How to Create a Milestone Chart in Excel
- How to Insert a People Graph in Excel
- How to Create PIVOT CHART in Excel
- How to Create a Population Pyramid Chart in Excel
- How to Create a Step Chart in Excel
- How to Create a Tornado Chart in Excel
- How to Create Waffle Chart in Excel
About the Author
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 ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.