To create a thermometer goal chart in Excel for Microsoft 365 on Windows, put the current amount and the amount remaining in two series beside a goal, select the chart data, and choose Insert > Insert Column or Bar Chart > 2-D Stacked Column. Format the remaining series with no fill so the current amount appears as a rising thermometer column; a stacked bar makes the same design horizontal.
What a thermometer goal chart shows
A thermometer chart is a visual progress indicator: the filled portion represents what has been achieved, and the full height or length represents the target. It is useful for fundraising, sales targets, savings goals, project milestones, reading challenges, and other situations where people need to see progress at a glance.
Excel does not have a separate built-in chart type named “thermometer.” The usual approach is to create a stacked column or stacked bar chart from two values: the current result and the amount still needed. The two values add up to the goal, so the complete stacked bar defines the full thermometer. Making the remaining series transparent leaves the achieved portion visible while preserving the scale.
This chart communicates progress, not a forecast. If the current amount is 6,500 and the target is 10,000, the column is 65% full. It does not say whether the target will be reached on time; that requires additional information such as a deadline and the rate of progress.
Prepare the goal data
Start with a small, clearly labeled table. For example, put the chart category in column A, the achieved amount in column B, and the amount remaining in column C. Put the target in a separate cell so it is easy to change. The chart will use the first three columns; the separate target cell supports the remaining-value calculation.
- A1: Progress; B1: Current; C1: Remaining.
- A2: Goal; B2: 6500; C2: the remaining-value formula.
- E1: Goal target; E2: 10000.
With the current amount in B2 and the goal in E2, enter this in C2: =MAX(0,$E$2-B2). For this example, the result is 3,500, because 10,000 minus 6,500 is 3,500. MAX prevents a negative remaining value if the current amount exceeds the goal. If your data uses different cells, adjust the references accordingly.
Use numeric values for the chart data rather than typing currency symbols or percent signs into the cells. Apply a number format instead: select the cells, then use Home > Number to choose Currency, Accounting, Percentage, or another suitable format. This keeps the underlying values usable in formulas and charts.
Create the stacked column thermometer
- Select A1:C2, including the headers and the single row of values. Do not include the separate goal cell E2 in this selection.
- Choose Insert > Insert Column or Bar Chart > 2-D Stacked Column. Excel should create one category named Goal with Current and Remaining as the two stacked series.
- If Excel puts the data in the wrong orientation, select the chart and choose Chart Design > Switch Row/Column. The desired result is one column made of two segments, not two separate columns.
- Click the Remaining segment, right-click it, and choose Format Data Series. In the formatting pane, under Fill & Line, set Fill to No fill and Border to No line.
- Optionally remove the legend if the chart is self-explanatory: select the chart and choose Chart Design > Add Chart Element > Legend > None.
The visible Current segment will now fill 65% of the total column in this example. The transparent Remaining series still contributes its 3,500 to the stack, so it provides the unfilled part of the goal. If you accidentally delete the Remaining series rather than making it transparent, the column may fill the entire axis and stop representing progress accurately.
Format the chart so progress is easy to read
Remove visual elements that do not help explain the result, but keep enough context to make the goal clear. Select the value axis, right-click, and choose Format Axis. Under Axis Options, set Minimum to 0 and Maximum to the goal value, such as 10000. A fixed maximum makes the scale explicit and prevents a misleading view in which a small amount of progress appears nearly complete.
A fixed maximum is a manual setting: if the goal changes, update the axis maximum as well. For a chart that needs to accommodate changing goals, consider using percentage values instead. For example, chart 0.65 as achieved and 0.35 as remaining, then set the axis maximum to 1. Format the axis as Percentage. The bar will retain a 0%–100% scale, though labels will show proportions rather than the underlying currency or item count.
To make the filled portion clearer, select the Current series and choose a solid, high-contrast fill in Format Data Series. Add a concise title with Chart Design > Add Chart Element > Chart Title, or place the target in a nearby worksheet cell. To show the current amount on the column, select the Current series and choose Chart Design > Add Chart Element > Data Labels > Inside End. Check the label format and make sure it does not display an unwanted decimal or an unreadable number.
For a vertical thermometer appearance, a single narrow column often works better than a wide one. Right-click the column, choose Format Data Series, and increase Gap Width to make the column narrower. You can hide unnecessary gridlines by selecting them and pressing Delete, or use Chart Design > Add Chart Element to control chart elements. Avoid removing the value axis until you have decided how readers will understand the scale.
Make a horizontal thermometer or add a bulb
A horizontal thermometer is often easier to fit in a dashboard or beside a long label. Use the same data and select Insert > Insert Column or Bar Chart > 2-D Stacked Bar instead. Apply No fill and No line to the Remaining series, set the value axis to the goal range, and format the Current series. If the category appears in an unexpected position, use Chart Design > Switch Row/Column or adjust the category-axis options in Format Axis.
You can create a more literal thermometer look by adding a circle at the base of a vertical column. Choose Insert > Shapes, select an oval, and position it so it touches or slightly overlaps the bottom of the column. Give it the same fill color as the Current series. This circle is a separate shape, not part of the chart data. It can shift out of alignment when the chart is resized or moved, so use it only when the visual benefit is worth the extra maintenance. The stacked column alone is generally more robust.
Keep the chart accurate as values change
When the current value changes, the formula recalculates the remaining amount and the chart updates because its source cells stay the same. For example, if B2 changes from 6,500 to 7,200 while E2 remains 10,000, C2 becomes 2,800 and the filled segment represents 72% of the goal. If the target changes, the remaining formula also recalculates, but a manually fixed axis maximum must be updated to match.
Decide how to display overshooting the goal. With the MAX formula above, a current amount above the target produces zero remaining, so the stack total is the current amount and may exceed the target scale. If you prefer the thermometer to stop at 100% while showing the true amount separately, use a capped chart value and keep the actual result in a nearby cell. A capped chart is a design choice, not a change to the underlying achievement; label it clearly so readers do not mistake the capped display for the actual total.
For multiple goals, create one row per goal and consider a horizontal stacked bar chart with a separate bar for each category. Keep each row’s Current and Remaining values on the same scale if the targets are comparable. If targets vary greatly, a percentage-based chart may make relative progress easier to compare, while labels or adjacent cells retain the actual amounts. A single thermometer is best for one prominent target; multiple thermometers can become cluttered quickly.
Common problems and how to fix them
- The chart shows two columns instead of one: Excel may be treating the two series as categories. Select the chart and choose Chart Design > Switch Row/Column. Check that the headers are in B1 and C1 and the two values are in the same row.
- The whole column appears filled: Confirm that the Remaining value is positive and included in the chart range. Make the series transparent rather than deleting it. Also check that the Current amount is not equal to or greater than the target.
- The column looks almost full when progress is low: The axis may be automatically scaled to the data. Set its minimum to zero and maximum to the goal, or use a percentage-based chart with a 100% maximum.
- Changing the goal makes the chart misleading: A manually specified maximum does not automatically follow a worksheet cell. Update the axis maximum when the target changes, or choose a percentage scale.
- The label shows a long decimal or the wrong units: Format the source cells as currency, whole numbers, or percentages, and inspect the data-label number format. A chart label’s display may need its own formatting.
- The thermometer bulb moves: A drawn oval is a floating shape independent of the plotted values. Resize or reposition it with the chart, or remove it and use the simpler column design.
When another progress display is a better choice
A thermometer chart is effective when the main message is “how much of one target is complete?” It is less suitable when readers need to compare trends over time, understand several stages, or judge performance against a changing plan. A line chart can show progress by date; a standard bar chart can compare several targets; and a cell-based data bar can fit compactly in a table.
For a small in-cell indicator, select the progress cells and choose Home > Conditional Formatting > Data Bars. Data bars are quick to maintain and do not require a separate chart object, but they are not the same as a thermometer chart and may be harder to interpret unless the scale is consistent. For a formal report, a stacked chart with a clear target and labels offers more control over titles, axes, colors, and layout.
Frequently Asked Questions
Does Excel have a built-in thermometer chart?
Not as a dedicated chart type. Create the effect with a stacked column or stacked bar chart using Current and Remaining as the series, then make Remaining transparent.
How do I show the goal value at the top?
Add a chart title or place a linked worksheet label near the chart. You can also add a data label to the full stack, but Excel may label both series; a nearby cell is often the clearest way to show the target without clutter.
Can the thermometer update automatically?
Yes. If the chart refers to worksheet cells and Remaining is calculated from Current and Goal, changing those inputs updates the plotted values. A manually fixed axis maximum still needs adjustment when the goal changes.
How do I show progress as a percentage?
Use achieved and remaining fractions that add to 1, then format the value axis and labels as percentages. For a current value of 6,500 against a goal of 10,000, the portions are 65% and 35%.
Can I make the thermometer horizontal?
Yes. Insert a 2-D Stacked Bar instead of a 2-D Stacked Column, then format the Remaining series with No fill and No line. Set the value axis to the target range so the bar’s full length represents the goal.
Related
- Close Excel Shortcut Key for Windows and Mac
- Close Group Excel Shortcut: Close Workbooks and Rows
- Close Excel Shortcut: Windows and Mac Keys
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Waterfall Chart Connector Lines: How to FormatIn Excel for Microsoft 365 on Windows, select the waterfall chart’s data series, open Format Data Series, expand…
- Chart Labels in Google Sheets: Add, Edit, and FormatTo add chart labels in Google Sheets, open the chart, choose Customize in the Chart editor, then use…
- Excel Mac Chart Creation: Make and Format ChartsTo create a chart in Excel for Mac, select your data, choose Insert > Recommended Charts, select a…
- Excel Gantt Chart Template Monthly: Build One That WorksYes. You can make a monthly Gantt chart template in Excel by listing each task with its start…
- Excel Horizontal Waterfall Chart: How to Build OneExcel does not provide a horizontal orientation for its built-in Waterfall chart: the native chart displays changes as…
- Excel Pivot Table Filter After a Date: Steps and FixesTo show PivotTable records later than a chosen date, open the date field’s dropdown, choose Date Filters >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.