Thermometer Chart in Excel

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

1. Create a drop-down list in excel of employee names.
   
   

   

2. Create a table like the below one.
   
   

   

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.
   
   

   

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

   

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

   

6. Select Achieved % and Target % cells.
   
   

   

7. For selected data, let’s insert a column chart in excelInsert A Column Chart In ExcelColumn chart is used to represent data in vertical columns. The height of the column represents the value for the specific data series in a chart, the column chart represents the comparison in the form of column from left to right.read more. Go to the INSERT tab, and insert 2 D Column Chart.
   
   

   

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

   

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

   

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

    

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

    

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

    

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

    

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

    

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

    

17. Now you can see the border.
    
    

    

18. Make the chart width as short as possible.
    
    

    

19. Remove excel gridlinesExcel GridlinesGridlines are little lines made of dots to divide cells from each other in a worksheet. The gridlines have slight faint invisibility; you can find it in the page layout tab. This option has a checkbox; for activating the gridlines, you can tick on it and untick if you wish to deactivate gridlines.read more from the chart.
    
    

    

20. Now again, select the vertical axis bar and make the major tick marks as “Inside.”
    
    

    

21. Remove the horizontal axis label as well.
    
    

    

22. Select the chart and make Outline as “No Outline.”
    
    

    

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

24. Go to the Insert tab from shapes chooses the “Oval” shaped circle.
    
    

    

25. Draw the Oval shape below the chart.
    
    

    

26. For the newly inserted Oval shape, fill color as same as the chart bar and make an outline as no outline.
    
    

    

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

28. Select both chart and shape, group it.