How to Create a Thermometer Chart in Excel

excel thermometer chart looks like a thermometer

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…

Quick Navigation

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.
data to create your first thermometer chart in excel
  • After that, select entire data and go to Insert Tab ➜ Charts ➜ 2D-Column Chart and insert this chart.
insert column chart to create thermometer chart in excel
  • From here, right click on your chart and click “Select Data”.
right click on chart to create thermometer chart in excel
  • Now, from the select data window, click on “Switch Row/Column” and click OK.
switch row column to create thermometer chart in excel
  • Next right click on achievement data bar and open “Format Data Series”.
open format data series to create thermometer chart in excel
apply secondary axis to create thermometer chart in excel
  • At this point, we have two data bars (overlapping each other) with the different axis. But we need to make their axis values same.
need to change axis value to create thermometer chart in excel
  • 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.
change chart axis values to create thermometer chart in excel
  • 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.
  • For target bar, use no fill for color and a solid line color border.
  • Use the same color for achievement bar (fill and border) which you have used for target bar border.
  • Delete chart title, horizontal axis, and right vertical axis.
  • Select left axis ➜ go to formatting pane ➜ Axis Options ➜ Tick Marks ➜ add outside tick marks for major and minor.
final step to create thermometer chart in excel

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.

  • Up to 40 – Red color bar.
  • Less than 70 and more than 40 – Yellow color bar.
  • More than 70 – Green color bar.
learn how to create a thermometer chart in excel

So, let’s get started...

  • Enter following data in your worksheet.
blank data table to create thermometer chart in excel different colors
  • 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.
  1. From Excellent: =IF(F4>=70,F4,””)
  2. For Good: =IF(AND(F4>=40,F4<70),F4,””)
  3. For Bad: =IF(F4<40,F4,””)

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.
insert column chart to create thermometer chart in excel different colors
  • 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.
  1. Select your chart ➜ Right click ➜ Format chart area.
  2. Click on the chart options drop down and select Series “Excellent”.
  3. Change the chart plot to secondary (Do this for excellent, good, and bad bars) .
  4. Make series overlap 100% (Do this for all four bars).
  5. Gap width 0% (Do this for all four bars).
insert column chart to create thermometer chart in excel different colors

Final touch:

  1. Match axis unit values for both of the axes.
  2. Delete chart title.
  3. Delete secondary vertical axis and horizontal axis.
  4. Add tick marks.

Bang! here is your thermometer chart.

final steps to create a thermometer chart in excel different colors

Conclusion