Creating a Thermometer Chart in Excel

​By Marlene Clapp, Senior Research Analyst, University of Massachusetts Dartmouth  

There may come a time when you want to track and share progress for a certain activity, such as student response to a survey. Thermometer charts are a useful graphic to display progress toward reaching a goal. This tip provides a quick look at a process for developing a basic thermometer chart in Microsoft Excel. 

The first step in the process is to enter the data for your participation goal and progress toward that goal. For instance, assume that for a particular survey the goal is to have 326 and 270 of the invited first-year students and seniors, respectively, respond. A second row can be updated at each phase of the survey administration (e.g., initial invitation, reminder, final reminder) for the number of total respondents who have completed the survey to date. A third row is then used to calculate the percentage completion towards goal (i.e., total to date divided by the goal).

Next, select only the four cells corresponding to the two student groups and their respective percentage completion towards goal.  

With the selected data, set up a basic column chart.
 
.   
 
The resulting chart will look something like the chart below. 
Next, set the vertical axis options so that the minimum is fixed at 0.0, the maximum is fixed at 1.0, and the major unit is fixed at 0.10 (or whatever you desire). Delete the legend or set it to show “none.”ext, set the vertical axis options so that the minimum is fixed at 0.0, the maximum is fixed at 1.0, and the major unit is fixed at 0.10 (or whatever you desire). Delete the legend or set it to show “none.” 

 

 
Set the primary horizontal gridlines to “none.” Set the primary horizontal axis to “none.” Add data labels and format the data label options to set their position to “inside end” and to show the category name as well as the value. For the separator, select “new line.” 

 

 
Select the bar for first years and set the fill to a desired color. Repeat the same procedure for the bar for seniors with another color. Create two oval shapes and set the fill for one oval to the first color and the fill for the other oval to the second color. Remove any undesired preset effects to the ovals like 3-D, glow, or shadow. Overlay the ovals onto the ends of the two bars. 

 

Under format chart area, set the border to “no line.” Make the size of your chart slightly taller and set the fill for the area of the spreadsheet around the chart to white. Adjust the formatting and size of your data labels, as desired, as shown below. Your thermometer chart is now complete! You can make further adjustments to add desired formats and then copy and share your chart on a webpage or in a report, for example.

 

 

 

 

 Comments

 
To add a comment, Sign In
Total Comments: 6
 
Steve posted on 7/17/2014 10:07 AM
Fun, useful, and should be easy to share with interested colleagues and administrators. Thank you!
JR posted on 7/17/2014 10:28 AM
Very simple and creative way to achieve this visual, use shapes all the time for other purposes, didn't think of this idea though! Very nice look too when finished! Could use the institutions' color(s) and or other images as well to make it more connected for the user. Thanks for the information and step by step example!
Terry posted on 7/17/2014 11:08 AM
I like it. This is the kind of chart that attracts attention. I can think of a myriad of possible uses for this.
Lisa posted on 7/17/2014 11:24 AM
I'm the "survey person" at my university and I have also kept Excel spreadsheet tracking survey responses, reminder dates, etc. This is a fun tip to help create an easy to read graphic presentation of the survey progress. Very useful tip, indeed! Thank you for sharing.
Mary Jo posted on 7/17/2014 1:49 PM
Easy tip to follow which adds additional interest into the presentation of data. Thanks for providing this great idea!
Lee posted on 7/18/2014 11:41 AM
Thanks Marlene! Interesting and helpful tip which I'll add to my toolbox of techniques for the visual display of data.