Watch Video – Creating 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 few scenarios when where it can be used is when analyzing sales performance of regions or sales rep, or employee satisfaction ratings vs the target value.
In this tutorial, I will show you the exact steps you need to follow to create a thermometer chart in Excel.
Creating Thermometer Chart in Excel
Suppose you have the data as shown below for which you want to create a chart to show the actual value as well as where it stands as compared with the target value.
In the above data, the Achieved% is calculated using the Total and Target values (Total/Target). Note that the Target percentage would always be 100%.
Here are the steps to create a thermometer chart in Excel:
- Select the data points.
- Click the Insert tab.
- In the Charts group, click on the ‘Insert Column or Bar chart’ icon.
- In the drop-down, click the ‘2D Clustered Column’ chart. This would insert a Cluster chart with 2 bars (as shown below).
- With the chart selected, click the Design tab.
- Click on Switch Row/Column option. This will give you the resulting chart as shown below.
- Right-click on the second column (orange column in this example) and select format data series.
- In the Format Data Series task pane (or dialog box if you are using Excel 2010 or 2007), select Secondary Axis in Series Options. This will make both the bars align with each other.
- Note that there are two vertical axes (left and right), with different values. Right-click on the vertical axis on the left and select format axis.
- In the Format Axis task pane, change the maximum bound value to 1 and minimum bound value to 0. Note that even if the value is already 0 and 1, you should still manually change this (so that you see the Reset button on the right).
- Delete the axis on the right (select it and hit the delete key).
- Right-click on the column visible in the chart, and select format data series.
- In the format data series, make the following
- Fill: No Fill
- Border: Solid Line (choose the same color as that of actual value bar)
- Delete the chart title, grid lines, the vertical axis on the right, and horizontal (category) axis, and the legend. Also, resize the chart to make it look like a thermometer.
- Select the vertical axis on the left, right-click on it and select ‘Format Axis’.
- In Format Axis task pane, select Major Tick Mark Type as Inside.
- Select the chart outline, right-click and select Format Chart Area.
- In the task pane, make the following selection
- Fill: No Fill
- Border: No Line
- Now click the Insert tab and insert a circle from the Shapes drop-down. Give it the same color as thermometer chart and align it to the bottom.
That’s it! Your Thermometer chart is ready to measure.
You May Also Like the Following Excel Tutorials:
- Creating a Pareto Chart in Excel.
- Gantt Chart in Excel.
- How to Make a Bell Curve in Excel (Step-by-step Guide).
- Step Chart in Excel – A Step by Step Tutorial.
- How to Make a Histogram in Excel (Step-by-Step Guide).
- Creating a Heatmap in Excel.
- 10 Advanced Excel Charts that You Can Use In Your Day-to-day Work.