Create thermometer chart in Excel
Thermometer chart is one of the charts that commonly used in your daily work, it is used to make a comparison between the actual value and a given target value to show the progress if they have achieved the target. For example, it can be used to a few scenarios, such as: analyzing sales performance, analyzing employee satisfaction rating, comparing budget and expenditure and so on.
- Create a simple thermometer chart in Excel
- Create a color changing thermometer chart in Excel
- Create a simple thermometer chart in Excel with a handy feature
- Download Thermometer Chart sample file
- Video: Create thermometer chart in Excel
Supposing, you have a range of data as below screenshot shown, and you want to create a thermometer chart to show the comparison of the actual and target values.
Firstly, prepare the data, please calculate the total value of the order, and then get the percentage of the actual value, and the target percentage should always be 100%. See screenshot:
After preparing the data, please do with the following steps to create a thermometer chart:
1. Select the data range which contains the actual and target percentage values, and then click Insert > Insert column or bar chart > Clustered Column, see screenshot:
2. And a column chart has been inserted into the sheet, then, click to select this chart, and choose Switch Row / column option under the Design tab, see screenshot:
3. And you will get a new chart as below screenshot shown:
4. Then, right click the second column which is the target series, and choose Format Data Series option from the context menu, see screenshot:
5. In the Format Data Series pane, select Secondary Axis under the Series Options icon, and you will see both the bars are aligned each other, see screenshot:
6. There are two vertical axes in the chart with different values, so, right click the left vertical axis, and choose Format Axis option, see screenshot:
7. In the Format Axis pane, under the Axis Options icon, change the Maximum bound value to 1 and Minimum value to 0, see screenshot:
8. Repeat this step to change the bound values to 0 and 1 for the secondary axis, now, you can see both the primary axis and secondary axis have been set to 0% - 100%. See screenshot:
9. Then, go on right clicking the visible column series, choose Format Data Series option, see screenshot:
10. In the Format Data Series pane, under the Fill & Line icon, select No fill from the Fill section, select Solid line from the Border section, and then choose blue color that is as same as the column, see screenshot:
11. In this step, you should hide other unneeded elements, such as chart title, gridlines. Click the Chart Elements icon to expand the box, and then uncheck the following operations:
- Axis > Primary Horizontal, Secondary Horizontal
- Chart Title
12. And then, right click the primary vertical axis, and select Format Axis, in the Format Axis pane, under the Axis Options icon, click Tick Marks option, and choose Inside from the Major type drop down list, see screenshot:
13. Then, you should remove the border from the chart, right click the chart area, and choose Format Chart Area option, see screenshot:
14. In the Format Chart Area pane, click the Fill & Line icon, and then select No fill and No line from the Fill and Border sections separately, see screenshot:
15. Then, drag and resize the chart to make it look like a thermometer, see screenshot:
16. At last, you can insert a circle at the bottom of the column, please click Insert > Shapes > Oval, and draw a circle, give it the same color as thermometer chart, put it at the bottom of the column series, and then format it’s outline as no outline, and you will get a thermometer chart as below screenshot shown:
Sometimes, you may would like the column color of the thermometer chart changes when the actual value changes. For example, when the actual value is greater than or equal to 80%, the column color is orange, when the actual value between 50% and 80%, the column color is green, and if the actual value is less than or equal to 50%, a blue column is displayed as below demo shown.
1. Firstly, you should enter some virtual data which are within the specified data range you need into the cells as below screenshot shown:
2. Select the range of A4:B7, and then, click Insert > Insert column or bar chart > Clustered Column, and a column chart is inserted into the sheet, see screenshot:
3. Then, click the chart to select it, and then click Switch Row / Column option under the Design tab, and you will get a new chart as following screens shown:
4. Now, you can give a specific color for each of the column series to your need. For example, I will color the excellent series as orange, good series as green and bad series as blue, the target series should be formatted as no fill and format a border color.
- Right click the excellent series, and then click Shape Fill from the Format tab, and then choose the color you like, repeat the same process to fill color for each data series.
- For the last target series, select No Fill from the Shape Fill drop down list, and then choose a border color from the Shape Outline drop down.
5. Then, click any one column series in the chart, and right click, then choose Format Data Series option from the context menu, see screenshot:
6. In the Format Data Series pane, under the Series Options icon, change the Series Overlap to 100%, now, all the series will be covered each other, see screenshot:
7. And then, right click the primary vertical axis, and select Format Axis, in the Format Axis pane, under the Axis Options icon, change the Maximum bound value to 1 and Minimum value to 0, see screenshot:
8. Then, you should hide other unneeded elements and change the tick marks of the primary vertical axis into inside as the step 10-11 of the first method. Finally, resize the chart to make it look like a thermometer, see screenshot:
9. After creating the thermometer chart, in this step, you should insert some formulas in cell B4, B5, B6 separately to replace the original virtual data to make the chart color change dynamically:
10. From now on, when you change the actual value in cell A2, the thermometer chart will be changed dynamically as below demo shown:
Kutools for Excel provides various special types of charts that Excel does not have, such as Bullet Chart, Target and Actual Chart, Slop Chart and so on. With its easy tool- Thermometer Chart, you can create a thermometer chart based on the cell values or manually typed values, and the Y axis labels can be displyaed as percentages or normal numbers as you need. Click to download Kutools for Excel for free trial!