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

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
  • Gridlines

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:


Create a color changing thermometer chart in Excel

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:

doc thermometer chart 21 1

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:

B4: =IF(A2>=80%,A2,"")
B5: =IF(AND(A2>50%,A2<80%),A2,"")
B6: =IF(A2<=50%,A2,"")

10. From now on, when you change the actual value in cell A2, the thermometer chart will be changed dynamically as below demo shown:


Create a simple thermometer chart in Excel with a handy feature

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!


Download Thermometer Chart sample file


Video: Create thermometer chart in Excel


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.