Excel Thermometer Chart – Free Download & How to Create

This tutorial will demonstrate how to create a thermometer chart in all versions of Excel: 2007, 2010, 2013, 2016, and 2019.

Thermometer Chart – Free Template Download

Download our free Thermometer Chart Template for Excel.

Download Now

A thermometer chart is a special modification of a stacked column chart. The columns are placed on top of each other and visually resemble a thermometer, which fills up as you progress towards your goal.

Any time you need to track progress towards a goal—be it funding, sales, revenue, or other relevant metrics—this chart provides a simple and effective way to visualize that data.

But a simple thermometer chart just doesn’t cut the mustard anymore (come on, this is 2020!).

In this in-depth, step-by-step tutorial, you will learn how to plot a remarkably versatile thermometer goal chart with a fancy label that works even when your results exceed a stated goal.

Just check it out:

How to create a thermometer chart in Excel

As you will see shortly, building an impressive thermometer chart takes tons of effort. That’s why we designed the Chart Creator Add-In, a tool for building advanced Excel charts in just a few clicks.

So, let’s get down to the nitty-gritty.

Getting started

For illustration purposes, imagine you launched an e-commerce store as a side hustle and want to build a thermometer chart to keep track of the store’s performance against your stated revenue goal. In 2019, you had a pretty good run.

The store generated $21,734 in revenue, slightly exceeding your initial goal (you can pop a bottle of champagne later).

That being said, take a look at the dataset for the chart:

Thermometer chart dataset

Before we move on to building the chart, we need to break down each data point so that you can easily retrace the steps using your data.

The data set has two columns:

  • Original Data: This column represents your actual data, whatever it may be. Just leave it as it is.
  • Chart Inputs: This is where all the magic happens. The table contains all the special values you will use for building the chart. Also, an important note: the categories must be ordered in the exact same way as shown on the screenshot above!

You might be wondering where all the values in the second table come from. Well, you will actually have to calculate them yourself using the special Excel formulas listed below (surprise, surprise).

Unfortunately, there is no other way around it—but the instructions below should make it fairly easy to follow.

So the journey begins!

Step #1: Set up the helper table.

As the linchpin holding it all together, each of the elements in the second table have their purpose. Let’s cover these bad boys more in detail.

Target Revenue: This data point should match the goal value (B7). As simple as that.

Total Revenue Formula: This IF function prevents the data markers from stacking on top of each other the wrong way in case the result exceeds the goal:

Here is the decoded version based on the original data:

Translation into basic human language: if the result (B6) is less than or equal to the goal (B7), the formula returns the result value (B6)—in our case, anything that falls below the $20K mark. Conversely, if the result (B6) exceeds the goal (B7), the formula returns the goal value (B7).

Quick tip: click the “Accounting Number Format” button (Home tab > Number group) to display the function output as currency.

Total Revenue formula

Overachieved: This IF function calculates whether or not your target is overachieved. Whenever that takes place, the formula subtracts the goal (B7) from the result (B6) and returns what is left over. Otherwise, the formula simply shows zero:

The decoded version:

Overachieved formula
Progress: This one is pretty self-explanatory. We need to measure the progress as a percentage of the goal for our ultra-fancy label. Simply divide the result (B6) by the goal (B7) and format the output as a percentage—click the “Percent Style” button (Home tab > Number group).

Decoded version:

Progress formula
Label: The icing on the cake. This one combines the numeric and percentage values representing the progress towards your goal and neatly formats them. To pull it off, we will turn to the TEXT function (Excel newbies, brace for impact):

And the decoded version:

If that seems daunting, check out the article linked above covering the function.

Here is how it looks in practice:

Label formula

Whew, the worst is behind us. To sum it all up, take a second glance at the formulas:

