How to Make Thermometer Graphic for Excel (with Detailed Steps)

Thermometer graphic in Excel gives the perception of targeted percentage vs. obtained percentage. We can use this graphic chart to show the progress of a process, a person’s performance and so on. In this article, I will show you how to create thermometer graphic for Excel with detailed steps and clear illustrations.

Download Practice Workbook

Please download the following practice workbook in which I have added the data that we have used to write this article. You will also get a ready-made thermometer graphic here.

Steps to Create Thermometer Graphic Chart in Excel

1^st Step: Set Data for Thermometer Graphic Chart

To create a thermometer chart, we need to set the data properly.

In the following image, we see a sales summary of two shops, X and Y. Their target is 100% and they have reached 95.14% and 51.42% so far respectively. Now, we will create a chart that will look like a thermometer and show their progress visually.

Read More: How to Create Progress Thermometer in Excel (with Easy Steps)

2^nd Step: Plot a Graph with the Collected Data (Chart Type- Clustered Column)

The next step is, creating a clustered column graph from the source data. To do that,

• Select data in B21:D23.
• Go to the Insert tab and click on Insert Column or Bar Chart icon.

• After that, click on More Column Charts… option from the bottom.

• Then the Insert Chart window will appear.
• Go to the All Charts section and then select Column.
• After that, select the 2nd option of Clustered Column, and press OK.

As a result, the following graph will appear in the spreadsheet.

3^rd Step: Format the Axes of the Graph If Needed

Now, format the axes if you think you need to. Here, we have done this in the following way.

• Select the Y axis and right-click on your mouse.
• After that, select the Format Axis option.

• The Format Axis window appears.
• Now, go to Axis Options and change the maximum bound to 1.0 from 1.2, and press Enter.
• This will set the Axis units from 0 to 100%.

• After that, make all the options in the Chart Elements unmarked except the Data Labels and the Legends.
• You can apply the following look to Data Labels and Legends for a better presentation.

4^th Step: Open Microsoft PowerPoint and Create a Thermometer Graphic

This step requires opening a PowerPoint slide. We will create a Thermometer graphic in the PowerPoint slide and copy it to Excel then. To do this,

• Go to the Windows search bar and type PowerPoint and click on the PowerPoint icon.

• Open a Blank Presentation from Home.

• After that, select the default title boxes and delete them all.

• Now, go to the Insert tab, and from the Shapes drop-down, select a Rectangle shape.

• Draw a Rectangle and similarly, a Circle in the same way.
• Use the Oval shape to draw a circle.
• Press and hold the Shift key while drawing a circle with an Oval shape. It will help you to create a perfect circle.
• Now, drag the circle and place it on the Rectangle.
• But they are not in the same line, and they don’t look like a thermometer.
• So, select both of them using the Ctrl key and go to the Shape Format tab.
• Then from the Align drop-down, select Align Center.

• Now, they are aligned perfectly.
• Now, again select both the shapes with the Ctrl key and go to the Merge Shapes option of the Shape Format tab.
• Then select Union.

• As a result, the shapes will be unified.
• Now, select the single thermometer shape and press Ctrl+C to copy this shape.

Read More: How to Create Goal Thermometer in Excel (3 Effective Ways)

5^th Step: Copy the Thermometer Graphic to Excel and Give It a Proper Format

• Now, paste the shape to your Excel sheet using Ctrl+V.

• Do some optional formatting to the shapes.
• Create another copy of this shape and apply no fill to the copied shape.

• Add some more shapes with suitable fill formats.

6^th Step: Replace the Clustered Columns with Formatted Thermometer Graphics

• Now, select the first blue column twice (as a result only the first column will be selected).

• Now, copy the blue thermometer shape and paste it into the first blue column by using simple Ctrl+C and Ctrl+V presses.

• Similarly, paste the white thermometer with the blue outline in the second column’s place in the graph.

• Repeat these steps and you will have a new graph that looks like the following.

7^th Step: Stack and Scale the Thermometer Columns with 1

• Now, select the first thermometer column and double-click on it.
• The Format Data Point window will appear.
• From the Fill & Line section, select Stack and Scale with radio button and input 1 in the Units/Picture box just below the button.
• If your maximum target is 90%, then input 0.9, and so on.

• Repeat these for the rest of the thermometer columns of this graph and you will see the following results.

8^th Step: Overlap the Series to 100% and Adjust Gap Width

• Now, double-click on the first column of the thermometer chart, and again the Format Data Point window will appear.
• Go to the Series Options section and make the Series Overlap value 100% and adjust the Gap Width as per your wish.

• After applying a 150% gap width, the graph has the following look.

Last Step: Do Some Additional Formatting to the Thermometer Graphic Chart

• Technically, we are done, but we can do some additional formatting to this.
• Select the 100% labels and delete them.
• Select other % labels and make them align in the Center position.

• You can make the axes visible again if you feel like they should be visible.

Conclusion

So, these are the steps to create a thermometer graphic in Excel. If you have any questions regarding these, leave us a comment. Please visit our blog to explore more such Excel-related articles. Thank you!

Related Articles

• How to Create Fundraising Thermometer in Excel (with Easy Steps)
• Create Debt Thermometer in Excel (with Easy Steps)