Creating a simple thermometer chart

Excel 365 2016 2013 2007 2003
You're probably familiar with a "thermometer" type display that shows the percentage of a task that's completed. It's very easy to create such a display in Excel.
The thermometer Chart Excel 2010

The trick involves creating a 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 a 10-day period. Cell C15 contains the goal value, and cell B16 contains a simple sum formula:

= SUM (B4:B13)

Cell B18 contains a formula that calculates the percent of goal:

= B16 / B15

As you enter new data in column B, the formulas display the current results.

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

   1.   Select cell B18.

Notice the blank row before cell B18. Without this blank row, Excel uses the entire data block for the chart, not just the single cell. Because B18 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:

Charts Excel 2010

Choose Clustered Column.

   3.   Remove the (x) axis and the legend.

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

Gap Excel 2010

Set the Gap width to 0.

   5.   In the popup menu of axis choose Format Axis...:

Format Axis popup Excel 2010

In the Format Axis dialog box, in the Axis Options tab, set the Minimum to 0 and the Maximum to 1:

Format Axis Excel 2010

   6.   In the popup menu select Add Data Labels....

   7.   Then in the popup menu select Format Data Label... to open the Format Data Label dialog box:

   8.   In the Format Data Label dialog box, on the Label Options tab:

  • In the Label Contains group, check Value
  • In the Label Position group, check Center
Value Excel 2010

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 get the look you desire.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more