How to Create a Thermometer Chart in Excel


- Written by Puneet

It happens sometimes, 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

And it should be easy to create. I believe that the thermometer chart is one of the best charts for single point target. It’s simple to create and easy to understand for a user.

What is a Thermometer Chart?

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

excel thermometer chart looks like a thermometer

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.
    data to create your first thermometer chart in excel
  2. After that, select the entire data and go to Insert Tab ➜ Charts ➜ 2D-Column Chart and insert this chart.
    insert column chart to create thermometer chart in excel
  3. From here, right-click on your chart and click “Select Data”.
    right click on chart to create thermometer chart in excel
  4. Now, from the select data window, click on “Switch Row/Column” and click OK.
    switch row column to create thermometer chart in excel
  5. Next right click on the achievement data bar and open “Format Data Series”.
    open format data series to create thermometer chart in excel
  6. And now, go to “Series Options” and select “Secondary Axis” to convert the achievement bar into a secondary axis chart.
    apply secondary axis to create thermometer chart in excel
  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.
    need to change axis value to create thermometer chart in excel
  8. Now from here, right-click on the first axis and select “Format Axis” and add maximum value “100” or the max target values you have. Do the same with the secondary axis as well.
    change chart axis values to create thermometer chart in excel
  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 chart title, horizontal axis, and 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 make “Gap Width” for both of the data bars (Target and Achievement) to “0%” you have the bar’s width according to the chart’s width.

Hurrah! You did it.

final step to create thermometer chart in excel

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

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.

learn how to create a thermometer chart in excel
Up to 40
> 70

So let’s get started…

  1. Enter following data in your worksheet.
    blank data table to create thermometer chart in excel different colors
  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 rest of the three cells (these formulas will show achievement value if it is falling in their range).
    • From 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 out them have no value.
    insert column chart to create thermometer chart in excel different colors
  6. After that, change “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 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).
      Gap width 0% (Do this for all four bars).

    insert column chart to create thermometer chart in excel different colors
  8. 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

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


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.

Last Updated: July 13, 2023

4 thoughts on “How to Create a Thermometer Chart in Excel”

  1. “This thermometer chart tutorial is fantastic. The step-by-step guide makes it easy to follow along.

  2. very good tutorials…however, how do i create a thermometer chart where actual fund collection exceeds the target. Help to do that will be valued.


  3. I love the thermometer chart. The only thing that’s giving me trouble is that the left horizontal axis on the chart changes from a normal 0 – 100 to showing 84 – 100 when I enter a value of 90 – 99 in the achievement box so it appears that the bar drops. Are there any solutions for this?

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


Leave a Comment