How to create Thermometer Chart in Excel

Thermometer is used to show the percentage of achievement. It is based on a single cell of achievement and it is best for use in a dashboard to display the progress of everyone that whether they achieved a goal or not.

To prepare the Thermometer chart we have to use the Clustered Column Chart.

Let’s take an example and understand how we can prepare Thermometer Chart.

We are having a table in the range C8:D28, cell D30 contains the target value, cell D31 contains total score and cell D33 contains the percentage of target achieved. To create the chart we have to assure that the blank row preceding cell D33, if we fail to include this blank row, Excel will use the entire data block not just the single cell to construct the chart since D33 is out-of-the-way from the other data the chart wizard uses only a single cell.

Follow below mentioned steps to create a thermometer chart;-

Select the cell D33, go the “Insert Tab”.
Click on Clustered Column chart in the section of Charts.

Further customized the chart, click the legend on the right side and press delete.

Change the width of the chart, right click on the bar, select format data series and to make the column occupy the entire width of the plot area.
Double click the column to display the Format Data Point dialog box, in the series option tab, change the gap width to 0% and separated overlap 0%.

Right click the percentage on the chart, click format Axis, and fix the minimum to 0 and the maximum of 1, Click close.

Provide the thermometer shape

Decrease the plot area of chart width wise and increase length wise.

In the insert tab, in the illustration group, select shapes, on the shapes list, in the basic shapes group choose oval shape.

Format the shape according to chart shape and insert with the graph.

To change the pattern of chart, click on the patterns tab and select the gradient fill effect and choose the color as per desire.
To change the “Vertical Axis Major Gridlines” right click on the Gridlines, select the option of “Format Gridlines”. The dialog box will appear on the window, select the dash type in the tab of fill and line.

Conclusion:- If you want to show the clear picture of achieved target then this chart will really help you.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Shannon J. Perkins
November 13, 2014 at 9:11 am

Oh great! Thanks for this amazing tutorial. I am learning some of the basics of using Microsoft Excel. I will definitely try these methods that you share.

Reply ↓