How to Create and Customise a Thermometer Progress Chart in Microsoft Excel

When presenting progress towards a crucial business benchmark—such as a fundraising campaign, annual sales quota, or project milestone—executive dashboards require clear, intuitive visualisations that communicate performance at a glance. Standard horizontal progress bars and pie charts can become repetitive and fail to evoke the visual urgency needed in milestone presentations. A classic thermometer chart provides an engaging, instantly recognisable visual representation of percentage completion towards a fixed target.

While Microsoft Excel does not offer a standalone “thermometer” chart icon in its default chart gallery, you can easily build one by configuring an overlapping clustered column chart, customising axis scales, and formatting fill transparency. In this comprehensive tutorial, you will learn step by step how to structure your benchmark data, construct a dynamic thermometer chart, style the mercury tube and bulb, and automate live percentage updates in Microsoft Excel.

Understanding the Mechanics of a Thermometer Chart

A thermometer progress chart consists of two graphical components layered directly on top of each other:

• The Target Tube (The Background Container): A vertical column representing the total goal (typically 100% or a fixed monetary sum like £50,000). It features a subtle outline with a transparent or light background fill to depict the glass thermometer casing.
• The Mercury Level (The Actual Progress): A narrower or perfectly overlapping column representing current achievement (e.g. 72% or £36,000). It is styled with a vibrant fill colour (such as bold crimson red, electric blue, or emerald green).
• The Bulb Base: An optional circular shape or formatted marker attached to the bottom of the column to complete the authentic thermometer silhouette.

As team members log new contributions or sales figures into your worksheet, the mercury column rises automatically inside the glass tube.

Step 1: Structuring Your Benchmark Data

To construct a thermometer chart, set up a simple data table in your Excel worksheet. Enter the following structure across cells A1:B3:

Metric (Column A)	Value (Column B)
Actual Progress	£36,000 (or 72%)
Target Goal	£50,000 (or 100%)

If you prefer tracking percentages directly, enter 72% in cell B2 and 100% in cell B3. Ensure cell B2 is formula-driven (for example, =SUM(Sales_Range) or =Current_Funding / Goal) so the thermometer dynamically responds to new data.

Step 2: Inserting a 2D Clustered Column Chart

Once your data table is prepared:

1. Select the data range A1:B3.
2. Navigate to the ribbon and select the Insert tab.
3. In the Charts group, click Insert Column or Bar Chart and choose the first option under 2D: Clustered Column.
4. By default, Excel places the two values side by side in two separate columns. If Excel graphs your data across two horizontal category ticks, click on the chart, go to the Chart Design tab on the ribbon, and click Switch Row / Column.

Both columns should now be grouped together under a single category tick.

Step 3: Overlapping the Progress and Target Columns

To create the thermometer effect, the Actual column must sit directly inside the Target column:

1. Right-click either of the columns inside the chart and select Format Data Series. A configuration panel will dock on the right side of Excel.
2. Under the Series Options tab (indicated by the three bar icons), locate the slider for Series Overlap.
3. Adjust Series Overlap to exactly 100%. Both columns will now occupy the identical horizontal position.
4. Adjust the Gap Width slider to approximately 250% to 300%. Increasing the gap width narrows the column, creating a slender, elegant thermometer stem.

If the Target column completely conceals the Actual column, right-click the chart and choose Select Data. In the Legend Entries (Series) box, use the Move Up / Move Down arrows to ensure the Target series is listed first, allowing the Actual progress series to render on top in the foreground.

Step 4: Customising the Vertical Value Axis

To prevent the chart’s scale from jumping unexpectedly when values change, lock the vertical axis boundaries:

1. Right-click the vertical axis (numbers along the left side) and select Format Axis.
2. Under Axis Options, locate the Bounds settings:
   • Set Minimum to 0.0.
   • Set Maximum to your exact target (e.g. 1.0 for 100% or 50000 for a £50,000 financial ceiling).
3. Under Units, set the Major tick interval to 0.1 (10%) or 0.2 (20%) for clean increment markings.
4. Under the Tick Marks section, set Major type to Outside to simulate thermometer calibration hashes.

Step 5: Formatting the Mercury Tube and Bulb

Now apply distinct styling to create the physical thermometer appearance:

1. Styling the Target (Glass Casing):

• Click once on the outer Target column to select it.
• In the Format Data Series pane, select Fill & Line (the paint bucket icon).
• Set Fill to No fill (or a very soft light grey #f1f5f9).
• Set Border to Solid line, choose a dark grey colour (e.g., #475569), and set the border width to 1.5 pt.

2. Styling the Actual Progress (Mercury Column):

• Click directly on the inner Actual column.
• Under Fill & Line, set Fill to Solid fill and choose a vibrant crimson red (e.g. #dc2626) or corporate theme colour.
• Set Border to No line.

3. Adding the Thermometer Bulb Base:

• Go to Insert > Shapes and select the Oval shape.
• Hold down the Shift key while dragging to draw a perfect circle directly beneath the base of the column.
• In the Shape Format tab, set the Shape Fill to the exact red colour used for the mercury column, and set the Shape Outline to match the dark border of the glass tube.
• Group the circle and chart together by holding Ctrl, selecting both objects, right-clicking, and selecting Group > Group.

Step 6: Adding a Dynamic Progress Label

To display the exact percentage clearly on the thermometer:

1. Click on the red Actual column.
2. Click the Chart Elements icon (the green plus + sign) at the top-right corner of the chart and check Data Labels.
3. Click on the newly created label, right-click, and select Format Data Labels.
4. Set the Label Position to Inside Base or Center.
5. In the font settings on the Home ribbon, format the label text as bold, white, and centered for maximum contrast against the red mercury fill.

Troubleshooting Common Thermometer Chart Issues

• Actual Column Completely Hidden: If your red progress column disappears behind the target casing, the series plotting order is inverted. Right-click the chart, choose Select Data, highlight the Actual series, and use the move arrows to place it after the Target series.
• Axis Automatically Rescaling Beyond 100%: If achieving 105% of goal causes Excel to stretch the axis to 120%, reopen Format Axis and ensure the Maximum Bound is explicitly set to a fixed numeric constant rather than remaining on Auto.
• Horizontal Category Label Clutter: If text labels crowd the bottom of your thermometer, click on the horizontal category axis at the base of the column and press Delete on your keyboard to clear it cleanly.

Frequently Asked Questions

Can I build multiple thermometer charts side by side for team benchmarks?

Yes. You can plot multiple team metrics in a single worksheet, copy your completed thermometer chart layout, and link each chart’s data range to individual team performance figures.

Does a thermometer chart update automatically when data changes?

Yes. Because the chart references live spreadsheet cells, any change in underlying ledger figures, donations, or formula outputs causes the red mercury column and data label to adjust immediately.

Can I use conditional formatting to change mercury colours automatically?

While native Excel charts do not apply worksheet conditional formatting rules directly to series fills, you can use a separate formula column that routes values into different series (e.g. Red for under 50%, Yellow for 50-80%, Green for 80%+) to trigger dynamic colour shifts.

Can I save my custom thermometer chart as a reusable Excel chart template?

Yes. Once your thermometer styling is perfected, right-click the chart area and select Save as Template. You can then apply this custom layout to future datasets with a single click from the Templates tab in the Insert Chart window.