How to How to Create Thermometer Charts in Excel in Excel - Practical Guide

Learn to create thermometer charts in Excel to visualize progress toward goals, completion percentages, or performance metrics. Thermometer charts use a vertical or horizontal bar design filled proportionally to show target achievement. This tutorial covers building them from scratch using stacked bar charts and conditional formatting, making complex progress data instantly understandable for dashboards and reports.

Why This Matters

Thermometer charts instantly communicate progress and goal achievement to stakeholders, making them invaluable for KPI dashboards, project tracking, and performance reports. They simplify complex data into visually intuitive formats that require no explanation.

Prerequisites

•Basic Excel knowledge including data entry and chart creation
•Understanding of stacked bar/column charts
•Familiarity with cell formatting and conditional formatting basics

Step-by-Step Instructions

Prepare your data

Create three columns: Goal Label, Actual Value, and Remaining Value. Enter your target in Actual Value (e.g., 750 out of 1000), then calculate Remaining as =Target-Actual in the third column.

Insert a stacked bar chart

Select your data (Actual Value and Remaining Value columns), go to Insert > Charts > Bar Chart, choose 100% Stacked Bar, and position it vertically for a thermometer effect.

Format the chart appearance

Right-click the chart, select Format Data Series, and remove gap width entirely (set to 0%). Adjust the bar width to create a tall, narrow thermometer shape.

Customize colors for clarity

Click the Actual Value series, right-click, choose Format Data Series > Fill, and select a bold color (e.g., red/green). Set the Remaining series to light gray or white for contrast.

Add labels and finishing touches

Right-click the chart, select Add Chart Element > Data Labels > Best Fit, then manually edit to show percentage or actual value. Add a title and remove gridlines via Chart Elements for a clean thermometer appearance.

Alternative Methods

Using Progress Bar shapes and conditional fill

Insert a rectangle shape, set its fill color based on actual value using VBA or manual color adjustment, then layer it with a border shape to create a thermometer effect without charts.

Horizontal thermometer variant

Follow the same stacked bar approach but select Horizontal Bar Chart instead, which creates a left-to-right thermometer ideal for space-constrained dashboards.

Using Sparklines with custom formatting

Insert a Win/Loss Sparkline showing progress, then apply custom number formatting and cell background colors to approximate a thermometer visualization.

Tips & Tricks

✓Ensure your data totals 100% by having Actual + Remaining = Target; this prevents chart distortion.
✓Use contrasting colors (e.g., bright red for progress, light gray for remaining) to make the thermometer instantly readable.
✓Add a text box with the actual percentage value above the chart for clarity on mobile/print versions.
✓Test your thermometer with different values to confirm the bar fills proportionally at all levels.

Pro Tips

★Link chart data to dynamic cells using formulas so updating one cell automatically refreshes your thermometer.
★Create multiple thermometers in a dashboard and align them vertically for easy cross-metric comparison of progress.
★Use conditional formatting on your source data cells to highlight when targets are met, adding an extra visual cue.
★Export your thermometer as a PNG for presentations by right-clicking the chart and selecting Save as Picture.

Troubleshooting

Thermometer shows inverted colors (gray filled, progress empty)

Right-click the chart, select Format Data Series, and reorder the series so Actual Value appears on top. Alternatively, swap your data column order in the source table.

Chart label overlaps or doesn't display

Go to Chart Elements > Data Labels and change position to Inside End or Outside End; manually adjust label font size via Format Data Labels > Number to reduce character count.

Thermometer looks distorted or doesn't fill properly

Verify your data: ensure Actual + Remaining = Target exactly. Check that both values are numeric (not text) by clicking Insert > Recommended Charts and reselecting the stacked bar.

Colors don't match when updating data

Right-click the series, go to Format Data Series > Fill > Solid Fill and manually reapply color; Excel may revert colors on data refresh, so consider using chart formatting templates.

Related Excel Formulas

=Target-Actual =Actual/Target*100 =IF(Actual>=Target,"Complete","In Progress")

Frequently Asked Questions

Can I create a vertical thermometer instead of horizontal?

Yes, select Column Chart instead of Bar Chart in Step 2 to create a vertical thermometer. Adjust the chart dimensions to be narrow and tall for the classic thermometer appearance.

How do I update the thermometer if the goal value changes?

Use formulas in your data cells (Actual and Remaining) that reference named cells containing your goal and actual progress. Changing those named cells automatically updates the thermometer without recreating the chart.

Can I add a target line or threshold indicator to the thermometer?

Yes, add a secondary axis with a scatter plot or line chart overlay showing the threshold value, then format it with markers at specific positions to indicate milestones or targets.

What's the best way to display multiple thermometers in a dashboard?

Align them vertically with consistent colors and sizing, use consistent axis scales (0-100%), and group them by category (Sales, Support, etc.). This allows stakeholders to compare progress instantly.

How do I export the thermometer for a presentation?

Right-click the chart, select Save as Picture, choose PNG or JPG format, and set resolution to at least 300 DPI for print quality. This preserves colors and formatting across different programs.