Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

10000+ Copies Already Downloaded

How To Create a Thermometer Chart In Excel

how-to-create-a-thermometer-chart-in-excel-step-by-step-guideI love to use thermometer chart in excel.

A thermometer chart in excel is one of the best ways to track single point target vs. achievement.

And, it’s super easy to create.

Here, I have a step by step guide to help you to create your first thermometer chart in excel.

After that, I’ll show how to create thermometer chart which can change it’s color according to it’s values.

steps-to-create-a-thermometer-chart-in-excel

So let’s kick it off.

Steps To Create a Thermometer Chart in Excel

  • Enter below data in your worksheet.

data to create your first thermometer chart in excel

  • Now, select this entire data table and insert a column chart.
    • Go to -> Insert Tab -> Charts -> 2D-Column Chart.

insert column chart to create thermometer chart in excel

  • Right click on your chart & click “Select Data”.

right click on chart to create thermometer chart in excel

  • In your select data window, click on “Switch Row/Column” and click OK.

switch row column to create thermometer chart in excel

  • Now, right click on achievement data bar & open “Format Data Series”.

open format data series to create thermometer chart in excel

apply secondary axis to create thermometer chart in excel

  • Now, we have two data bars with the different axis, next we have to make their axis values same.

need to change axis value to create thermometer chart in excel

  • Right, click on your first axis & select “Format Axis”.
    • Add maximum value for the axis “100” or as per the target you have. Do the same with your secondary axis.

change chart axis values to create thermometer chart in excel

  • And, our excel thermometer chart is almost ready. We just need little formatting touch.
    1. For target bar, use no fill for color & a solid line color border.
    2. Use the same color for achievement bar (Fill & Border) which you have used for target bar border.
    3. Delete chart title, Horizontal Axis, & right vertical axis.
    4. One final thing, 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

Hurray! 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 to check my ready to use excel thermometer chart.

Using Different Colors in a Thermometer Chart

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:

learn how to create a thermometer chart in excel

  • Up to 40 – Red color bar.
  • Less than 70 & more than 40 – Yellow color bar.
  • More than 70 – Green color bar.

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 am using 100 here). And, in achievement cell insert your actual achievement (You can also automate this if you need).

enter target achievement values to create thermometer chart in excel different color

  • Now, we have to insert three simple formulas in rest of the three cell.
    • =IF(F4>=70,F4,””) for excellent
    • =IF(AND(F4>=40,F4<70),F4,””) for good.
    • =IF(F4<40,F4,””) for bad.
  • These all formulas will show achievement value if it is falling in their range (Here I have achievement value in cell F4).
  • Select target cell, excellent, good & bad cell. Like this & insert a column chart.
  • Now, you have a chart like this. And, in this chart, we have four bars but two out them have no value right now.

insert column chart to create thermometer chart in excel different colors

  • Do the basics first, change “Switch Row/Column” (You know that how to).
  • Next, you have to change three bars (Excellent, Good, & 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 & select Series “Excellent”.
      select data bar to create thermometer chart in excel different colors
    • Change the chart plot to secondary. (Do this for all three bars).
    • Make series overlap 100%. (Do this for all four bars).
    • Gap width 0%. (Do this for all four bars).

change to secondary axis to create thermometer chart in excel different colors

  • Now next is your basics.
    • 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.

final steps to create a thermometer chart in excel different colors

Download this file check how I have created my thermometer chart in excel.

Some Final Words

Using a thermometer chart in excel is really good.

I found some people saying that it’s better to use thermometer chart than using a gauge chart.

Creating a basic thermometer chart in excel is really easy & simple.

And, if you want to create this color changing thermometer chart you just need some extra steps.

Hope you don’t mind for that. 🙂

Over To You

I really want to you play with this thermometer chart. I hope you’ll find it perfect for some of your dashboards.

Don’t forget to give me your views in the comment section.