Create a Thermometer Chart in Excel: Steps and Tips

Charts & pivot tables · Updated November 19, 2026

To create a thermometer chart in Excel for Microsoft 365 on Windows, enter a completed amount and a goal, calculate the completed and remaining percentages, then insert a 2-D Stacked Column chart and format it as a single vertical bar. Set the value axis from 0 to 1, hide the axes and legend, and color the completed portion; an optional circle at the bottom gives the chart a thermometer bulb.

What a thermometer chart shows

A thermometer chart is a visual way to show progress toward a single target. The filled section represents what has been completed, while the unfilled section represents what remains. For example, a fundraising total of $6,300 against a $10,000 goal appears as a column filled to 63 percent. The same design can show tasks finished, units produced, training completed, or any other measurable amount compared with a target.

Excel does not have a built-in chart type named Thermometer. The usual approach is to use a stacked column chart with two data series: completed and remaining. Because the series stack on top of each other, they form one column whose full height represents 100 percent of the goal. Formatting removes the chart features that are not needed and leaves a simple progress indicator.

This works best when one number is being compared with one meaningful goal. If you need to compare several teams or periods, a thermometer chart can become difficult to read; a bar chart or a line chart may communicate the comparison more clearly.

Prepare the data and calculate progress

Keep the inputs separate from the chart values so the chart can update when the actual amount or goal changes. For a basic example, enter these labels and numbers:

  • Enter Actual in A1 and 63 in A2.
  • Enter Goal in B1 and 100 in B2.
  • Enter Category in A4, Complete in B4, and Remaining in C4.
  • Enter Progress in A5. The chart helper values will go in B5 and C5.

With the actual amount in A2 and the goal in B2, enter =MIN(A2/B2,1) in B5. In this example, B5 returns 0.63, or 63 percent. The MIN function caps the displayed completion at 100 percent if the actual amount exceeds the goal. This is useful for a goal tracker, but it means the chart will not show how far past the goal the result went.

With the completed percentage in B5, enter =1-B5 in C5. The result is 0.37, or 37 percent remaining. Format B5:C5 as Percentage if you want to inspect the values easily; the chart can use these values whether or not the cells display a percent sign.

Check that the goal is greater than zero before dividing. A zero goal makes the division invalid, and a negative goal generally does not make sense for this kind of progress display. If a goal can be blank or zero, add a check to your worksheet or leave the chart blank until a valid goal is entered.

Create the stacked column chart

  1. Select A4:C5, including the series headings and the Progress category label.
  2. On the ribbon, choose Insert > Insert Column or Bar Chart > 2-D Stacked Column. Do not choose 100% Stacked Column: the helper values already add up to 100 percent, and a regular stacked column makes the axis bounds easier to control.
  3. If Excel puts Complete and Remaining on the category axis instead of creating one stacked column, select the chart and choose Chart Design > Switch Row/Column. The desired result is one column divided into two colored sections.
  4. Move and resize the chart so the column has room to look tall and narrow. A roughly square chart area can make the column look more like a gauge, but the proportions are a design choice.

In the Windows desktop version of Microsoft 365, the Chart Design tab appears when the chart is selected. Excel for Mac has similar chart controls, although ribbon layout and labels can vary by release. In Excel for the web, chart creation is available, but some detailed formatting controls may be more limited than in the desktop application; open the workbook in desktop Excel if a control described below is not available.

Format the column to resemble a thermometer

First remove the visual elements that make the chart look like a standard business chart. Select the chart legend and press Delete, then remove the horizontal gridlines if Excel added them. You can also use the Chart Elements button beside a selected chart in Windows to turn off Legend and Gridlines. Keep the plot area simple and avoid a chart title if a nearby worksheet heading already explains the measure.

Set the vertical value axis to a fixed range. Right-click the vertical axis and choose Format Axis. In Axis Options, set the minimum bound to 0 and the maximum bound to 1. This ensures the bar always represents a full goal, even when progress is low. If the axis displays numbers, use the Number section of the formatting pane to choose Percentage, or hide the axis entirely for a cleaner thermometer effect. If you hide it, keep a nearby label such as 63% complete so readers can still see the exact value.

Next, format the two sections. Select the completed segment and use Format Data Series to choose a strong fill color, such as red, blue, or green. Select the remaining segment and give it a pale neutral fill. Use solid fills with enough contrast to distinguish the two parts. In Format Data Series, reduce Gap Width to 0% or a small value so the column appears broad and continuous; a narrow column can look more thermometer-like, so adjust the chart width as well.

For a particularly clean finish, remove the chart-area and plot-area borders and backgrounds if they distract from the bar. These settings are available by right-clicking the relevant area and choosing Format Chart Area or Format Plot Area. Keep the chart’s formatting restrained: a 3D effect, gradients, or heavy borders can make the progress harder to judge rather than more informative.

Add an optional bulb and useful labels

