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.
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.
As a result, the following graph will appear in the spreadsheet.
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
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.
- 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.
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.
- 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.
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.
- 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.
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.
- 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.
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.
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!