How to Make a Thermometer Chart in Excel for a Goal

A thermometer chart in Excel shows progress towards one goal as a single column that fills up, like the fundraising thermometers outside a school or charity. Excel has no thermometer chart type, but you can build one from a normal column chart in a few minutes. This guide shows the data setup with SUM and MIN, the chart steps for Windows and Mac, and a quicker in-cell version.

When to use a thermometer chart#

Use a thermometer chart when there is one target and one running total, and the only question is "how close are we?". Typical examples:

• A fundraising or donation goal.
• A sales target for the month or quarter.
• Hours completed towards a training or volunteering goal.
• Units produced against an order size.

If you need to compare several people or months against a target, a normal column chart with a target line on the chart works better. For a dial-style display of one KPI, see the gauge chart in Excel.

Step 1: Set up the data#

A thermometer needs three numbers: what you've raised, the goal, and the percentage between them. This example tracks donations by week:

The results are:

• Raised (E2): 17,775. SUM adds the five weeks.
• % of goal (E4): 0.88875, or 88.9% formatted as a percentage.
• Remaining (E5): 2,225.

The percentage is the value you chart. If percentages are new to you, the percentages and % change lesson covers how Excel divides and formats them.

Why cap the percentage with MIN?#

MIN returns the smaller of its numbers, so =MIN(E2/E3,1) can never go above 1 (100%). Without it, raising 22,400 against a goal of 20,000 gives 112%, and the column grows out of the top of the chart. The MIN function guide covers the syntax.

You still want people to see that you beat the goal. Keep the real figure in another cell, such as =E2/E3, and use that in the label. The chart shows a full thermometer; the label says 112%. The same "cap with MIN" idea appears in KPI summaries, where progress bars shouldn't run past 100% either.

Step 2: Insert a column chart#

The thermometer starts as a clustered column chart with one bar:

1. Select the percentage cell, E4.
2. Go to Insert > Insert Column or Bar Chart > Clustered Column.
3. Check that the chart shows one bar. If Excel picked up other cells, choose Chart Design > Select Data and set the series values to =Sheet1!$E$4.

The bar will look full, because Excel scales the axis to fit the one value. The next step fixes that. If you are new to charts, Excel chart basics explains chart parts such as the axis, series and plot area.

Step 3: Turn the column into a thermometer#

Now you shape the chart. These menus are the same on Windows and Mac:

1. Fix the axis. Right-click the vertical axis, choose Format Axis, and under Bounds set Minimum to 0 and Maximum to 1. The bar now fills 88.9% of the height.
2. Widen the bar. Right-click the bar, choose Format Data Series, and set Gap Width to 0%. The bar now fills the plot area from side to side.
3. Remove clutter. Click the horizontal axis and press Delete. Do the same for the gridlines and the legend.
4. Draw the tube. Right-click the plot area, choose Format Plot Area, and give it a light grey fill and a solid border. The empty part of the tube now shows how much is left.
5. Make it tall and narrow. Drag a corner handle of the chart until it looks like a thermometer.
6. Add the bulb (optional). Choose Insert > Shapes > Oval, hold Shift while you drag to draw a circle, give it the same colour as the bar and place it under the column.
7. Add a label. Use Chart Design > Add Chart Element > Data Labels, or add a text box, click in the formula bar, type = and click the cell with the real percentage.

Format the axis numbers as percentages (Format Axis > Number) so the scale reads 0% to 100%. Because the chart points at E4, it updates every time a new week of donations goes in.

Another method: a stacked column#

A stacked column gives the same look without fixing the axis. Chart two values in one column: Raised and Remaining.

1. Select D2:E2 and D5:E5 (hold Ctrl on Windows or ⌘ on a Mac to select both).
2. Choose Insert > Insert Column or Bar Chart > Stacked Column. If you get two separate columns, click Chart Design > Switch Row/Column.
3. Colour the Raised part red and the Remaining part white with an outline.

The column's total height is always the goal, so the tube is the right size automatically. The Remaining formula uses MAX so it never goes below zero once you pass the goal.

Some people build the tube as a second series set to 100%, placed on a secondary axis with no fill. That works too; the guide to a combo chart with a secondary axis shows how to move a series to the secondary axis.

A quick in-cell thermometer with REPT#

When you don't need a real chart, a formula can draw a bar inside a cell. The REPT function repeats a character a number of times:

=REPT("|",ROUND(E4*50,0))

With E4 at 0.88875, this repeats the bar character 44 times, out of 50 for a full goal. Use a narrow font and colour the cell, and you have a progress bar that fits in a table. The guide to in-cell bars with REPT has more styles. Conditional formatting data bars (Home > Conditional Formatting > Data Bars) are another no-chart option.

Practise it#

The exercise on this page asks you to build the thermometer's value: total the donations and cap the percentage with MIN. Then practise SUM and AutoSum in a live spreadsheet and practise percentages and % change: each lesson checks your formulas instantly, so the numbers behind your charts are right.