Thermometer Chart Excel Template: Create One That Works

Charts & pivot tables · Updated November 20, 2026

A thermometer chart Excel template is usually a worksheet with an actual value and a target, paired with a stacked column chart; Excel does not have a dedicated chart type named Thermometer. In Microsoft Excel for Microsoft 365 on Windows, you can create one by splitting the target into a filled portion and an unfilled remainder, then formatting the stacked column to resemble a thermometer.

What a thermometer chart shows

A thermometer chart is a compact way to show progress toward a defined goal. The filled section represents the amount achieved, while the unfilled section represents what remains. For example, if a fundraising campaign has collected $6,800 toward a $10,000 target, the chart can show 6,800 filled units and 3,200 remaining units on an axis that runs from zero to 10,000.

This is a visual comparison, not a separate Excel chart technology. The familiar appearance is made from a stacked column chart and formatting choices. The value and target need to use the same unit: do not combine a dollar amount with a percentage, or a monthly result with an annual target, in the same thermometer.

Set up a reusable worksheet template

Start with a small input area. Put the current result in one cell and the goal in another, and label both clearly. For example, use A1 for the label Actual and B1 for the value 68; use A2 for Target and B2 for 100. These example values represent 68 units achieved out of a 100-unit goal. You can replace them with your own measure, such as tickets sold, pages read, or dollars raised.

Below or beside the inputs, create a two-part chart table with the headings Actual and Remaining. With the actual value in B1 and target in B2, put the headings in A5:B5 and the chart values in A6:B6. Use these formulas:

  • With actual in B1 and target in B2, enter =MIN(B1,B2) in A6 to cap the filled column at the target.
  • With actual in B1 and target in B2, enter =MAX(B2-B1,0) in B6 to calculate the unfilled portion.

For the example, A6 returns 68 and B6 returns 32, so the stacked column totals 100. If the actual value rises above the target, the chart’s filled part stays at the target rather than exceeding the scale. Keep the original actual input visible elsewhere in the worksheet so readers can still see an over-target result. These formulas assume nonnegative values and a positive target; add a clear check or note if your workbook allows blank, zero, or negative targets.

Create the chart in Excel for Windows

Once the two chart values are in place, select the headings and values, including A5:B6 in the example. On the ribbon, choose Insert > Insert Column or Bar Chart > 2-D Stacked Column. Check that the chart contains one category with two stacked series, Actual and Remaining. If Excel plots the series in the wrong direction, use Chart Design > Switch Row/Column.

  1. Select the chart’s vertical axis, right-click, and choose Format Axis. Under Axis Options, set the minimum bound to 0 and the maximum bound to the target value, such as 100. A fixed maximum keeps the thermometer’s scale aligned with the goal.
  2. Select a column and open Format Data Series. Set Series Overlap to 100% if the series are not already stacked as one column, and reduce Gap Width to a small value, often 0% for a narrow, solid-looking column.
  3. Format the Actual series with a strong fill color and the Remaining series with a pale contrasting color. Remove the legend if the chart is self-explanatory, and add a chart title that names the measure and goal.
  4. Resize the chart to be tall and narrow. Keep enough room for the axis labels and title, and consider adding a separate cell beside it that states the actual value and target in text.

Excel’s Format Axis and Format Data Series panes provide the controls used for these adjustments. The precise pane layout can vary with the selected chart element, so select the axis or series itself before looking for its options.

Add the thermometer bulb and polish the design

A basic stacked column can communicate progress without any decorative additions. To make it look more like a physical thermometer, some templates place a circle or oval at the base of the narrow column. In Excel for Windows, use Insert > Shapes to add an oval, then set its fill to match the Actual series and place it beneath the column. This is a visual embellishment, not part of the chart data, so it may need repositioning if the chart is resized or moved.

Use a short title and make the scale understandable. If the values are percentages, format the axis as percentages and make sure the underlying values and target are both stored consistently—for example, 68% as 0.68 and 100% as 1, rather than mixing 68 with 1. Avoid relying on color alone: include a written value, a meaningful title, and labels that explain the goal. Strong contrast between the filled and unfilled sections improves readability.

Choose the right scale and handle changing targets

