How to Build a Thermometer Chart in Excel: Step by Step

Charts & pivot tables · Updated November 20, 2026

To build a thermometer chart in Excel, put the completed amount and the amount remaining in a two-series table, insert a 2-D stacked column chart, and format the two series as a narrow vertical bar. For example, if your goal is 100 and your current result is 65, the chart stacks 65 completed with 35 remaining. You’ll need Excel for Microsoft 365 on Windows, a workbook with your values, and a clear maximum or goal for the thermometer scale.

What You’ll Need

  • Excel for Microsoft 365 on Windows. The chart-building steps are similar in Excel for Mac, although some ribbon labels and formatting panes may differ. Excel for the web has chart creation and basic formatting, but fewer fine-grained formatting controls.
  • A current value and a maximum value, such as 65 completed out of a goal of 100. The current value should be between zero and the maximum.
  • A blank worksheet area and, if desired, a label or title that explains what the chart measures.

Step-by-Step

  1. Set up the chart data. In a blank worksheet, enter Component in A1, Complete in B1, and Remaining in C1. Enter Goal in A2, the current value 65 in B2, and the maximum 100 in B5. These example values mean 65 units are complete out of a 100-unit goal. Keep the chart’s source table compact: the two series need to sit beside each other in the same row so Excel can stack them as one column.
  2. Calculate the amount remaining. With the current value in B2 and the maximum in B5, enter =B5-B2 in C2. For the example, the result is 35 because 100 minus 65 equals 35. The chart will use 65 for the completed section and 35 for the uncompleted section, making the full column represent the 100-unit goal. If you change the current value or goal later, the remaining amount recalculates.
  3. Check the inputs before charting. Confirm that the current value is not negative and does not exceed the maximum in B5. If your data represents a percentage, use consistent values: either enter 65 and 100, or enter 0.65 and 1. Do not mix those scales. You can add labels such as “Tasks completed” or “Monthly target” in a nearby cell, but keep the three-column source table itself clear and easy to select.
  4. Select the source table. Select A1:C2, including the headers and the Goal row. This tells Excel that Complete and Remaining are separate data series and that Goal is the category. Don’t include the maximum in B5: it is used to calculate the remaining amount, not as a third chart series. Including it would create an extra value and distort the chart.
  5. Insert a stacked column chart. Go to Insert > Insert Column or Bar Chart > 2-D Stacked Column. Avoid Clustered Column, which places the completed and remaining values side by side rather than stacking them. The result should be one column with a completed section and a remaining section. If Excel creates two columns or labels the chart unexpectedly, see the troubleshooting section below.
  6. Make sure Excel has the right chart orientation. Click the chart, open Chart Design, and choose Switch Row/Column if the chart does not show one stacked column. With the suggested layout, Excel should use Complete and Remaining as series and Goal as the category. The chart should show one column whose total is 100. The exact preview can vary with the worksheet’s other data and Excel’s initial chart interpretation.
  7. Choose distinct series colors. Click the completed portion of the column once to select the Complete series. Right-click it and choose Format Data Series, then use Fill & Line to apply a solid fill. Pick a strong color for progress, such as blue or green. Select the Remaining portion and give it a pale neutral fill, such as light gray. Use a clear contrast so viewers can tell the filled portion from the unfilled portion without relying on color alone.
  8. Turn the column into a thermometer stem. With either series selected, open Format Data Series and find Series Options. Set Gap Width to a low value, such as 10% or 20%, to make the column much wider. Adjust the setting until it looks like a thermometer stem without becoming so wide that it resembles a block. Since there is only one category, the gap setting controls the space around that single column.
  9. Set the scale and simplify the chart. If the vertical axis is visible, right-click it and choose Format Axis. Under Axis Options, set the minimum to 0 and the maximum to the goal, 100 in this example. This makes the scale predictable if Excel has chosen an unsuitable range. You can remove unnecessary gridlines, the legend, or the category label by selecting the item and pressing Delete. Keep the chart title or a nearby text label if the meaning is not obvious.
  10. Add a useful number label. To display the completed amount, click the completed segment and use Chart Design > Add Chart Element > Data Labels, then choose a position that is readable. You can also show a percentage in a worksheet cell beside the chart and refer to it in a separate label. Avoid overcrowding the narrow column with several labels. A title such as “65 of 100 tasks complete” is often easier to read than multiple tiny labels.
  11. Resize and test the chart. Drag a corner handle to size the chart, keeping its proportions sensible so the stem remains vertical. Change B2 from 65 to another value and check that the filled section changes while the total column remains at 100. Restore the intended value when you finish testing. If the chart will be reused, save the workbook with the data cells and chart together, and consider adding a note that identifies the goal and units.

Common Mistakes

Using a clustered chart: A clustered column chart creates two separate bars, not one thermometer. Choose 2-D Stacked Column. If the wrong chart was inserted, select it and use Chart Design > Change Chart Type to choose the stacked version.

