In this tutorial, I will teach you how to make a Thermometer goal chart in Excel 2013. A thermometer goal chart is a chart used to find out how much the difference remain from the actual value to the target value.
For example if we want to measure how much sales target is achieved and how far we have to go to achieve the target.
In order to create the thermometer chart we need to have data of actual value and target value adjacent to each other.
Select the cells and then go the Insert tab and under charts section select 2D clustered column chart.
This will insert charts with two bars having actual and target sales.
Now we have to consolidate both the bars to make it one.
In order to do that select the chart and then go the Design tab under chart tools and click on “Switch Row/Column”
Right click the bigger bar and select “Format Data Series”.
Under the Format data series options select “Secondary Axis”.
Bye doing this, there will be two vertical axis displayed with different kind of values.
In order to do that, right click the left axis and click on “Format Axis”.
In the task pane, change the maximum bound value to 100.
Do the same for the axis on the right side.
When you make both axis values to 100,000 as the targeted value, you will only see the target value bar and actual value bar will be hidden.
Now you will right click on the visible chart and select format data series.
Under the fill option, select “No Fill” and make the border as “Solid Line”
Now it is turning to shape. In order to make it look like the thermometer, we will start to make changes to the chart.
Delete the chart title, right axis and bottom axis.
To give tick markers to the amount and the graph, right click the vertical axis and select Format Axis.
In the format axis pane, select major tick mark type as “Inside”.
To the formatting overall on the chart, select the outline of the chart and right click it and from the menu click on “Format Chart Area”.
In the task pane of chart area, set the fill to “No Fill” and bolder to “No line”.
Now we are all set to change the shape to the actual thermometer.
Go the insert tab and under shapes, insert a circle and draw it on the bottom of the bar.
Give it the same color to look like a thermometer.
You can make the changes to the amount and the thermometer will fill/empty according to the distance to target accordingly.