If you are looking for some special tricks to know how to create a progress thermometer in Excel, you’ve come to the right place. There is one way to create a progress thermometer. This article will discuss every step of this method to do this Excel. Let’s follow the complete guide to learn all of this.
How to Create Progress Thermometer in Excel (With Easy Steps)
In the section that follows, we’ll demonstrate a useful but challenging technique for building an Excel progress thermometer. We must first create a dataset in Excel, then a stacked column chart, and finally modify and re-modify the data series and chart area in order to create a progress thermometer. In-depth information on this technique is provided in this section. To enhance your ability to think critically and your familiarity with Excel, you should learn and use these. Although we use the Microsoft Office 365 version here, you are free to use any other version of your choice.
Step 1: Prepare Dataset
We must first organize the dataset before moving on to creating the progress thermometer chart. Information is provided regarding the price of overall product development as well as the revenue realized from sales. To determine how little or no impact a change in the dataset value would have on the overall target, we will now calculate the profit before creating the thermometer chart. To prepare the dataset, we’ll use the SUM function.
- First, select the cell where you want to calculate the Total Profit. Here, we choose cell F14.
- Then, write the following formula in the selected cell.
=SUM(F5:F13)
- Next, press Enter.
- Therefore, you will get the following total profit value.
- After this, select cell C17 and enter the following formula:
=F14/F15
- We can calculate our progress in terms of percentage profit-making by entering this formula.
- Finally, the dataset that we required to include the thermometer in Excel is available.
Step 2: Create Excel Column Chart to Show Progress
In order to make a thermometer chart, we now have all the information we need. Starting with a straightforward clustered chart, we can further alter the chart.
- Firstly, select the range of cells B17:C18.
- Go to the Insert tab, and then click on the Insert Column or Bar Chart.
- Then from the drop-down menu, click on the Clustered Column from the 2D Chart option.
- Therefore, you will get the following chart.
Step 3: Modify Data Series and Chart Axis
We will modify the data series of the newly created clustered chart to create a thermometer chart in Excel.
- First of all, in the new chart, select any of the data columns, and then from the Chart Design tab, click on the Switch Row/Column.
- Then, the axis row and column will be shuffled.
- You will notice that one of the column’s colors is changed to orange.
- Next, select any of the columns and right-click on them.
- Afterward, from the context menu, click on the Format Data Series.
- Then from the right-side panel, click on the Secondary Axis on the Series Options.
- The chart will look like the below image, as the columns are basically stacked now.
- We can now change the axis settings to make the thermometer Chart data series easier to read and understand after making the necessary modifications to the data series.
- In order to delete it, first select the right axis and then press Delete.
- Then, choose the left axis by right-clicking on it.
- From the context menu, click on the Format Axis.
- Next, in the right-side panel, input the Minimum bound as 0.0 and press Enter.
- And then type 1.0 in the Maximum bound field and hit Enter.
- This will fix the axis’s visible limit; however, if the chart is changed, the axis value will now start at 0.0 and end at 1.0.
- Next, click the tick and choose Inside from the Major type.
- The tick will then show up inside the chart as a result.
- The modified chart will look like the below image.
Step 4: Remodify Data Series
After changing the chart axis, we returned to the data series to make further changes. This change will directly assist us in making an Excel thermometer chart.
- Again, select any of the columns and right-click on them.
- Then, from the context menu, click on the Format Data Series.
- In the right-side panel menu, go to Fill and Line.
- Then in the Fill option, select No Fill.
- And in the Border option, click on the Solid line.
- Make sure the color of the border matches the column color.
- Set the Width to 1.25 pt from 0.75 pt.
- Eventually, the shape of the thermometer on our chart will be apparent.
- Depending on the input data, the shape will alter dynamically.
Step 5: Format Chart Area
The chart’s final form has already been created. We will now make changes to the chart area to make it clutter- and distraction-free.
- Now select the gridlines of the chart and then right-click on it.
- From the context menu, click Delete.
- Now click anywhere in the chart except the plot area and right-click on the mouse.
- From the context menu, click on the Format Chart Area.
- Then on the right-side panel, click on the No Fill in Fill option and No Line in the Border option.
- Our chart now become transparent almost.
- Now, click on the right plus sign on the chart. And go to the axes option.
- And uncheck the Primary Horizontal checkbox.
- Then, remove the tick from the Chart Title checkbox.
- With all those steps, we managed to modify the chart area and make the chart transparent.
Step 6: Add Thermometer Bulb
The procedure is completed by placing a bulb-shaped object directly beneath the thermometer. This will complete the process of making an Excel thermometer chart.
- After we have the thermometer-shaped chart, we can now add the bulb shape below it.
- To add this shape, go to the Insert tab.
- And from there, click on the Shapes, and from there, click on the oval shape icon.
- Then we place the oval at the bottom of the chart.
- The final form of the thermometer will look like the below image.
- The thermometer chart level will change if the dataset’s value changes in any way.
- For example, there is a change of value in the revenue in cell F9.
- The revenue dropped from $3525 to just $1000, causing the drop level shown below in the chart.
- Because of this, it can be seen that the chart is actually a dynamic thermometer chart.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.
Conclusion
That’s the end of today’s session. I strongly believe that from now you may be able to create a progress thermometer chart in Excel. If you have any queries or recommendations, please share them in the comments section below.
