Thermometer Chart in Excel

Thermometer chart in Excel could be a good way to represent data when you have the actual value and the target value. A good example could be while analyzing sales performance of a product on a region.

Data:

Data used for depicting Thermometer chart in Excel is shown below

DayUnit Sales
Day 1120
Day 280
Day 390
Day 476
Day 593
Day 6104
Day 780
Day 879
Day 992
Day 1056

Let Assume the Total Target of 1000 units and out of that 870(by summing up all the unit sales) is achieved

Keeping this assumption in mind, let’s see how to create a thermometer chart in excel

Step 1:  Calculate the Percentage of Target achieved as shown below

thermometer chart in excel 1

  • Target 1000 is an assumption
  • Achieved is calculated by summing up all the unit sales (=SUM(B2:B11))
  • % Achieved is (Achieved/Target)*100

Note : Make sure an Empty Row above % Achieved as shown above

Step 2:  Create a column bar chart

  • Select cell B17 (make sure B16 row is empty)
  • On the Insert tab, in the Charts group, choose the Column button-> choose Clustered Column as shown below

thermometer chart in excel 2

  • Remove X axis
  • Remove Chart title

So the resultant chart will be

thermometer chart in excel 3

Step 3 : Right click on the bar -> Format data series ->

  • Set Series Overlap to 0
  • Set Gap Width 0
  • Narrow down the chart

thermometer chart in excel 4

Step 4 : right click on the vertical Axis -> Format Axis, set the Minimum to 0
and the Maximum to 1 as shown below

thermometer chart in excel 5

So now the resultant chart will be like

thermometer chart in excel 6

Step 5: Add the Botton Chord/oval to the chart

Click insert tab ->shapes -> Basic Shapes Chose Chord as shown below

thermometer chart in excel 7

And make it fit in the bar chart so the final chart will be like

thermometer chart in excel 8

Finally, we successfully created thermometer chart in excel

previous small thermometer chart in excel                                                                                                                next small thermometer chart in excel