A thermometer works best when there is one clear target and a meaningful zero point. A fixed axis maximum equal to the target makes progress easy to compare over time, but it means the axis may need updating when the goal changes. If you use a different target next month, review both the target input and the axis maximum; changing the worksheet cell does not necessarily change a manually fixed chart-axis bound.

For a reusable template, write the target near the chart and state the unit and time period. “68 of 100 tasks this week” is more informative than “68.” If you need several thermometers for different goals, consider whether each should have its own scale. A 50% result on a 100-unit goal and a 50% result on a 1,000-unit goal show equal relative progress, but their absolute amounts differ. Decide whether the reader needs to compare relative progress or actual quantities before choosing a shared scale.

Common problems and how to fix them

  • The chart shows two separate columns: Make sure you inserted a 2-D Stacked Column chart, not a clustered column chart. Confirm that Actual and Remaining are the two series for the same category.
  • The filled column exceeds the goal: Use a capped chart value such as the MIN formula above, and keep the uncapped actual figure in a separate input cell. Check that the vertical axis maximum matches the target.
  • The chart is empty or the remainder is negative: Confirm that the input cells contain numbers, not text that only looks numeric. Check that the actual and target use the same units and that the target is not below zero.
  • The result appears as 68 instead of 68%: Check the stored values and number format. A value of 68 is not the same as 68% in Excel; the latter is stored as 0.68 and displayed with percentage formatting.
  • The chart changes scale unexpectedly: Excel may be using automatic axis bounds. Set the minimum and maximum bounds deliberately, and revisit them when the target changes.

One frequent misconception is that a thermometer chart automatically updates its axis to match a target cell. The plotted values can update through formulas, but a manually specified axis bound is a separate chart setting. For a workbook used by others, explain which cells are inputs and which chart settings should be revisited when the goal changes.

When another chart is a better choice

A thermometer is effective for one result against one target, especially when the audience needs a quick progress indicator. It is less suitable when the chart must compare many categories, show trends over time, or communicate uncertainty. A bar chart can compare multiple projects more clearly; a line chart is usually more useful for change over time; and a gauge-style visual can show a position within defined ranges, though it can take more effort to build and interpret.

If the key question is simply “what percentage is complete?”, a cell with a percentage and a progress bar created with conditional formatting may be simpler to maintain. In Excel for Windows, select the relevant cells and choose Home > Conditional Formatting > Data Bars. A data bar is not a thermometer chart, but it can suit a table with many rows better than a separate chart for every item.

Saving, sharing, and maintaining the template

Save a reusable workbook as an Excel template using File > Save As and choose Excel Template (*.xltx). If the workbook includes macros, the macro-enabled template format is different; a standard thermometer chart does not require VBA. Keep a normal workbook copy for an active project and a clean template copy for future use. Protecting formula cells can reduce accidental edits, but make input cells easy to identify and do not hide the assumptions readers need to understand.

The core stacked-column method is also available in Excel for Mac, though ribbon placement and dialog appearance can differ from Windows. Excel for the web supports many chart tasks, but some detailed formatting controls may be limited or arranged differently. If the chart must look identical across desktop and web versions, verify the final layout in the version your audience will use.

Frequently Asked Questions

Does Excel include a built-in thermometer chart template?
Excel does not provide a dedicated Thermometer chart type in its standard chart list. A typical template uses a stacked column chart with an Actual series and a Remaining series, plus axis and color formatting.

Can a thermometer chart show progress above 100%?
It can, but the conventional thermometer design stops at the target. A capped chart value keeps the column within its scale; show the uncapped actual amount separately or choose a chart that can display overachievement clearly.

Can I use percentages instead of counts?
Yes. Use percentage values for both the actual and target, and format the axis as a percentage. Ensure the stored values are consistent—for example, 0.68 for 68% and 1 for 100%.

Why does my thermometer chart not fill to the target?
Check that the Actual and Remaining values add up to the target, that both series are stacked in one category, and that the vertical axis maximum is set to the intended goal. Also confirm that the input values are numeric and use the same unit.

Can I make several thermometers in one worksheet?
Yes, but separate charts can become crowded. For a list of many goals, a table with conditional-formatting data bars or a bar chart may be easier to scan and maintain than one thermometer chart per item.

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.