Chart Setting Formula
Total Revenue Formula =IF(B6<=B7, B6, B7)
Overachieved =IF(B7<=B6, B6-B7, 0)
Progress =B6/B7
Label =TEXT(B6, “$#0.00″) & ” (” &  TEXT(E5, “##0%”) & “)”

Once there, move on to the next step.

Step #2: Create a stacked column chart.

  1. Highlight the cell range E2:E4 in the Value
  2. Select the Insert
  3. Click the “Insert Column or Bar Chart” icon.
  4. Choose “Stacked Column.

Creating a stacked column chart in Excel

Step #3: Stack the data series on top of each other.

Once the chart appears, rearrange the data series:

  1. Click on the chart.
  2. Switch to the Design
  3. Choose “Switch Row/Column.

Switch Row/Column in Excel

Step #4: Change the data marker colors.

To turn the chart into something resembling a thermometer, start by changing the color of the data markers. First, make the data marker for Series 1 “Target Revenue” (E2) transparent and put border lines around it.

Right-click on the data marker representing the “Target Revenue” (E2—the bottom section of the chart) and select “Format Data Series.

How to color data markers in Excel

In the task pane that pops up, do the following:

  1. Navigate to the Fill & Line
  2. Under “Fill,” select “No fill.”
  3. Under “Border,” choose “Solid line.”
  4. Under “Border,” click the Fill Color icon to open the color palette and select red.
  5. Set the Width value to 2 pt.

Coloring data markers in Excel

Color the other data markers as follows:

  1. For Series 2 “Total Revenue Formula” (E3):
    1. Select the data marker (E3) with the Format Data Series task pane still open.
    2. Under “Fill,” choose “Solid fill.”
    3. Click the Fill Color icon and select red.
  2. For Series 3 “Overachieved” (E4):
    1. Select the data marker (E4) with the Format Data Series task pane still open.
    2. Under “Fill,” choose “Solid fill.”
    3. Click the Fill Color icon and select light green.

Step #5: Add the data label to the chart.

Now it’s time to insert the lovely data label (E6) that took us so much blood, sweat, and tears to put together.

Right-click on Series 2 “Total Revenue Formula” (the one with a red fill) and choose “Add Data Labels.”

Adding data labels to an Excel chart

Step #6: Change the data label value.

Link the label to the value in the second table.

  1. Double-click on the label.
  2. Type “=” into the Formula bar.
  3. Select the Label value (E6).

Creating custom data labels in Excel

Now we need to spruce it up a bit. Make the label bold, color it in white, and change the font size to enhance the element aesthetically (Home tab > Font group).

Step #7: Reposition the data label.

After you have set up the label, push it to the upper end of the related data marker. Right-click on the data label and choose “Format Data Labels.

Formatting data labels in Excel

Then, in the Label Options tab, under Label Position, choose “Inside End.”

Repositioning data labels in a chart

Step #8: Move Series 1 “Target Revenue” to the secondary axis.

To mold the chart into a thermometer shape, you need to get Series 1 “Target Revenue” (E2) in the right position.

  1. Right-click on Series 1 “Target Revenue” (the transparent one at the very bottom).
  2. Pick “Format Data Series.

Formatting data series in Excel

In the task pane that appears, under Plot Series On, choose “Secondary Axis.

Moving data series to the secondary axis in Excel

Step #9: Remove the secondary axis.

The secondary axis did its job, so we no longer need it.

Right-click on the secondary axis (the column of numbers on the right) and select “Delete.”

Removing the secondary axis in Excel

Step #10: Modify the primary axis scale ranges.

As you may have noticed, the primary axis scale looks a bit off. Let’s change that.

Right-click on the primary axis scale and choose “Format Axis.

Changing the primary axis scale

Set the Minimum Bounds value to -10000. Why? Doing so will free up some space below for the thermometer bulb.

This value differs based on your chart data. A good rule of thumb is to use your goal number and divide it by two.

For instance, suppose your revenue goal is $100,000. Then your Minimum Bounds value would be -50000.

Modifying the primary axis scale ranges in Excel

Step #11: Change the number format of the primary axis scale and add tick marks.

Those negative numbers do not help us here. Fortunately, we can easily get rid of them using the power of custom number formats.

With the Format Axis task pane still open, under Axis Options, navigate to the Numbers section and do the following:

  1. For “Category,” select “”
  2. Type “$#,##0.00;;$0” into the Format Code field and click Add.

Also, under Tick Marks, choose “Inside” from the “Major type” dropdown menu.

Formatting axis scale numbers in Excel

Now, switch to the Fill & Line tab. Change the color of the tick marks to black and set the Width value to 1.5 pt.

Coloring tick marks in Excel

Step #12: Remove the chart title, gridlines, and horizontal axis.

Remove the chart elements that have no practical value: the chart title, gridlines, and horizontal axis. Right-click each element and select “Delete.”

How to create a thermometer goal chart in Excel

Step #13: Change the gap width of the data series on the secondary axis.

As you are putting the final touches on your chart, slim down the thermometer tube. Start with the series placed on the secondary axis (Series 1 “Target Revenue”).

Right-click on Series 1 “Target Revenue” (E2), open the Format Data Series task pane, and set the Gap Width value to 250%. Increase the percentage if you want to slim it down more.

Changing data marker gap width in Excel

Step #14: Change the gap width of the data series on the primary axis.

After that, click the edge of the red tube once more to select Series 2 “Total Revenue Formula” (E3), placed on the primary axis. Set the same Gap Width value (250%).

Adjusting the spacing between data markers in Excel

Step #15: Add the thermometer bulb to the chart.

As a final adjustment, create and add the thermometer bulb to the chart.

  1. Switch to the Insert
  2. Choose the “Shapes” button.
  3. Pick “Oval.

Drawing an oval in Excel

Hold down Shift while drawing to create a perfect circle. Then right-click on the circle, and in the menu that appears, change the Shape Outline and Shape Fill colors to red.

Drawing a perfect circle in Excel

Just one last thing. Copy the circle by selecting it and pressing Ctrl + C. Then select the chart and press Ctrl + V to paste the circle into the thermometer chart. Finally, resize and adjust the position of the bulb to make it fit into the picture.

Inserting shapes into an Excel chart

So, there you have it. You are now ready to create breathtaking thermometer charts in Excel!

Download Thermometer Chart Template

Download our free Thermometer Chart Template for Excel.

Download Now