Thermometer Chart in Excel

Last Updated :

21 Aug, 2024

Blog Author :

N/A

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

How to Create a Thermometer Chart in Excel?

Excel thermometer chart is the visualization effect used to show the "achieved percentage vs. targeted percentage." We can use this chart to show employee performance, quarterly revenue target vs. actual percentage, etc. Using this chart, we can also create a beautiful dashboard.

Table of contents

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

  1. Create a dropdown list in Excel of employee names


    Thermometer chart Example 1-1

  2. Create a table like the below one.


    Thermometer chart Example 1-2

  3. Apply the VLOOKUP function for a dropdown cell to fetch the target and achieved values from the table as we select the names from the dropdown list.


    Thermometer chart Example 1-3

  4. Now, arrive at "Achieved %" by dividing the "Achieved" numbers by "Target" numbers.


    Thermometer chart Example 1-4

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


    Thermometer chart Example 1-5

  6. Select "Achieved %" and "Target %" cells.


    Thermometer chart Example 1-6

  7. For selected data, let us insert a column chart in Excel. Go to the "INSERT" tab, and insert a 2-D column chart.


    Thermometer chart Example 1-7

  8. Now, we will have a chart like the below one.


    Thermometer chart Example 1-8

  9. Select the chart, go to the "Design" tab, and click on the option "Switch Row/Column."


    Thermometer chart Example 1-9

  10. Select the larger bar and press "Ctrl + 1" to open the "Format Data Series" option.


    Thermometer chart Example 1-10

  11. The first formatting we need to make the larger bar "Secondary Axis."


    Thermometer chart Example 1-11

  12. Now, in the chart, we can see two vertical axis bars. One is for the target, and another one is for achievement.


    Thermometer chart Example 1-12

  13. We need to delete the "Target Axis Bar."


    Thermometer chart Example 1-13

  14. Select the "Achieved %" axis bar and press "Ctrl + 1" to open the "Format Data Series" window.

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

    Thermometer chart Example 1-15
    Our chart can hold a maximum of 100% value and a minimum of 0%. Major interval points are 10% each.

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


    Thermometer chart Example 1-16

  16. Make the border the same color as shown above for the same bar, which is blue.


    Thermometer chart Example 1-17

  17. Now, you can see the border.


    Thermometer chart Example 1-19

  18. Make the chart width as short as possible.


    Thermometer chart Example 1-20

  19. Remove Excel gridlines from the chart.


    Thermometer chart Example 1-21

  20. Again, select the vertical axis bar and make the "Major type" tick marks "Inside."


    Thermometer chart Example 1-22

  21. Remove the horizontal axis label as well.


    Thermometer chart Example 1-23

  22. Select the chart and outline "No Outline."


    Thermometer chart Example 1-24

  23. Now, our chart looks like this now.


    Thermometer chart Example 1-25

    We are almost done. The only thing left is we need to add a base for this Excel thermometer chart.

  24. Go to the "Insert" tab from "Shapes" and choose the "Oval" shaped circle.


    Thermometer chart Example 1-26

  25. Draw the Oval shape below the chart.


    Thermometer chart Example 1-27

  26. For the newly inserted oval shape, fill color like the chart bar with no outline.


    Thermometer chart Example 1-28

  27. You need to adjust the oval shape to fit the chart and make it look like a thermometer chart.

    Move the shape and change the width and top size to fit the chart's base.
    Thermometer chart Example 1-29

  28. Select both chart and shape, and group them.


    Thermometer chart Example 1-30

We are done with charting, and it looks like a beauty now.

meter Example 1-31

From the dropdown 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 a custom heading for the chart. The custom heading should be based on the employee selection from the dropdown list.

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

Target achieved Example 1-32

Now, insert a rectangular shape from the "Insert" tab.

Rectangular Example 1-33

Fill the color with "No color" and change the border to "black" color.

No color Example 1-34

Select the rectangular shape, click on the formula bar, and give a link to the formula, B8 cell.

formula Example 1-35

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

Thermometer chart Example 1-36

So, now the heading is also dynamic and keeps updating the values per the changes made in the dropdown list.

Things to Remember

  • Drawing the shape under the base of the chart is key.
  • We must adjust the oval-shaped circle to fit the chart and make it look like a thermometer chart.
  • Once the oval-shaped circle is adjusted, always group the shape and chart.
  • The border of "Target %," "Bar of Achieved %," and the thermometer chart's base should be the same color and not contain any other colors.

This article is a guide to the Thermometer Chart in Excel. Here, we will learn how to create a dashboard using a thermometer chart in Excel, examples, and a downloadable template. You may learn more about Excel from the following articles: -