I 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.
So let’s kick it off.
Steps To Create a Thermometer Chart in Excel
- Enter below data in your worksheet.
- Now, select this entire data table and insert a column chart.
- Go to -> Insert Tab -> Charts -> 2D-Column Chart.
- Right click on your chart & click “Select Data”.
- In your select data window, click on “Switch Row/Column” and click OK.
- Now, right click on achievement data bar & open “Format Data Series”.
- Now, we have two data bars with the different axis, next we have to make their axis values same.
- 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.
- And, our excel thermometer chart is almost ready. We just need little formatting touch.
- For target bar, use no fill for color & a solid line color border.
- Use the same color for achievement bar (Fill & Border) which you have used for target bar border.
- Delete chart title, Horizontal Axis, & right vertical axis.
- One final thing, select left axis -> go to formatting pane -> Axis Options -> Tick Marks -> add outside tick marks for major and minor.
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:
- 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.
- Enter target value ( I am using 100 here). And, in achievement cell insert your actual achievement (You can also automate this if you need).
- 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.
- 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”.
- 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).
- 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.
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.