Rubayed Razib Suprov
Thermometer-type looking and functioning charts can bring your Excel Charts to life. This Chart is visually eye-pleasing and has better clarity. If you are curious to know how you can create a Thermometer Chart in Excel, then this article may come in handy for you. In this article, we discuss how you can create a Thermometer Chart in Excel with elaborate explanations.
Download Practice Workbook
Download this practice workbook below.
Step-by-Step Procedure to Create a Thermometer Chart in Excel
In this below, step-by-step process, you will be able to create a Thermometer Chart in Excel. Remember, do not alter the serial of the step or try any step before the previous one. Otherwise, the process will crumble and you may have to start again.
Step 1: Prepare Dataset
Before we delve into creating the Thermometer Chart, we need to organize the dataset first. The information about the cost of total product development and the revenue earned after selling the products are presented. We now will calculate the profit and then create the Thermometer Chart to understand how little or impact there would be if there is any change in the dataset value on the total target. We will use the SUM function in order to prepare the dataset.
- To calculate the profit, select cell F5 and enter the following formula:
=E5-D5
- Entering this formula will calculate the profit for the product in the range of cells B5:B13.
- Then drag the Fill Handle to cell F13, doing this will fill the range of cell F5:F13 with the profit of the products listed in the range of cells B5:B13.
- After this, select cell F14 and enter the following formula:
=SUM(F5:F13)
- We then put our target profit in cell F15.
- After this, select cell C17 and enter the following formula:
=F14/F15
Entering this formula will help us to determine how much progress we made in profit making in percentage.
- Finally, we have the dataset that we needed in order to add the Thermometer in Excel.
Step 2: Create and Merge Stacked Column Chart
We now have the necessary information in order to create a Thermometer Chart. We can start by creating a simple clustered Chart and then further modify the Chart.
- First, 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.
- We will have the Chart right next to the table.
Read More: How to Create Meter Chart in Excel (with Easy Steps)
Step 3: Modify Data Series
We will modify the data series of the newly created clustered Chart to create a Thermometer Chart in Excel.
- In the new Chart, select any of the data columns, and then from the Chart Design tab, click on the Switch Row/Column.
- The axis row and column will now be shuffled.
- You will notice that one of the column’s colors is changed to orange.
- Select any of the columns and right-click on it.
- Then 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.
Step 4: Modify Chart Axis
Right after we modify the Thermometer Chart data series, we can now alter the axis settings to make it more readable and easier to interpret.
- To begin with, select the right axis and then press Delete to delete it.
- right after that, select the left axis and right-click on it.
- From the context menu, click on the Format Axis.
- In the right-side panel, input the Minimum bound as 0.0 and press enter.
- And input 1.0 in the Maximum bound option and press enter.
- This will fix the visible limit of the axis. Now, however, the Chart changes, the Chart axis value will begin at 0.0 and end at 1.0.
- Now click on the tick and from the Major type, select Inside.
- This will make the tick appear on the inside part of the Chart.
- The modified Chart will look like the below image.
Step 5: Remodify Data Series
After the modification of the Chart axis, we again back to the data series to modify. This modification will directly help us to create a Thermometer Chart in Excel.
- Again, select any of the columns and right-click on it.
- 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.
- And set the Width to 1.25 pt from 0.75 pt.
- Our Chart will finally have the distinctive Thermometer shape.
- The shape will change dynamically according to the input data.
Step 6: Modify Chart Area
We already made the final form of the Chart. So now we will modify the Chart area to make the Chart area 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 made the Chart transparent.
Step 7: Add Thermometer Bulb
The final stage of this procedure is to add a bulb shape object beneath the Thermometer. This will finalize the process of creating a Thermometer Chart in Excel.
- 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.
- And then we place the oval at the bottom of the Chart.
- The final form of the Thermometer will look like the below image.
- If is there any change in value in the dataset, then the Thermometer Chart level will also change.
- For example, there is a change of value in the revenue in cell F9.
- The revenue fell from $3525 to just $1000, which resulted in the below drop level in the Chart.
- This demonstrates that the Chart is truly a dynamic Thermometer Chart.
Conclusion
To sum it up, the issue of how you can create a Thermometer Chart in Excel is answered here by step by step explanation. For this problem, a workbook is available for download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable
