Creating a simple thermometer chart

Excel 365 2013 2010 2007 2003
A thermometer chart is a kind of progress chart that shows the current completed percentage of the task or the value of any metric relative to the predefined value. E.g., the thermometer scale can display the sales plan status or client satisfaction rate.
The thermometer Chart Excel 2016

The trick involves creating a clustered column chart that uses a single cell (which holds a percentage value) as a data series.

For example, a worksheet set up to track daily progress toward a goal: 1,000 new sales in 10 days. Cell C16 contains the goal value for the thermometer chart, and cell C17 contains a simple sum formula:

= SUM (C5:C14)

Cell C19 contains a formula that calculates the percent of goal completed for the thermometer lower bar:

= C17 / C16

To create the chart like this one, do the following:

   1.   Select cell C19.

Notice the blank cell before cell C19. Without this blank row, Excel uses the entire data block for the chart, not just a single cell. Because C19 is isolated from the other data, the Chart Wizard uses only the single cell.

   2.   On the Insert tab, in the Charts group, choose the Column button:

Column Charts in Excel 2016

Choose the Clustered Column Clustered Column Charts in Excel 2016 chart.

   3.   Remove the horizontal (x) axis.

   4.   In the thermometer chart, the column width is equal to the chart width. To make the column occupy the entire width of the plot area, double-click the column to display the Format Data Point task pane (or choose it in the popup menu). Then in the Series Options tab, change the Gap Width setting:

Gap width for chart in Excel 2016

Set the Gap Width to 0.

   5.   Right-click the vertical axis and choose Format Axis... in the popup menu (or double-click the axis):

Format Axis popup in Excel 2016

On the Format Axis task pane, in the Axis Options tab, set the Minimum value to 0 and the Maximum value to 1:

Format Axis in Excel 2016

   6.   In the popup menu, select Add Data Labels and again Add Data Labels:

Add Data Label in Excel 2016

   7.   Then in the popup menu, select Format Data Labels... in the popup menu:

Format Data Labels in popup menu Excel 2016

   8.   On the Format Data Labels task pane, on the Label Options tab:

  • In the Label Contains group, check Value,
  • In the Label Position group, check Center:
Format Data Label in Excel 2016

You can also change the label format on the Number tab (for more details see Conditional formatting of chart axes). Make any other adjustments to make your thermometer chart more appealing to the audience.

See also this tip in French: Comment créer un graphique simple de thermomètre.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.