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
| Day | Unit Sales |
|---|---|
| Day 1 | 120 |
| Day 2 | 80 |
| Day 3 | 90 |
| Day 4 | 76 |
| Day 5 | 93 |
| Day 6 | 104 |
| Day 7 | 80 |
| Day 8 | 79 |
| Day 9 | 92 |
| Day 10 | 56 |
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
- 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
- Remove X axis
- Remove Chart title
So the resultant chart will be
Step 3 : Right click on the bar -> Format data series ->
- Set Series Overlap to 0
- Set Gap Width 0
- Narrow down the chart
Step 4 : right click on the vertical Axis -> Format Axis, set the Minimum to 0
and the Maximum to 1 as shown below
So now the resultant chart will be like
Step 5: Add the Botton Chord/oval to the chart
Click insert tab ->shapes -> Basic Shapes Chose Chord as shown below
And make it fit in the bar chart so the final chart will be like
Finally, we successfully created thermometer chart in excel
