Yes. In Excel for Microsoft 365 on Windows, create a thermometer-style temperature gauge by charting the measured temperature and the unused part of a fixed scale as a 2-D Stacked Column chart, then format it as one vertical bar. Excel does not have a dedicated chart type called “temperature gauge”; this visual is assembled from a standard chart and a small data table.
What a temperature gauge chart shows
A thermometer-style gauge represents one current reading against a defined range. If the scale runs from 0°F to 100°F and the reading is 68°F, the colored portion fills 68 units and the remaining portion fills 32. The two stacked sections together make a column 100 units high, so the chart’s full height represents the chosen maximum.
This is useful for a dashboard tile, a room-temperature display, or a single reading from a process. It is not a measuring instrument: Excel only visualizes the value already in a cell. It also does not show change over time particularly well. If you need to see temperature trends, a line chart is generally more informative.
The main design choice is the scale. A gauge starting at zero is straightforward for a positive range such as 0–100°F. It can be misleading for weather or other readings that may be below zero, because a zero-based column cannot show a negative value as a normal filled thermometer. For a range that crosses zero, choose a chart designed to show positive and negative values, or use an offset gauge and label its endpoints clearly.
Set up the data and scale
First make a compact chart table, with the temperature and the unfilled remainder as two series. For a simple 0–100°F example, put the following headings and values in the worksheet:
- A1: Category; B1: Temperature; C1: Remaining
- A2: Reading; E1: Actual °F; E2: 68
- F1: Scale maximum °F; F2: 100
Leave the chart table’s B2 and C2 ready for formulas. With the actual reading in E2 and the maximum in F2, put =MIN(MAX($E$2,0),$F$2) in B2. It returns 68 for this example and prevents a negative input or a value above the scale maximum from making the filled part fall outside the intended range.
With the same cell layout and B2 holding the capped reading, enter =MAX(0,$F$2-B2) in C2. It returns 32, so B2 plus C2 equals the 100-degree scale maximum. If the input changes, the two chart values recalculate automatically. Keep the input, maximum, and chart table visibly labeled; a gauge without units or a stated range can be easy to misread.
Insert the stacked column chart
- Select A1:C2, including the headings.
- On the ribbon, choose Insert > Insert Column or Bar Chart > 2-D Stacked Column.
- If Excel plots the data in an unexpected orientation, select the chart and use Chart Design > Switch Row/Column. The desired result is one category containing two stacked series, not two separate columns.
- Move the chart near the input cells or to a dashboard sheet. If it will be copied elsewhere, keep the source cells available or put the chart table alongside the dashboard data.
The chart will initially look more like an ordinary stacked column than a thermometer. That is expected: the colored temperature series forms the filled part, while the remaining series forms the empty part above it. Formatting the axis and column spacing makes the gauge shape clearer.
Format the axis and thermometer shape
Right-click the vertical value axis and choose Format Axis. In Axis Options, set the minimum bound to 0 and the maximum bound to 100 for the example. The exact bounds should match the scale maximum in the worksheet. Setting them explicitly matters: automatic axis scaling can make the bar appear nearly full even when the reading is low, or can change the visual as data changes.
Choose a major unit that gives useful reference marks, such as 10 or 20 degrees on a 0–100 scale. Keep the axis labels if readers need to estimate values from the chart. If the exact number will be shown in a prominent cell or data label, you can hide the axis labels for a cleaner dashboard, but retain some indication of the scale maximum and units.
Click a column, then right-click and choose Format Data Series. Reduce Gap Width to make the column wider; a value around 30% is a reasonable starting point, though the right setting depends on the chart’s dimensions. Format the Temperature series with a strong fill color, and give Remaining a pale neutral fill. Remove the chart legend if the two series are self-explanatory or if you add a clear label elsewhere. A chart title such as “Current temperature (°F)” communicates both the metric and unit.
A stacked column has a flat base rather than a rounded thermometer bulb. If that appearance matters, you can add an oval from Insert > Shapes and give it the same fill as the Temperature series, then position it at the column’s base. Treat the oval as decoration, not data: it may need repositioning when the chart is resized, and it does not change what the chart measures.
Show the reading, units, and updates
A precise reading is often more useful than estimating from the axis. To show the value on the column, select the Temperature series and choose Chart Design > Add Chart Element > Data Labels. Then use Format Data Labels to choose a placement that does not obscure the bar. If the default label shows an unhelpful series name or category, adjust the label options in the Format Data Labels pane.
Another option is to display the input value in a nearby worksheet cell with a unit, such as “68°F,” and keep the chart itself uncluttered. If you need a formatted label built from a numeric value, keep the numeric input in its own cell for calculations and use a separate display cell. This separation prevents a value typed as text with a degree symbol from being mistaken for a number in later calculations.
When a sensor feed or another formula updates E2, the chart changes because its series values refer to B2 and C2. If the maximum changes, update F2 and the axis bound as well; the formulas recalculate the remainder, but a manually fixed axis bound does not automatically follow the cell. For a dashboard where the scale maximum varies, consider a fixed, meaningful scale or review the axis setting whenever the maximum changes.
Handling Celsius, Fahrenheit, and below-zero values
Choose one unit system for the gauge and make it explicit in the title, axis, or nearby label. Do not mix a Celsius input with a Fahrenheit axis. If a conversion is needed, calculate it in a separate cell and chart the converted result. With a Celsius reading in A2, the formula =A2*9/5+32 converts 20°C to 68°F. The formula assumes A2 contains a numeric Celsius value; it does not convert text such as “20 C.”
For temperatures below zero, a conventional thermometer-style fill from zero cannot depict the reading correctly. One approach is an offset scale: set a lower bound and upper bound, and make the filled portion represent the distance from the lower bound rather than the temperature itself. For example, on a -20°C to 40°C range, a reading of -5°C is 15 degrees above the lower endpoint. Label the endpoint values and display the actual reading separately, because the bar’s internal chart value is an offset, not the temperature. If viewers need to compare negative and positive readings at a glance, a line chart with a visible zero line is often clearer.
Common problems and practical fixes
- Two columns appear instead of one: Excel may be treating the series as categories. Use Chart Design > Switch Row/Column and confirm that the chart table has one category row and two value series.
- The bar is full or nearly full unexpectedly: Check that the temperature and remainder add to the intended scale maximum, and that the vertical axis bounds are set to the same scale.
- The colored part exceeds the scale: Check the input and formula references. The capped formula above limits the plotted temperature to the 0-to-maximum range; also confirm the axis maximum is not lower than the chart data total.
- The gauge is hard to read: Add units, use a restrained contrast between the two fills, reduce unnecessary gridlines, and show the exact reading as a label or nearby value.
- The chart stops matching a changed scale: A manually set maximum axis bound does not necessarily track a changed maximum cell. Update both, or use a stable scale that makes readings comparable over time.
- The chart shows blanks or errors: Check that the input is numeric and that formula references point to the intended cells. A text entry such as “68 degrees” is not the same as the number 68.
When to use another Excel visual
A thermometer gauge is best when one current value needs a quick comparison with a fixed limit. It uses space efficiently, but its single vertical bar is not ideal for several readings, a long history, or a range with meaningful negative values.
- For a temperature history: Use a line chart with dates or times on the horizontal axis. It makes rises, falls, and patterns easier to see.
- For several locations: Use a line chart or a clustered column chart so readers can compare the locations directly. One thermometer per location can take up space and make comparisons awkward.
- For a small in-cell indicator: Home > Conditional Formatting > Data Bars can add a bar to a cell. Data bars are compact, but they do not provide the same dedicated axis and thermometer appearance.
- For a decorative dashboard dial: A doughnut-based gauge is possible, but it takes more setup and can make values harder to compare accurately than a simple bar.
These are presentation choices, not different ways to measure temperature. Use a chart type that matches the question: a gauge answers “How close is this reading to a chosen limit?”, while a trend chart answers “How has it changed?”
Frequently Asked Questions
Does Excel have a built-in thermometer chart?
Not as a dedicated chart type in Microsoft 365 for Windows. The common thermometer-style display is made from a 2-D Stacked Column chart with a value series and a remaining-to-maximum series.
Can the temperature gauge update automatically?
Yes, if the chart uses formula cells linked to the input and maximum. The plotted values recalculate when the input changes. A manually configured axis maximum may still need to be changed if the scale maximum changes.
How do I show a temperature in Celsius instead of Fahrenheit?
Use Celsius values throughout the data, set the chart scale in Celsius, and label the chart or axis with °C. If converting from Fahrenheit, calculate the converted number in a separate cell before charting it; do not label an unchanged Fahrenheit value as Celsius.
Can a thermometer chart display a negative temperature?
A basic zero-based thermometer gauge cannot show a negative reading correctly as a filled column. Use a clearly labeled offset scale or choose a chart with a zero line, such as a line chart, when below-zero comparisons matter.
Why does my gauge look different after changing the data?
Excel may rescale an axis that is set to automatic, or the source values may no longer total the intended maximum. Set axis bounds deliberately, check the remaining-value formula, and confirm the chart still points to the correct cells.
Related
- QGIS Copy Attribute Table to Excel: Steps & Tips
- Project Timeline in Microsoft Excel: Build and Manage It
- Project Timeline Excel Template: Simple Planning Guide
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Refresh a Visio Org Chart from ExcelIf your Visio org chart is linked to an Excel workbook, open the diagram in Visio for Windows…
- Excel for Mac Spiderweb Graph: Create and Format ItYes. In Excel for Mac, a spiderweb graph is called a Radar chart: arrange your categories and values…
- Excel Pivot Table: Group Dates by Year, Quarter & MonthYes. In Excel for Microsoft 365 on Windows, put a real date field in a PivotTable, right-click one…
- How to Make a Large Org Chart in PowerPointTo make a large org chart in PowerPoint, first organize the reporting structure, then use Insert > SmartArt…
- Excel Pivot Table Not Showing Dates Correctly?If an Excel PivotTable is showing dates as numbers, text, individual days, or unexpected months, first check that…
- Excel Pivot Chart Colors Keep Changing? Fix the CauseExcel PivotChart colors usually change because filtering, refreshing, or rearranging fields changes which series are displayed or their…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.