How to Make a Thermometer Chart in Excel

Charts & pivot tables · Updated November 20, 2026

To make a thermometer chart in Excel for Microsoft 365 on Windows, enter a completion value and the amount remaining in two cells, select those cells with their category label, then choose Insert > Insert Column or Bar Chart > 2-D Stacked Column. Set the vertical axis maximum to the goal, then format the two stacked series so one looks like the filled portion of a thermometer and the other like the empty portion.

What a thermometer chart shows

A thermometer chart is a visual progress indicator: its colored column rises as a value approaches a defined target. It can make a single measure—such as fundraising progress, a project milestone, or a production quota—easy to read at a glance. Excel does not have a dedicated “thermometer chart” type. The familiar appearance is built from a stacked column chart and careful formatting.

The chart’s two parts represent the amount completed and the amount still to go. Their combined height equals the goal, so the top of the column stays at the same level while the colored portion grows. This is different from charting the completed value alone: a column chart of 65% would typically be scaled against the other values or the chart’s automatic axis, rather than consistently showing how close it is to a 100% goal.

Thermometer charts work best for one main measure and one clear target. If you need to compare many departments or track several metrics at once, a bar chart, bullet chart, or small table may communicate the information more clearly.

Prepare the data for a 100% goal

Set up one category and two series. In a blank worksheet, enter these headings and values:

  • A1: blank; B1: Complete; C1: Remaining
  • A2: Progress; B2: 65%
  • C2: enter the remaining-amount formula described below.

With completion in B2 set to 65% and the goal fixed at 100%, enter this in C2: =100%-B2. The formula returns 35%, so the two chart values add up to 100%. Use a real percentage value in B2, such as 65%, rather than the number 65 formatted to look like a percentage; Excel stores 65% as 0.65.

If the target may change, place it in E2 and use this layout: B2 contains the current amount, E2 contains the goal, and C2 is the remaining amount. With B2 at 325 and E2 at 500, enter =E2-B2 in C2; the result is 175. The chart will then show 325 completed out of a total of 500. Keep the completed value between zero and the goal, because a negative remainder or a completed value above the goal can make the stacked chart misleading or push it beyond the intended scale.

Insert the stacked column chart

  1. Select A1:C2, including the blank corner cell, both series headings, the category name, and the two values.
  2. On the ribbon, choose Insert > Insert Column or Bar Chart > 2-D Stacked Column. Avoid 100% Stacked Column: that chart normalizes the series to 100% and can conceal whether the target is a percentage or a different quantity.
  3. Check that the result contains one column divided into two segments. The lower segment should represent Complete, and the segment above it should represent Remaining.

Because the data has one category and two series, a stacked column is the appropriate structure. If Excel creates two separate columns instead, check the layout: the two values must be in separate series columns on the same data row, as in B2 and C2, rather than listed as two category rows. You can also select the chart and use Chart Design > Switch Row/Column if Excel has interpreted the selection differently.

Set the axis and shape the thermometer

The vertical axis should use the same scale as the goal. Right-click the vertical axis and choose Format Axis. In Axis Options, set the minimum bound to 0 and the maximum bound to the goal. For the percentage example, enter 1 as the maximum because 100% is stored as 1; for the example with a goal of 500, set the maximum to 500. Setting a fixed maximum is important: an automatic scale can change as the value changes and make progress look inconsistent.

To make the column resemble a thermometer, right-click a segment and choose Format Data Series. Under Series Options, set Series Overlap to 100% if that control is available for the selected chart, and adjust Gap Width to make the column narrower or wider. A smaller gap produces a thicker column; a larger gap makes it slimmer. A moderately narrow column usually looks more like a thermometer stem than a wide bar.

Give Complete a strong fill color, such as red, blue, or a brand color. Give Remaining a white or very pale fill and a light outline so that the unfilled portion remains visible against the chart background. Remove visual clutter you do not need: select chart gridlines, a legend, or the chart title and press Delete, or use the Chart Elements button beside the chart. If you retain the vertical axis, format it as a percentage for a percentage goal, or use the units appropriate to the measure.

Add labels and make the chart understandable

A thermometer graphic is most useful when readers can tell what it measures and what the target is. Add a descriptive chart title, such as “Annual Giving Goal,” and consider a data label on the completed segment. To add a label, select the Complete segment, then use Chart Design > Add Chart Element > Data Labels and choose a position. Check that the label displays the useful value; depending on the selection and Excel build, you may need to open Format Data Labels to choose the value or number format.

