Charts and Dashboards: Thermometer Chart Part 1
22 October 2021
Welcome back to our Charts and Dashboards blog series. This week, I start to create a Thermometer chart.
The results have come in for last year’s sales for three of my imaginary salespeople.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/cd/078/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I would like to be able to see at a glance how well they are doing against their targets. There are several ways I could do this, but I have chosen to create a Thermometer chart for each salesperson.
I start by adding two new rows. I will add a row for the percentage of the Target achieved and one which simply holds the maximum achievable, which is always 100%. For my scenario, I know that no-one has exceeded their target.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/cd/078/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I will start by creating a chart for Mary. The data I am using for my chart is in $A$18:$B$19. I select this data and go to the Insert tab, where I find the ‘Insert Column or Bar Chart’ dropdown and choose a ‘Clustered Column’ chart.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/cd/078/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
This creates a simple Clustered Column chart with only two columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/cd/078/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I start by removing the Chart Title by selecting and deleting it. Next, I am going to swap the columns and the rows. To make the effects of this step clearer, I will first select my chart and right-click to access the ‘Select Data’ option.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/cd/078/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
The resulting dialog has a button to ‘Switch Row/Column’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/cd/078/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
When I do this, instead of having one Series and two Axis Labels, I have two Series, which each have one column which I will be combining into one column for both Series.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/cd/078/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I can then edit the % Target and % Max Data Series separately:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/cd/078/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I right click on the second column (which is the maximum percentage), and select ‘Format Data Series’ to access the ‘Format Data Series’ pane. I can then select ‘Secondary Axis’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/cd/078/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
This overlays the columns, and gives me two axes. I right-click on the right-hand axis and select ‘Format Axis’ to access the pane:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/cd/078/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I change the Minimum Bound to zero [0] and the Maximum Bound to one [1]. Note that even if the values are already set to this, they should still be entered as I am removing the ‘Auto’ value:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/cd/078/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I no longer need the right-hand axis, so I select it and delete it. Next, I right-click on the column and select ‘Format Data Series’ to access the pane.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/cd/078/image12.png/a1537847463e660a31158c8032525438.jpg)
I change Fill to ‘No fill’ and Border to ‘Solid line’ which I make blue. This will reveal the column underneath:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/cd/078/image13.png/917da985be13220165c8d2823e95344f.jpg)
I can now delete the Chart Elements I don’t want: the gridlines and the legend. I also right-click on the left-hand axis and change the range shown.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/cd/078/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
I resize the chart to make it look more like a thermometer.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/cd/078/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
The chart doesn’t quite look like a thermometer, but it is taking shape, which is a clue to how I will enhance it next time!
That’s it for this week. Come back next week for more Charts and Dashboards tips.