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 regions or sales rep.
Here is a quick way to create Thermometer Chart in Excel
- Have the data in adjacent cells (Actual Value and Target Value)
- Select the data points, go to Insert –> Charts –> 2D Clustered Column Chart. This would insert a Cluster chart with 2 bars.
- Go to Design –> Data –> Switch Row/Column
- Right-click on the second bar (orange 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 over each other.
- Note that there are 2 vertical axis, 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 100. Repeat the same process for the vertical axis on the right. When you make the upper bound of both the axis as 100, you would only see the target value bar (as the actual value bar is completely hidden by it).
- Right-click on the bar visible in the chart, and select format data series. In the format data series, make the following
- Fill: No Fill
- Border: Solid Line (better to have it of the same color as that of actual value bar)
- Delete the chart title, grid lines, vertical axis on the right, and horizontal (category) axis, and the legend.
- Select the vertical axis on the left, right click and select Format Axis. In Format Axis task pane, select Major Tick Mark Type as Inside. (Also resize the chart to make it look like a thermometer)
- Select the chart outline, right click and select Format Chart Area. In the task pane, make the following selection
- Fill: No Fill
- Border: No Border
- Now go Insert –> Shapes, and insert a circle. Give it the same color as thermometer chart and align it to the bottom.
- Your Thermometer chart is ready to measure!
- Show Target Vs Actual in Bar Chart and Line Markers.
- Using a Dynamic Target Line to show Target Vs. Actual Values.
- Dynamic Pareto Chart in Excel.
- Gantt Chart in Excel.