Chart & Visualization Issues

How to Create a Thermometer Chart with a Stacked Column in Excel

Olivia Miller Oct 10, 2026 868 views

Question details

The user wants to create a thermometer chart using a stacked column chart as an alternative to unavailable secondary axis or clustered combo chart features.

How to Create a Thermometer Chart in Excel 365 with a Stacked Column Chart

Product: Excel 365
Device & OS: not provided
Scenario: Tracking project progress or target completion using a visual thermometer-style chart.
Observed behavior: A stacked column chart successfully visualizes the completed and remaining amounts to simulate a thermometer effect without needing a secondary axis.

Before you start

Ensure your dataset clearly lists the total target value and the currently completed value so you can calculate the remaining amount before generating the chart.

Solution 1Recommended

Build a Thermometer Chart Using Calculated Stacked Columns

Calculate the completed and remaining values, then plot them on a stacked column chart to accurately create the thermometer visual.

When standard combo chart options or secondary axes are missing or complex to configure in Excel 365, a stacked column chart offers a straightforward and highly customizable alternative for building a thermometer chart.

Calculate the remaining value

Set up your data by isolating the completed amount and the remaining amount. For instance, if your total target is in B3 and your completed amount is in B2, place the completed value in cell D2 (=B2) and calculate the remaining value in D3 by subtracting the completed amount from the total (=B3-D2).

Insert a stacked column chart

Highlight the calculated values in D2 and D3. Navigate to the Insert tab on the Excel ribbon, click on the Column Chart icon, and select 'Stacked Column'.

Adjust the vertical axis

Right-click the vertical axis on your new chart and select 'Format Axis'. Set the Maximum bound to match your exact total target value (the value in cell B3) so the scale accurately reflects 100% completion.

Format to resemble a thermometer

Remove unnecessary chart elements like gridlines and the legend. Click on the 'remaining amount' data series on the chart, go to the Format tab, change its Shape Fill to 'No Fill', and give it a solid outline. Adjust the 'completed amount' series with a solid fill color (like red) to mimic thermometer fluid.

Build a Thermometer Chart Using Calculated Stacked Columns

Formatting Tip: You can overlap a separate oval shape at the base of the column chart to complete the traditional thermometer bulb look.

Create Visualizations in WPS Spreadsheet

Easily Build Thermometer Charts with WPS Spreadsheet

WPS Spreadsheet provides a comprehensive set of charting tools that make it incredibly easy to visualize target completion. You can quickly insert stacked column charts and format them into professional thermometer charts to track your data.

1. Prepare your data: Open your worksheet in WPS Spreadsheet and ensure you have calculated your completed and remaining values.
2. Insert the chart: Select the calculated data, go to the Insert tab, and choose the Stacked Column Chart option.
3. Customize the axis: Double-click the vertical axis to open the properties panel and set the Maximum value to match your total target.
4. Format the series: Adjust the fill colors of the stacked segments (making the top segment transparent with an outline) to finalize the thermometer visualization effect.

Fully compatible with Microsoft Excel (.xlsx) formulas and chartsIntuitive formatting options to easily customize chart series and axesRich library of built-in chart types for all data visualization needsFree and lightweight alternative to Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why use a stacked column chart instead of a combo chart for a thermometer chart?

If the secondary axis or clustered combo options are unavailable or behaving unexpectedly in Excel 365, a stacked column chart provides a reliable visual effect by simply stacking the completed and remaining segments to accurately reach the total target amount.

How do I make the stacked column wider to look more like a real thermometer?

Right-click the data series in the chart and select 'Format Data Series'. In the format pane, decrease the 'Gap Width' percentage to make the column wider and more prominent on the chart canvas.

How do I fix the vertical axis if the scale changes automatically?

Excel often auto-scales the axis based on data changes. To fix this, right-click the vertical axis, choose 'Format Axis', and manually type your total target amount into the 'Maximum Bound' field. This locks the axis to that specific value regardless of data updates.

Excel & Data Problems