Keep the target visible somewhere in the chart or nearby. A label such as “$32,500 of $50,000” gives context that a colored column alone cannot. For percentages, use a consistent format and avoid showing excessive decimal places. If you remove the axis to create a cleaner graphic, include the completed value and goal in a title, label, or adjacent worksheet cell so the chart does not become an attractive but ambiguous shape.

For readers using screen readers, do not rely on color alone to explain progress. Keep a plain-language title, retain the numeric value in the worksheet, and use a contrasting fill and outline. A chart’s alternative text can also be added through its context menu, where available, using a concise description of the measure, value, and target.

Update the chart as values change

Once the chart is built, change the completion cell and the remaining amount will recalculate automatically if it contains a formula. For example, changing B2 from 65% to 80% makes the remaining portion 20% when the formula is =100%-B2. The chart updates because it is linked to those worksheet cells; there is no need to redraw it.

For a variable target, use the corresponding goal cell in the remaining calculation. With current amount in B2 and target in E2, =E2-B2 in C2 recalculates the uncompleted amount whenever either input changes. The chart’s maximum axis bound does not necessarily update just because E2 changes. If the target varies, update the axis maximum to match it, or consider a design that handles changing scales more clearly. A thermometer whose axis remains fixed at 500 will not accurately represent a new goal of 1,000.

If you expect to make many charts, you can keep the source values in a small, clearly labeled area and place the chart elsewhere on the sheet. That makes it easier to edit inputs without accidentally changing the chart’s source data. You can also format the input cells differently from formula cells so other users can distinguish what they should edit.

Common problems and fixes

  • The chart shows two columns. Arrange Complete and Remaining as two series in adjacent columns on one row, then select the category label and both series. If needed, use Chart Design > Switch Row/Column.
  • The column reaches the top at the wrong value. Set a fixed vertical-axis maximum equal to the goal. For a percentage goal stored as 100%, the maximum is 1, not 100.
  • The remainder changes the scale unexpectedly. Confirm that the chart type is Stacked Column, not 100% Stacked Column, and that the axis maximum is fixed.
  • The remaining segment is invisible. A white segment on a white chart background can disappear. Add a subtle outline or use a pale gray fill.
  • The chart looks too wide or too thin. Adjust Gap Width in Format Data Series. The exact visual result depends on chart size, so resize the chart as well as changing the gap.
  • The bar exceeds the target. Check that the current value does not exceed the goal and that both values use the same units. For example, do not combine 65 with a target of 100% if 65 means 65%.

When to use another chart

A thermometer chart is a decorative form of a progress bar, not a special measurement instrument or a built-in Excel chart category. Its strength is showing one value against one target. It is less effective when several goals need comparison, when the target changes frequently, or when readers need to inspect detailed trends over time.

For multiple categories, a horizontal bar chart often makes comparisons easier because every category has a visible label and a common scale. For progress over time, use a line chart with dates and a target reference line. For a compact dashboard, a cell-based progress bar made with conditional formatting can be easier to align with a table, although it has less of the thermometer appearance. A simple numeric value with a clear target may be the best choice when precision matters more than visual styling.

Excel for Mac and Excel for the web

The basic method also works in Excel for Microsoft 365 on Mac: arrange the data the same way and use the Insert tab to insert a 2-D Stacked Column chart. Formatting options are available by selecting or Control-clicking chart elements and opening the formatting pane, though ribbon placement and pane layout can differ from Windows. The chart type and underlying data arrangement are the same.

Excel for the web can create and edit many standard charts, but some detailed formatting controls may be limited or placed differently from the desktop application. If you cannot set the axis bounds or series appearance as described, open the workbook in the desktop version of Excel. The Windows ribbon paths above refer to Microsoft 365 desktop Excel.

Frequently Asked Questions

Does Excel have a built-in thermometer chart?
No. Create the appearance with a 2-D Stacked Column chart, using one series for the completed amount and another for the remainder. The formatting makes it look like a thermometer.

Why does my thermometer chart show two bars?
Excel is likely treating the completed and remaining values as categories rather than as series. Put the two values in separate columns on the same row, with one category label, then select the full range and insert a stacked column chart.

Should I use a stacked column or a 100% stacked column?
Use a regular Stacked Column chart when the values already add up to the target. A 100% Stacked Column chart rescales each category to a 100% total, which can hide differences in actual goal size.

How do I make the thermometer update automatically?
Link the chart to worksheet cells and calculate the remaining amount with a formula. Editing the completed value recalculates the remainder and updates the chart; a variable goal may also require changing the axis maximum.

Can I make a thermometer chart for a target other than 100%?
Yes. Put the current amount and goal in the same units, calculate the remainder as goal minus current amount, and set the vertical-axis maximum to the goal. For example, a current value of 325 and a goal of 500 leave 175 to go.

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.