How to Create a Thermometer Chart in Excel

What is a Thermometer Chart?

The Thermometer chart looks like a thermometer. The filled part goes up as the achievement increases. 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 shared simple steps to create a thermometer chart in Excel.

Steps to Create a Thermometer Chart in Excel​

​To make a thermometer chart in Excel, you need to follow the below steps and make sure to download this sample file to follow along:

1. First, make sure you have data in the below format, where the target is 100 (in percentage) and achievement is 68 percent against the target.
   
2. After that, select the entire data and go to Insert Tab ➜ Charts ➜ Insert Column or Bar Chart ➜ 2-D Clustered Column and insert this chart.
   
3. From here, right-click on your chart and click “Select Data”.
   
4. Now, from the select data window, click on “Switch Row/Column” and click OK.
   
5. Next, right-click on the achievement data bar and open “Format Data Series”.
   
6. And now, go to “Series Options” and select “Secondary Axis” to convert the achievement bar into a secondary axis chart.
   
7. At this point, you have two data bars (overlapping each other) with a different axis. But you need to make their axis values the same.
   
8. Now from here, right-click on the first axis and select “Format Axis” and add maximum value “100” or the max target value you have, and set the minimum to “0”. Do the same with the secondary axis as well, so both bars start and end at the same points.
   
9. At this point, your thermometer chart is almost ready; the only thing you need to do is a bit of formatting for the final touch.
   1. For the target bar, use no fill for color and a solid line color border.
   2. Use the same color for the achievement bar (fill and border) which you have used for the target bar border.
   3. Delete the chart title, the horizontal axis, and the right vertical axis.
   4. Select the left axis ➜ formatting pane ➜ Axis Options ➜ Tick Marks ➜ Outside tick marks for major and minor.
   5. In the end, make sure to set “Gap Width” for both of the data bars (Target and Achievement) to “0%”, so the bar’s width matches the chart’s width.
      

Hurrah! You did it.

Your thermometer chart is ready to rock. You can also add a shape below your chart to make it look like a real thermometer (I don’t like to add one, 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?”

This one is a little bit tricky, but it’s worth learning, and you’ll love your final chart. I have used different colors for different levels.

So let’s get started…

1. Enter the following data in your worksheet.
   
2. Enter target value (I’m using 100 here) and in the achievement cell insert the actual achievement value.
3. After that, we have to insert three simple formulas in the rest of the three cells (each formula shows the achievement value if it falls in its range). The formulas are also given below these steps.
   • For Excellent: =IF(F4>=70,F4,””)
   • For Good: =IF(AND(F4>=40,F4<70),F4,””)
   • For Bad: =IF(F4<40,F4,””)
4. Now select the target, excellent, good, and bad cells and insert a column chart.
5. At this point, we have a chart like below with four bars, but two of them have no value.
   
6. After that, right-click the chart ➜ Select Data and click “Switch Row/Column”.
7. Now we need to change three bars (Excellent, Good, and Bad) into the secondary axis. This is a little tricky because you can’t easily select bars that have no 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. In Series Options, set Plot Series On to Secondary Axis (do this for the Excellent, Good, and Bad bars).
   4. Make Series Overlap 100% (do this for all four bars).
      Set Gap Width to 0% (do this for all four bars).
   
   
8. Final touch:
   1. Match the minimum and maximum values of both axes.
   2. Delete the chart title.
   3. Delete the secondary vertical axis and the horizontal axis.
   4. Add tick marks.

Here are the three formulas from step 3. Use them in the Excellent, Good, and Bad cells (F4 is the achievement cell). Only one of them shows a value at a time, so only one colored bar shows in the chart:

=IF(F4>=70,F4,"")
=IF(AND(F4>=40,F4<70),F4,"")
=IF(F4<40,F4,"")

Then give each of the three bars its own fill color: green for Excellent, yellow for Good, and red for Bad. As the formulas use the same ranges as the table above, an achievement of exactly 40 shows in yellow and exactly 70 in green.

Bang! Here is your thermometer chart.

Download this sample file from here to learn more about this.

When the Achievement Goes Above the Target

As you have fixed the maximum of both axes to the target, an achievement above the target (like 120 against 100) fills the whole thermometer and the extra part is cut off. If you want the chart to stop at the target, but the label to show the real value, use a helper cell for the chart bar with the following formula, and keep the real value in the data label or a text box:

=MIN(F4,100)

And if you want to show the extra part, set the maximum of both axes a bit above the target, like 120, instead.

Show the Achievement Value on the Chart

A thermometer chart is easier to read with the value on it. Click the achievement bar, then click the Chart Elements button (+) next to the chart and tick Data Labels, or right-click the bar ➜ Add Data Labels. Use Inside End as the label position so the label sits at the top of the filled part.

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

A few days back, I found some people saying that it’s better to use a thermometer chart than using a SPEEDOMETER/GAUGE. I have used a thermometer chart in my one-page daily sales report template.

Now tell me one thing. 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.

Frequently Asked Questions