In this article, you will learn how to create a chart that looks like a thermometer. For this type of graph, you will want to present data that shows the percentage of a value (e.g., the annual plan for the sale or use of the budget).
A thermometer chart, also known as a "gauge chart," is a type of chart in Microsoft Excel that represents a single value within a range of values, using a thermometer-style visual. The chart can be used to visually track a goal, such as a sales target, and compare it to actual results.
Data preparation
To create a thermometer goal chart in Excel, you need data. One cell should show the current value, and the second should be the target value. On this basis, calculate the percentage.
Select the cell with the percentage and go to the ribbon.
Creating thermometer chart
In the Insert tab, Charts group, click Column, and select the Clustered Column chart.
Your chart at the moment looks like this:
The thermometer Chart layout
You must now adjust the formatting.
Right-click on the left axis (e.g. on one of the numbers) and choose Format Axis from the menu.
In the Axis Options, next to Maximum beside Fixed type 1.
Next, right-click the blue bar and choose Format Data Series.
Set the Gap Width to 0 %.
Right-click a blank area over a blue bar between the horizontal lines and select Format Plot Area.
On the left edge of the page, select Border Color and choose Solid Line. Below, select the blue Color.
Your chart now looks like this:
Select the entire chart (click anywhere on the chart). Go to the Ribbon > Layout tab. Click on Legend / None.
Axes > Primary Horizontal Axis > None
Gridlines > Primary Horizontal Gridlines > None
The thermometer Chart is just about to be ready.
It needs to be much more, such as a thermometer. Change the size of the chart. Narrow it and increase the height to be more like a mercury thermometer.
The effect
The effect of the thermometer is even better when you insert the oval on the bottom of the goal chart.
Go to Ribbon> Insert> Illustrations > Shapes. Choose the Oval and adjust it to the chart.
Here is the effect of your work. The thermometer Goal Chart is ready!
Your thermometer chart is now complete. You can use this chart to visually track the progress towards a goal and compare it to the actual result. You can also change the goal and actual result values in the worksheet, and the chart will update automatically.