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