How to Create Thermometer Chart in Excel? (Step by Step Guide)

Thermometer Chart for Excel

Excel Thermometer Chart is the visualization effect used to show the “achieved percentage vs targeted percentage”. This chart can be used to show employees performance, quarterly revenue target vs actual percentage, etc.… Using this chart we can create a beautiful dashboard as well.

How to Create a Thermometer Chart in Excel? (Step by Step)

Below are the steps to create a Thermometer chart in excel.

You can download this Thermometer Chart Excel Template here – Thermometer Chart Excel Template

Follow the below steps to create a thermometer chart in excel.

Step 1: Create a drop-down list of employee names.

Step 2: Create a table like the below one.

Step 3: Apply VLOOKUP for a drop-down cell to fetch the target and achieved values from the table as we select the names from the drop-down list.

Step 4: Now arrive Achieved % by dividing the Achieved numbers by Targeted numbers.

Step 5: Next to the Achieved column insert one more column (helper column) as Target and enter the target value as 100%.

Step 6: Select Achieved % and Target % cells.

Step 7: For selected data let’s insert column chart. Go to the INSERT tab, and insert 2 D Column Chart.

Step 8: Now we will have a chart like below one.

Step 9: Select the chart, go to the Design tab and click on the option “Switch Row/Column”.

Step 10: Select the larger bar and press Ctrl + 1 to open format data series option.

Step 11: The first formatting we need to do is to make the larger bar as “Secondary Axis”.

Step 12: Now in the chart we can see two vertical axis bars one is for target and another one is for achieved.

Step 13: Now we need to delete the Target Axis Bar.

Step 14: Now select the Achieved % axis bar and press Ctrl + 1 to open format data series window.

Click on “Axis Options” >>> Set the Minimum to 0, Maximum to 1 and Major to 0.1. Now our chart can hold a maximum of 100% value and a minimum of 0%, major interval points are 10% each.

Now our chart can hold a maximum of 100% value and a minimum of 0%, major interval points are 10% each.

Step 15: Now we can see the only red-colored bar, select the bar and make the FILL as No Fill.

Step 16: For the same bar make the border the same color as we can see above i.e. Blue.

Step 17: Make the chart width as short as possible.

Step 18: Remove Gridlines from the chart.

Step 19: Now again select the vertical axis bar and make the major tick marks as “Inside”.

Step 20: Remove the horizontal axis label as well.

Step 21: Select the chart and make Outline as “No Outline”.

Step 22: Now our chart looks like this now.

We are almost done, the only thing left is we need to add a base for this excel thermometer chart.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Step 23: Go to Insert tab from shapes chooses “Oval” shaped circle.

Step 24: Draw the Oval shape below the chart.

Step 25: For the newly inserted Oval shape fill color as same as the chart bar and make an outline as no outline.

Step 26: You need to adjust the oval shape to fit the chart and looks like a thermometer chart.

Move the shape change width, top size to fit the base of the chart.

Step 27: Select both chart and shape, group it.

We are done with charting and looks a beauty now.

From the drop-down list as you can change the numbers chart also will change respectively.

Insert Custom Heading For the Chart

One last thing we need to do is to insert custom heading for the chart. The custom heading should be based on the employee selection from the drop-down list.

For this create a formula like the below in any one of the cell.

Now insert a rectangular shape from insert tab.

Fill the color with no color and change the border to black color.

Keep selected the rectangular shape, click on formula bar and give a link to formula cell i.e. B8 cell.

Now we can see the heading text on top of the chart.

So, now heading is also dynamic and keeps updating the values as per the changes made in the drop-down list.

Things to Remember

Drawing the shape under the base of the chart is key
Adjust the oval-shaped circle to fit the chart and make it look like a thermometer chart in excel.
Once the oval-shaped circle is adjusted always group the shape and chart together.
Border of Target %, Bar of Achieved %, and the base of the thermometer chart should be the same color and should not contain any other colors.

Thermometer Chart for Excel

How to Create a Thermometer Chart in Excel? (Step by Step)

Insert Custom Heading For the Chart

Things to Remember

Recommended Articles