HOW TO CREAT A THERMOMETER CHART

THERMOMETER CHART

Sometimes you need to track a goal and its achievement. For this, it is important to have the right chart. This chart must be able to present your data in a understandable way. And it should be easy to create. I believe the thermometer chart is one of the best charts for a point target. It’s simple to create and easy for a user to understand.

1 What is a thermometer chart?

The thermometer chart resembles a thermometer. The filled area increases as success increases. Excel doesn’t have a default option to create a thermometer chart, but you can create one by following simple steps.

For this, in this article, I have shared simple steps to create a thermometer chart in Excel.

2 Steps to Create a Thermometer Chart

To create a thermometer chart in Excel, you need to follow the steps below :

1. First, make sure you have data in the format below , where the target is 100 (as a percentage) and the achievement is 68% of the target.

1. After that, select the entire data and go to Insert tab ➜ Charts ➜ Column Chart or 2D Column Chart and insert this chart.

1. From there, right-click on your chart and click « Select Data » .

1. Now in the data selection window, click on « Switch or Swap Row/Column » and click OK.

1. Then 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 to a secondary axis chart .

1. At this point, you have two data bars (overlapping) with different axis. But you need to make their axis values the same .

1. Now from here right click on the first axis and select « Format Axis » and add the maximum value « 100 » or whatever maximum target values you have. Do the same with the secondary axis.

1. At this point your thermometer chart is almost ready, the only thing you need to do is a little formatting for the finishing touches.
2. For the target bar, use no fill for the color and a solid color border.
3. Use the same color for the achievement bar ( fill and border) that you used for the target bar border.
4. Delete the chart title, horizontal axis, and right vertical axis.
5. Select the left axis, right-click and select axis format, in axis options click on graduation, on primary and secondary type click outside.

1. Finally, make sure you set « interval width  » for both data bars (target and achievement) to « 0% », you have the bar width based on the chart width.

Your thermometer chart is ready to rock. You can also add a shape below your chart to make it look like a real thermometer.

3 Dynamic thermometer graph with different colors

I used different colors for different levels.

So, let’s begin

• 1. Enter the following data into your spreadsheet.

• 1. Enter the target value (I’m using 100 here) and in the achievement cell, insert the actual achievement value.
  2. After that, we need to insert three simple formulas into the rest of the three cells (these formulas will display the realization value if it falls within their range).
  3. From Excellent: =IF(F4>= 70;F 4; » »)
  4. For good: =IF(AND(F4>=40 ;F4 <70);F4; » »)
  5. For Bad: = IF( F4<40 ; F4 ; «  »)
  6. Now select the target cells, excellent, good and bad and insert a histogram.
  7. At this point we have a chart like below with four bars but two of them have no value.
     
  8. After that, change « swap or Swap row/column ».
  9. Now we need to change three bars (Excellent, Good, and Bad) in the secondary axis. This is a bit tricky because you can’t easily select the bars that have a zero value, but I have a good solution for this.
  10. Select your chart ➜ Right-click ➜ Format Chart Area.
  11. Click the chart options drop-down menu and select « Excellent » Series.

1. Final touch:

• Match the axis unit values for both axes.
• Remove the chart title.
• Delete the secondary vertical axis and the horizontal axis.
• Add graduations.

Here is your thermometer chart.

Creating a basic thermometer chart in Excel is simple. And, if you want to create this color-changing thermometer chart, you need a few extra steps, I hope you don’t mind.