A stacked column gives you the thermometer tube, but not the rounded bulb. To create one, choose Insert > Shapes > Oval and draw a circle centered at the base of the column. Apply the same fill color as the completed series and remove the outline, or use a subtle outline that matches the fill. The circle is a separate worksheet shape, not part of the chart data, so position it carefully and check it after resizing the chart.

Excel may place a shape over or beside a chart depending on where you draw it. If the chart is moved or resized, the bulb may not stay perfectly aligned. One alternative is to leave the circle out: a simple vertical progress column still communicates the value and is easier to maintain. If the bulb is important to the design, check the alignment whenever you change the chart’s size or move it to another part of the sheet.

A data label can show the precise completion percentage. Select the completed segment, then use Chart Design > Add Chart Element > Data Labels and choose a suitable position. Depending on the chart layout, the label may be easier to read above the filled segment or in a nearby cell. Avoid placing it over a color boundary, where it could be mistaken for a value belonging to the remaining section.

Keep the chart connected to changing values

The chart updates when A2 or B2 changes because B5 and C5 are formulas linked to those inputs. For example, changing A2 from 63 to 80 while leaving the goal at 100 changes the completed value to 80 percent and the remaining value to 20 percent. The chart should redraw automatically, provided calculation is enabled and the chart series still point to B5:C5.

If you want to track multiple goals, make a separate helper row for each item and decide whether the chart should show one item at a time or compare several. A single thermometer with a drop-down selection requires additional setup, while multiple stacked columns can be used for side-by-side progress comparisons. Do not put unrelated measures on one thermometer: a percentage of tasks complete and a dollar amount raised need comparable goals before their progress can be fairly compared.

For dashboards, label the actual value and target in ordinary cells near the chart. A visual gauge alone can hide important context, such as whether a 63 percent completion rate means 63 of 100 units or 630 of 1,000. Showing the numerator, goal, and percentage gives readers a more complete picture.

Common problems and how to fix them

  • The chart shows two separate columns. Switch rows and columns using Chart Design > Switch Row/Column. Confirm that the values for Complete and Remaining are two series in the same category.
  • The column does not fill the chart height. Check that the vertical axis maximum is 1 and the minimum is 0. Automatic axis bounds can make a progress value look larger or smaller than it is.
  • The remaining section is missing. Confirm that C5 contains the remaining calculation and that the selected chart range includes both B5 and C5. Also check that the remaining series has a visible fill rather than No Fill.
  • The bar shows more than 100 percent or an error. Check the goal input and the formulas. The MIN formula caps progress at the goal, but a zero goal still causes a division error. Consider whether an over-goal result should be capped or displayed by a different visual.
  • The bulb drifts after resizing. The oval is a separate shape. Reposition it after changing the chart, or remove it to make the chart easier to move and maintain.

A common misconception is that a thermometer chart must use a special Excel chart type or a thermometer image. It does not: the essential feature is a stacked column made from completed and remaining portions. The visual styling is optional, and the data relationship is what makes the chart accurate.

When a different chart is a better choice

For a single goal, the thermometer design is compact and immediately recognizable. For several goals, horizontal progress bars are often easier to compare because they can share a common scale and labels can sit alongside each bar. For progress over time, a line chart can show whether the project is accelerating, slowing, or staying flat; a thermometer only shows the current total unless you build a more complicated display.

A conditional-formatting data bar in a cell is another lightweight option. Select the cell or range, then choose Home > Conditional Formatting > Data Bars. A data bar is quick to maintain and stays close to its numeric value, but it is not a standalone chart and may not look like a thermometer. Use the simplest display that makes the comparison and the underlying numbers clear.

Frequently Asked Questions

Does Excel have a built-in thermometer chart?

No. In Microsoft 365, you can create the effect with a stacked column chart using completed and remaining values. The chart is still an ordinary Excel chart, so you can edit its data and formatting through the regular chart tools.

Can a thermometer chart show progress above 100 percent?

Not with the capped formula shown here: MIN limits the completed portion to 100 percent. That is appropriate when the graphic means progress toward a fixed goal. If exceeding the target matters, show the actual amount and goal as labels or choose a chart that can make the overage visible without distorting the scale.

Why does my thermometer chart show the wrong percentage?

Check that the actual amount and goal are in the intended cells, the goal is positive, and the chart includes the calculated Complete and Remaining cells. Also verify the value-axis bounds are 0 and 1. A chart using automatic bounds may look misleading even when its source formulas are correct.

Can I make a horizontal thermometer chart?

Yes. Use a 2-D Stacked Bar chart instead of a Stacked Column chart, then format the horizontal value axis from 0 to 1 and remove unnecessary axes and gridlines. The same completed and remaining helper values can be used; the chart simply runs from left to right.

Will the chart update when I change the goal?

Yes, if the chart uses the formula cells and those formulas refer to the actual and goal inputs. Changing either input recalculates the completed and remaining portions. If the display does not change, inspect the chart’s Select Data source and confirm it still includes the helper cells.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.