To create a thermometer chart in Excel, calculate the completed portion and the amount remaining, then plot them as a 2-D stacked column chart. Format the vertical axis from 0 to 100%, color the completed portion, and make the remaining portion a contrasting background; the result shows progress toward a target in a familiar thermometer shape.
What a thermometer chart shows
A thermometer chart is a progress visualization, not a separate built-in chart type in Microsoft Excel. It represents a single value as a proportion of a defined maximum, such as sales against a goal, tasks completed against a project total, or money raised against a campaign target. The filled section rises as the value increases, while the unfilled section shows what remains.
In Excel, the usual construction is a stacked column chart with two series: the current progress and the remainder. Because the two series add up to 100% of the target, the full column represents the target. It is important to distinguish this from a chart of the raw value alone: a value of 630 does not communicate whether performance is strong unless the reader also knows the target.
Thermometer charts are best for one headline measure and a clear goal. They are less useful for comparing many categories, showing changes over time, or displaying several different measures at once. Those jobs generally call for a bar chart, line chart, or dashboard of separate visuals.
Set up the values and formulas
Start with a small, clearly labeled table. For example, suppose a team has completed 630 units toward a target of 1,000. Enter the labels and values in the following cells:
- A1: Measure; B1: Actual; C1: Target; D1: Progress; E1: Remaining.
- A2: Quarterly target; B2: 630; C2: 1000.
With the actual value in B2 and the target in C2, enter this formula in D2 to calculate the share of the target, capped at 100%:
=MIN(B2/C2,1)
For 630 divided by 1,000, D2 returns 0.63, which displays as 63% when formatted as a percentage. The cap prevents the plotted progress from exceeding the top of the thermometer if the actual value passes the target. Keep the original actual value in B2 so you can still report that the target was exceeded.
With progress in D2, enter this formula in E2 to calculate the unfilled portion:
=1-D2
E2 returns 0.37, or 37%. The progress and remaining values total 1, equivalent to 100%. Format D2:E2 as percentages using Home > Number, or select the Percentage Style button on the Home tab. If you use a target of zero, the division in D2 is not meaningful; check that the target is a positive number before relying on the chart. If actual values can be negative, decide how they should be represented rather than assuming a standard thermometer communicates them sensibly.
Create the stacked column chart
In Excel for Microsoft 365 on Windows, select D1:E2, including the two series labels and their values. Then choose Insert > Insert Column or Bar Chart > 2-D Stacked Column. Excel should create one column divided into a progress segment and a remaining segment. If it instead creates two separate columns, select the chart and use Chart Design > Switch Row/Column so that Progress and Remaining form one stack.
- Click the chart and confirm that it contains one category with two stacked series.
- Use Chart Design > Add Chart Element > Chart Title to add a title, such as Quarterly target: 630 of 1,000.
- Use Chart Design > Add Chart Element > Axes > Primary Vertical if the vertical axis is not visible.
- Right-click the vertical axis and choose Format Axis. Under Axis Options, set the minimum bound to 0 and maximum bound to 1.
Excel stores the proportions as decimal values, so an axis maximum of 1 means 100%. With the example data, the colored progress segment reaches 0.63 of the chart height. A fixed maximum is essential: if Excel automatically chooses a maximum of 0.7, for instance, 63% would appear nearly full and exaggerate progress. The same fixed 0-to-1 scale also makes charts easier to compare across reporting periods.
Excel for Mac has comparable chart-insertion and formatting controls, though ribbon layout and pane details can differ by release. In Excel for the web, chart creation and basic formatting are available, but some detailed formatting controls may be presented differently or be more limited than in the desktop app.
Format it to look like a thermometer
Select the chart series rather than the whole chart to adjust its appearance. Click the progress segment, then open the Format Data Series pane. Choose a solid fill with a strong, accessible color. Select the remaining segment and give it a pale gray or another muted fill. A strong contrast makes the completed amount easy to identify, but avoid relying on color alone if the chart will be used by people with color-vision differences.
In the Format Data Series pane, set Series Overlap to 100% and reduce Gap Width substantially, often to 0%, if those controls are available for the selected chart. The column then becomes a broad, continuous bar rather than a narrow default column. A vertical, elongated shape is usually easier to read as a thermometer than a short, wide one. Adjust the chart area’s width and height to suit the page or dashboard rather than distorting the column by dragging only one chart element.
The first series in the stack should be Progress so that the colored portion rises from the bottom and Remaining sits above it. If the order is reversed, the filled segment may appear at the top. You can change the series order through Chart Design > Select Data, then use the move-up or move-down controls in the Legend Entries (Series) list. Since the display is a column chart, the legend may be unnecessary: select it and press Delete, or remove it through Chart Design > Add Chart Element > Legend > None.
For a clean visual, remove unnecessary gridlines and chart borders, but retain a scale or a clear percentage label if readers need to judge the value precisely. Use Chart Design > Add Chart Element > Data Labels to show the progress value, then format the label so it is readable and does not obscure the fill. A concise chart title can state both the measure and its target. If you add a decorative circle beneath the column to suggest a thermometer bulb, treat it as a shape, not data: it will not automatically communicate a value and can make later resizing more fiddly.
Make the chart update with changing data
The formulas in D2 and E2 recalculate when the actual or target changes, and a chart linked to those cells updates with them. This makes the chart useful for a recurring report: replace B2 with the latest actual value, and the progress segment changes while the target remains fixed. If the target changes too, update C2 and check that the chart still uses the intended 0-to-1 axis scale.
For multiple reporting periods, do not simply add a long row of progress values to the same thermometer and expect a single gauge. A thermometer is designed to communicate one current measure. You can keep a single chart and update its source cells, or use a separate chart type to compare periods. If you do create several thermometers, give every one the same 0-to-1 vertical-axis bounds; independent automatic scaling can make equal percentages look different.
When the source table grows, consider converting it to an Excel table with Home > Format as Table. Tables can make structured reports easier to maintain, but the basic two-value thermometer does not require a table. For a one-off graphic, the small range D1:E2 is often simpler and less likely to include unintended data.
Common problems and how to fix them
- The chart shows two columns. The data is plotted by rows instead of by series. Select the chart and choose Chart Design > Switch Row/Column, then check that the two series are stacked.
- The colored bar appears at the top. The series order is reversed. Move Progress above Remaining in Chart Design > Select Data.
- The column looks almost full too early. The vertical axis is using automatic bounds. Set the minimum to 0 and maximum to 1 so that 100% always represents the full height.
- The chart exceeds 100% or shows an error. Check that the progress formula refers to the correct cells, that the target is not zero, and that the progress value is capped as intended. The example formula caps positive results at 1 but does not clamp negative results to zero.
- The fill and remainder are hard to distinguish. Choose contrasting fills, remove visual clutter, and use a data label or percentage text so the value is not communicated by color alone.
- The chart changes when it should not. Check the selected chart data range and formulas. A mistaken selection can include labels or unrelated cells as additional categories or series.
One common misconception is that the thermometer’s filled height automatically means the displayed number is a percentage. The visual is only meaningful relative to its scale. A value of 630 plotted against an axis maximum of 1,000 could work if the data and axis are deliberately set up that way, but the two-series percentage method makes the target relationship explicit and is generally easier to format consistently.
When to choose another chart
Use a thermometer when the audience needs an immediate answer to a question such as, How close are we to this one target? Choose a regular bar chart when comparing several teams, products, or targets; side-by-side bars are easier to compare than a row of narrow thermometer graphics. Choose a line chart when the important story is how a value changes over time. A KPI card with a large percentage may be clearer than a thermometer when the exact number matters more than the sense of filling up.
A progress bar made with conditional formatting is another option for a compact cell-based report. It can keep the display close to the underlying data, but it does not provide the same standalone chart object or title and axis controls. For a polished single-measure dashboard element, the stacked column is flexible and needs no add-in or macro. It is a construction technique rather than a special Excel chart command, so it is also easy to reuse in workbooks that do not have custom chart templates.
Frequently Asked Questions
Does Excel have a built-in thermometer chart type?
Not as a standard chart category in Microsoft 365 for Windows. Create the effect with a stacked column chart using progress and remaining values, then format the series and axis.
How do I show a thermometer chart as a percentage?
Calculate actual divided by target, plot that progress with its remainder to 100%, and set the vertical axis from 0 to 1. Format the source cells or a data label as a percentage so readers can see the number as well as the visual.
Can the thermometer show more than 100%?
The example caps the chart at 100%, which keeps the column full rather than allowing it to grow beyond the target. Report the actual value separately, or design a different scale if showing overachievement is central to the message.
Why is my thermometer chart horizontal or split into separate bars?
A stacked column is vertical; a stacked bar is horizontal. If you see separate columns, switch the chart’s row and column orientation and verify that Progress and Remaining are the two stacked series for a single category.
Can I make a thermometer chart in Excel without formulas?
Yes, you can type the progress and remaining proportions directly if the value will not change. Formulas are preferable for a report that updates, because they keep the two segments aligned with the actual and target values.
Related
- Excel Strikethrough Shortcut: Quick Key and How to Use It
- Subscript in Excel: Shortcut and Formatting Steps
- Quick Forecast in Excel: Steps, Limits, and Practical Tips
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table: Rename a Calculated FieldTo rename a calculated field in an Excel pivot table, you cannot simply edit its name in the…
- Rename Data Series in an Excel Chart: A Clear GuideTo rename a data series in an Excel chart, select the chart, choose Chart Design > Select Data,…
- Clustered Column Line Chart in Excel: How to Make OneIn Excel, a clustered column line chart is a combo chart: it displays one or more data series…
- Excel Histogram by Date: Group Time into Useful BinsTo make an Excel histogram by date, select a column of real Excel dates, then choose Insert >…
- Excel Pivot Table: How to Remove Zero ValuesTo remove zero-valued results from an Excel PivotTable, open the drop-down for the row or column field, choose…
- Excel Pivot Table Sort Columns by DateYes, you can sort pivot table columns by date. The fastest way is to right-click a date column…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.