How to Create a Thermometer Chart in Excel

A thermometer chart is one of the best ways to visually track progress toward a goal, and Excel is the perfect tool to build one. Whether you're monitoring a fundraising campaign, sales targets, or project completion, this simple visual makes it easy for anyone to see how close you are to your objective at a glance. This guide will walk you through creating a dynamic and professional-looking thermometer chart in Excel, step by step.

What Exactly is a Thermometer Chart?

A thermometer chart is a type of column chart styled to look like a thermometer. The "mercury" in the thermometer represents the current progress, while the full length of the thermometer represents the total goal. It’s an incredibly intuitive way to visualize percentage completion.

Why use one instead of a standard bar or pie chart? Three reasons:

• It's Thematically Spot-On: For goals that involve "heating up" or "rising" - like fundraising totals, sales quotas, or reaching a specific metric - the thermometer metaphor is a perfect fit.
• It's Radically Simple: It visualizes a single, crucial data point: progress toward one goal. There’s no ambiguity, and it’s instantly understood by anyone, regardless of their data literacy.
• It's Motivating: Seeing the mercury creep higher is a powerful visual motivator for teams and donors. It creates a sense of momentum that a simple number sometimes lacks.

Step 1: Set Up Your Project Data

Before building the chart, you need to structure your data correctly. All you need are two key numbers: your current progress and your target goal. It’s also helpful to have a cell that calculates the percentage complete, as this will be the value your chart actually visualizes.

Let's use a fundraising campaign as an example.

Open a new Excel sheet and set up your data like this:

In cell A1, type Goal Amount. In B1, enter your target, let's say \$10,000.

In cell A2, type Current Amount Raised. In B2, enter the current progress, for example, \$7,500.

In cell A3, type Percentage Complete. In B3, enter the formula to calculate this:

=B2/B1

Finally, select cell B3 and format it as a percentage by clicking the "%" symbol in the Home tab's Number group. This cell, which now shows 75%, is the key value we will use to build the thermometer.

Step 2: Create a Basic Column Chart

Now, let's create the foundation of our thermometer. It starts with a simple, single-column chart.

1. Click on the cell containing your Percentage Complete value (in our example, B3). Just that one cell.
2. Navigate to the Insert tab on the Excel ribbon.
3. In the Charts group, click the Insert Column or Bar Chart icon.
4. From the dropdown, select the first option under 2-D Column, which is a Clustered Column chart.

Excel will instantly generate a very basic chart with a single blue bar. It doesn't look like much yet, but this is our starting point. This single bar represents the "mercury" that will fill our thermometer.

Step 3: Format the Vertical Axis to a Fixed Range

Right now, Excel is automatically setting the vertical (Y) axis based on our current value. If we are at 75%, the axis might go up to 80%. This is not what we want. A thermometer should always show the full range from 0% to 100% so we can see the progress relative to the entire goal.

We need to "lock" the axis at 100%.

1. Right-click on the vertical axis of your chart (the numbers that show the percentages, like 0%, 20%, etc.).
2. From the context menu, choose Format Axis...
3. A "Format Axis" pane will appear on the right side of your screen. Under Axis Options, you'll see a section for Bounds.
4. Change the Minimum bound to 0.0.
5. Change the Maximum bound to 1.0. (Excel interprets 1.0 as 100% when the data is formatted as a percentage).

Press Enter. You'll see the chart's axis now permanently fixed between 0% and 100%. The blue bar will adjust accordingly, showing a more accurate visual representation of your progress.

Pro Tip: While you’re in this menu, you can also adjust the "Units" for the Major lines if you want fewer or more gridlines - for example, a Major unit of 0.25 would show gridlines at 0%, 25%, 50%, 75%, and 100%.

Step 4: Style the Chart to Look Like a Thermometer

This is where we turn a boring column chart into a recognizable thermometer. It involves a few formatting tweaks.

Widen the Thermometer Body

A real thermometer's body is much thicker than the default Excel column. Let's fix that.

1. Right-click directly on the colored bar in your chart (the blue bar representing 75% progress).
2. Select Format Data Series... from the menu.
3. The format pane will appear on the right. Under Series Options, find the slider for Gap Width.
4. Decrease the Gap Width. A number between 0% and 50% usually looks best. For this example, let's drag it down to 20%. This makes the bar much wider and more substantial.