Including the goal as another plotted value: The goal is used to calculate the remaining amount. If it is also included in the selected chart range, Excel may plot an extra series and make the result confusing. Select only the headers and the single category row, such as A1:C2 in this example.

Using inconsistent units or invalid values: A current value of 65 with a goal of 1,000 is valid only if both numbers use the same unit. A current value greater than the goal makes the remaining amount negative, which can produce a misleading chart. Check the source values and decide how to represent over-goal results before sharing the chart.

Making the stem too wide or too narrow: A large gap width can leave a thin line, while a zero gap can make the column look heavy. Adjust Gap Width in small increments and consider the chart’s final size on the sheet or in a printed report.

Relying on color alone: Pale colors may disappear when printed or viewed on a low-quality screen. Use a clear title, a numeric label where practical, and strong contrast between the two chart sections.

Tips

Use the same scale whenever you compare thermometer charts. If one chart has a goal of 100 and another has a goal of 500, similar-looking fill heights do not represent similar amounts. A shared percentage scale may be better for comparisons; alternatively, label each chart prominently with its own goal.

Keep the input cells separate from the chart formatting. That makes updates safer: change the current value or goal, and let the remaining value and chart update. For a reusable tracker, format input cells with a distinct fill color and add a short note identifying which cells someone should edit.

A thermometer chart works best for a single measure with a meaningful starting point of zero and a clear target. For several categories, a bar chart or progress-bar layout may be easier to compare. For accessibility, choose colors that remain distinct in grayscale and use descriptive chart text rather than relying on a legend alone.

On Excel for Mac, the chart types and main series formatting options are similar, but the ribbon arrangement and right-click menu may differ slightly. In Excel for the web, create the stacked column chart from the Insert tab and use the available chart formatting controls; if you cannot set a precise axis bound or gap width, open the workbook in the desktop app for those adjustments.

Troubleshooting

The chart shows two columns instead of one: Select the chart and choose Chart Design > Switch Row/Column. Check that the selected source range has the two series headers across the first row and one category in the next row. If needed, select A1:C2 again and insert a 2-D Stacked Column chart.

The remaining section is missing or negative: Check that C2 contains the remaining calculation and that the current value is lower than or equal to the maximum. For this example, a current value of 65 and a maximum of 100 should leave 35. If the current value is 100, the remaining section is zero and may not be visible; that is expected for a fully complete target.

The scale changes unexpectedly: Right-click the vertical axis, choose Format Axis, and set the bounds to the intended minimum and maximum. If you change the goal, update the maximum bound too, or return the bound to automatic so Excel can recalculate it. Remember that the plotted total must match the scale you intend to show.

The chart looks like a wide block or a thin line: Select a data series, open Format Data Series, and adjust Gap Width. Also check that the chart area is tall enough for a thermometer shape. If a narrow chart is beside other objects, increase its width or move it to a clearer part of the sheet.

Colors or labels are difficult to change: Click the chart once, then click the particular series or chart element again to select it. If the whole chart is selected, Excel applies changes at chart level rather than to the intended segment. Right-clicking the selected segment opens relevant formatting options.

Frequently Asked Questions

Can I make a thermometer chart without a formula?

Yes. You can calculate the remaining amount yourself and type it into the worksheet. A formula is more convenient because it updates when the current value or goal changes, reducing the chance that the two chart values stop adding up to the goal.

Can the chart show a percentage instead of a number?

Yes. Use a consistent percentage scale, such as 65 complete and 35 remaining for 65%, or use decimal values 0.65 and 0.35. Format labels as percentages only when the underlying values use the 0-to-1 scale; otherwise, a label may show an unintended percentage such as 6,500%.

How do I show more than one thermometer?

Create a separate stacked column for each measure, with Complete and Remaining as series for every category. That requires arranging each measure as a category and ensuring each pair adds to its own goal. If the goals differ, consider normalizing the values to percentages so the heights can be compared fairly.

Can I add a round bulb at the bottom?

You can add a circle shape below the column for a thermometer-like appearance. Use Insert > Shapes to choose an oval, then format its fill to match the completed section. The shape is decorative and does not automatically resize with the chart values, so check its position if you resize or move the chart.

Will the chart update when I change the current value?

Yes, provided the chart references the cells in the source table and the remaining value is calculated from the current value and goal. Change the input cell and confirm the chart updates. If it does not, check the selected chart data range and whether the workbook’s calculation mode is set to Automatic under Formulas > Calculation Options.

Can I print or copy the chart into a report?

Yes. Resize it so the labels remain readable, then use Excel’s normal print or copy options. Check the print preview because pale fills, small titles, or thin outlines may be hard to see on paper. A descriptive title and a clear numeric value help the chart make sense when separated from the worksheet.

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.