How to Create a Thermometer Chart in Excel (with Easy Steps)

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.

Prepare Dataset to Create a Thermometer Chart in Excel

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

Create and Merge Stacked Column Chart to Create a Thermometer Chart in Excel

  • We will have the Chart right next to the table.

Create and Merge Stacked Column Chart to Create a Thermometer Chart in Excel


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.

Modify Data Series to Create a Thermometer Chart in Excel

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

Modify Data Series to Create a Thermometer Chart in Excel

  • Then from the right-side panel, click on the Secondary Axis on the Series Options.

Modify Data Series to Create a Thermometer Chart in Excel

  • The Chart will look like the below image, as the columns are basically stacked now.

Modify Data Series to Create a Thermometer Chart in Excel


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.

Modify Chart Axis to Create a Thermometer Chart in Excel

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

Modify Chart Axis to Create a Thermometer Chart in Excel


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.

Modify Chart Axis to Create a Thermometer Chart in Excel

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

Modify Chart Area to Create a Thermometer Chart in Excel

  • Then on the right-side panel, click on the No Fill in Fill option and No Line in the Border option.

Modify Chart Area to Create a Thermometer Chart in Excel

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

Modify Chart Area to Create a Thermometer Chart in Excel

  • Then remove the tick from the Chart Title checkbox.

Modify Chart Area to Create a Thermometer Chart in Excel

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

Add Thermometer Bulb to Create a Thermometer Chart in Excel

  • And then we place the oval at the bottom of the Chart.
  • The final form of the Thermometer will look like the below image.

Add Thermometer Bulb to Create a Thermometer Chart in Excel

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

Add Thermometer Bulb to Create a Thermometer Chart in Excel


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

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo