Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

1st 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.

Set Data for Thermometer Graphic Chart

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


2nd 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.

Plot a Graph with the Collected Data (Chart Type- Clustered Column)

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

Plot a Graph with the Collected Data (Chart Type- Clustered Column)


3rd 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.

Data Labels- Outside End

Legend- Top


4th 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.

Open Microsoft PowerPoint and Create a Thermometer Graphic

  • 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.

Open Microsoft PowerPoint and Create a Thermometer Graphic

  • 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.

Open Microsoft PowerPoint and Create a Thermometer Graphic

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

Open Microsoft PowerPoint and Create a Thermometer Graphic

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


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

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

Copy the Thermometer Graphic to Excel and Give It a Proper Format

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

Copy the Thermometer Graphic to Excel and Give It a Proper Format

  • Add some more shapes with suitable fill formats.


6th 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.

Replace the Columns with Formatted Thermometer Graphics

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

Replace the Columns with Formatted Thermometer Graphic

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


7th 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.

Stack and Scale the Thermometer Columns with 1

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


8th 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.

Overlap the Series to 100% and Adjust Gap Width


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.

Doing Some Additional Formatting to the Thermometer Graphic Chart

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

Doing Some Additional Formatting to the Thermometer Graphic Chart


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

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo