How to create thermometer goal chart in Excel?

A thermometer chart is a graphical representation of the precise value of a well-defined measurement. It is used to show the percentage of achievement. It is based on a single cell of accomplishment, and it is the easiest way used in a dashboard to show the progress of all, whether they have achieved their targets or not

In this tutorial, we will go step by step and show how you can create a thermometer goal chart in Excel.

Step 1

Suppose you have the data given below and want to make a chart to display the actual number as well as where it stands in comparison to the desired value. You may do this by using a line chart or a column chart.

When analysing the data shown above, the total percent is determined by dividing the Total value by the Target value (Total/Target). It is important to keep in mind that the Target percentage would always be one 100 %

Step 2

After making your selections for the Total and Target percentages, proceed to the insert tab in the menu on Excel. Then Click on the drop down arrow next to the Insert Column or Bar Chart option in the Graphs section of the Insert menu. Next, pick the clustered column chart from the list of 2-D column chart options.

Step 3

This is not something that any of us were anticipating at all. In order to transform this one into the Thermometer Chart, the layout will need some minor adjustments on our part.

Step 4

When you have the Chart chosen, go to the Chart's menu and pick the Design tab. Once there, go to the data group and select the Switch Row/Columns option.

Step 5

Now, choose the orange column from the list, and right click anywhere on it to access all of the choices that are included inside it. To format the data series, click the option to do so. Excel will respond by opening a new window on the far-right side of the screen in which we may format the data series.

Step 6

Click the radio button corresponding to the Secondary Axis option that can be found in the series section of the Format Data Series box that appears on the far right of your Excel window. This will make it possible to overlay the two series columns on top of one another, and it will hopefully turn into a stacked bar chart like the one seen in the picture below.

Step 7

Make the necessary adjustments in the following under the fill & line section of the format data series window.

Transforming the fill to a solid line and the border to a no fill (click on the radio button)

Step 8

Simply making a selection on the main axis and then using the right mouse button to click on it will allow you to format or edit the primary axis. The axis that can be found on the left side of the chart is considered to be the major axis. It is important that you do not choose the option that is located on the graph's right side.

Step 9

Change the minimum and maximum bound values in the axis settings section of the format axis panel that displays on the right to 0 and 1 accordingly for minimum and maximum bound values. It may seem like 0 and I by default, but you still need to alter these numbers to 0 and 1 manually. As soon as you change these values, you will notice two Reset buttons next to the lowest and maximum bound values. You may use these buttons to reset the values to their original state.

Step 10

Select the appropriate major type as inside from the list that appears when you click the Tick Marks option.

Step 11

Now, click on the secondary axis, which should be located in the righthand side of your chart. After that, use your keyboard to hit the delete button, and the secondary axis should be removed. The chart will appear as shown in the following.

Step 12

To change the appearance of the chart, select it first, then go to the Format tab on the Excel ribbon. You'll see the insert shape group is located inside of it. Simply add the oval to the bottom of this chart by clicking on the form that looks like an oval.

Step 13

Place this oval shape at the bottom-right corner of the chart that we already have.

Step 14

To choose the shape, right-click on it, then modify the colour of the shape so that it matches the colour of the chart, and then go to the Shape Outline section and select the option that says "No Outline."

Step 15

I would like to inform you that I have eliminated all of the gridlines that were located below this chart. You may eliminate the same by clicking on the horizontal lines and then clicking the Delete button on your keyboard. Or, right-click the horizontal line and select the Delete option.

Finally, this is how the thermometer goal chart would look like.

Conclusion

In this tutorial, we explained in detail how you can create a thermometer goal chart in Excel.