At the same time, you can also change the fill color. Select the bar, go to the Fill & Line tab (the paint bucket icon) in the Format Data Series pane, and choose a more appropriate color, like a classic red or a branded color for your organization.

Add the Thermometer's Glass Casing

The trick to making the chart appear as though the mercury is rising inside a glass casing is to superimpose a see-through bar over a solid one. This is less intuitive, but it's simpler than it sounds.

1. You need to go back to your source data. Select a blank cell next to your "Percentage Complete" value, say C3, and type 100%.
2. Right-click on your chart and choose Select Data....
3. In the "Select Data Source" window that pops up, click the Add button under Legend Entries (Series).
4. In the dialog that appears, click into the "Series values" box and select the cell that contains 100% (cell C3). Click OK, and OK again.
5. You now have a two-bar column chart. Don't worry, we're about to merge them!
6. Right-click on either bar in your chart and select Format Data Series... again.
7. Under Series Options you'll find a slider for Series Overlap. Drag it all the way to 100%. This will stack the two columns directly on top of each other. The orange bar (100% "Total") is now hiding the red "Progress" bar, which is exactly what we want.
8. Click on the chart to make sure the "total" column (the one hiding on top) is selected. Go to the Fill & Line tab on your format data series panel on the right.
9. For the Fill, select No fill.
10. For the Border, select Solid line and choose a dark gray color. You can also increase the stroke's "Width" slightly (e.g., to 1.5 pt) to make the outline more prominent.

Voila! You now have a red bar rising inside a gray rectangular outline. It’s starting to look much more like a thermometer body.

Add the Bulb at the Bottom

No thermometer is complete without the circular bulb at the base. We can add this using Excel's built-in shapes.

1. Navigate to the Insert tab and click on Shapes.
2. Select the Oval shape from the Basic Shapes category.
3. Hold down the Shift key while you click and drag to draw a perfect circle on your worksheet. Make its diameter slightly wider than the width of your thermometer bar.
4. With the circle shape selected, go to the Shape Format tab that appears on the ribbon.
5. Set the Shape Fill to the same color as your progress bar (e.g., red).
6. Set the Shape Outline to the same color as your thermometer casing's border (dark gray). Again, you can match the line width here too.
7. Finally, drag the circle so it sits centered at the bottom of your thermometer's column, overlapping it just slightly to create a seamless look. You can use the arrow keys for fine-tuning the position.

Step 5: Clean Up and Add Finishing Touches

Your chart now looks like a thermometer, but it's likely still cluttered with extra elements that we don't need.

• Delete the Legend: Since it's visually obvious what the chart represents, you can delete the legend. Click on it and press the Delete key.
• Delete the Y-Axis Labels: The visual fill is the point, so the vertical axis labels are redundant and are just unwanted noise. Click on the Y-Axis (vertical axis labels) and press Delete.
• Delete Gridlines: The horizontal gridlines are distracting. Click on any of them and press Delete.
• Add a Data Label: It's useful to show the exact percentage directly on the chart so that your users/audience are able to read it.
• Adjust the Chart Title: Click the "Chart Title" and give it a clear, descriptive name like "Fundraising Progress" or "Q3 Sales Goal."

Your thermometer chart is now finished, polished, and ready to share! Best of all, it's completely dynamic. Go back to your data sheet and change the "Current Amount Raised" in cell B2. As soon as you update the number, the "mercury" in your thermometer will rise or fall automatically.

Final Thoughts

Building a thermometer chart in Excel from scratch is a fantastic way to turn a key metric into a highly motivating and easy-to-understand visual for your team or stakeholders. By following these steps, you can set up a professional, dynamic chart that visualizes progress toward any goal.

Creating custom charts in spreadsheets is incredibly powerful for one-off reports, but staying on top of all marketing and sales data across multiple platforms can quickly become a time-sink of manual exports. At Graphed , we've automated this entire process. We connect directly to tools like Google Analytics, Shopify, and Salesforce, allowing you to build real-time dashboards using simple, natural language. Instead of building charts manually, just ask for what you want to see - like "show me my sales pipeline from HubSpot this quarter" - and get the answers you need in seconds.