WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Thermometer Chart in Excel

Thermometer Chart in Excel

How to Create a Thermometer Chart in Excel?

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

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 in excel of employee names.

Thermometer chart Example 1-1

  • Step 2: Create a table like the below one.

Thermometer chart Example 1-2

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

Thermometer chart Example 1-3

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

Thermometer chart Example 1-4

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

  • Step 6: Select Achieved % and Target % cells.

Thermometer chart Example 1-6

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

Thermometer chart Example 1-7

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

Thermometer chart Example 1-8

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

Thermometer chart Example 1-9

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

Thermometer chart Example 1-10

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

Thermometer chart Example 1-11

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

Thermometer chart Example 1-12

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

Thermometer chart Example 1-13

Thermometer chart Example 1-14

  • Step 14: Now 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. Now 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

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.

Thermometer chart Example 1-16

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

Thermometer chart Example 1-17

Thermometer chart Example 1-19

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

Thermometer chart Example 1-20

  • Step 18: Remove excel gridlines from the chart.

Thermometer chart Example 1-21

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

Thermometer chart Example 1-22

  • Step 20: Remove the horizontal axis label as well.

Thermometer chart Example 1-23

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

Thermometer chart Example 1-24

  • Step 22: Now, our chart looks like this now.

Outline Example 1-25

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

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

shapes Example 1-26

  • Step 24: Draw the Oval shape below the chart.

draw oval Example 1-27

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

shape format Example 1-28

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

Popular Course in this category
Sale
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

oval shape Example 1-29

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

group Example 1-30

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

meter Example 1-31

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 a 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 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

Keep selected the rectangular shape, click on the formula bar, and give a link to formula ,cell, i.e., 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 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.
  • 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.

Recommended Articles

This has been a guide to Thermometer Chart in Excel. Here we will learn how to create a dashboard using a thermometer chart in excel along with examples and with a downloadable template. You may learn more about excel from the following articles –

  • Bullet Chart in Excel
  • Create Fishbone Diagram
  • Grouped Bar Chart in Excel
  • Dot Plots Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Thermometer Chart Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More