How to Create a Thermometer Chart in Excel?
The Thermometer chat in Excel can be used to depict specific data based on the actual value and the target value. It can be used in a wide range of scenarios such as representing the past performance of horses in horse racing or the global temperature and it’s variation throughout decades etc.
In this article, we will look into how we can create a Thermometer chart in Excel.
Steps for creating a Thermometer Chart in Excel
Follow the below steps to create a thermometer chart in Excel:
Note: This article is written using Microsoft Excel 2010, but all the steps shown below are valid for all later versions.
Step 1: Creating your Data Table. First, you must create your data table for the chart. For this article, we’ll see the sales data of a fictional retail store.
This table must contain TOTAL and TARGET rows as well. Now, add two more rows below this table viz. Achieved % and Total %:
Note that the Total % will always be 100%
Step 2: Form the Bar Chart. Now, select the last two rows that you added and go to Insert. Select the 2D Clustered Column from the Column Charts section.
Now, select the chart, and click on Switch Row/Column under the Design Tab.
The final result will look like the following:
Step 3: Final adjustments. Right-click on the Red Bar and select Format Data Series and set the Plot Series On values to Secondary Axis as shown below.
The final result will look like this
Now, select the Vertical Axis on the left-hand side and right-click on it. Then click on Format Axis and set the Minimum and Maximum values as shown below then hit Close.
Delete the Vertical Axis on the Right-Hand side.
Right-click on the Red Bar and select Format Data Series, and make the following changes.
The final result will look like this after deleting the Axis lines and the legend.
Now, resize the chart to look like a Thermometer and delete the bottom Axis. Then, add an oval shape from the Design tab under the Chart to make it look like an actual thermometer.