Creating a simple thermometer chart

Excel 365 2016 2013 2010 2007
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 2003

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 B15 contains the goal value, and cell C16 contains a simple sum formula:

= SUM (B4:B13)

Cell C18 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, click the Chart Wizard Chart Wizard button (or choose Insert -> Chart...).

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.   In the Chart Wizard dialog box, choose a Column type and the Clustered Column sub-type and then click the Next > button:

Chart Wizard Step 1 Excel 2003

For a quick preview of the chart you are creating, click Press and Hold to View Sample.

   3.   On the Step 2 of the Chart Wizard, check the data and click the Next > button.

   4.   On the Step 3 of the Chart Wizard:

  • Remove the legend in the Legend tab.
    Legend Excel 2003
  • Uncheck the Category (x) axis in the Axes tab to removes the category axis from the chart.
  • In the Data Labels tab choose the Value option.

   5.   On the Step 4 of Chart Wizard, click the Finish button.

   6.   To make the column occupy the entire width of the plot area, double-click the column to display the Format Data Series dialog box. Then select the Options tab, and change the Gap Width setting.

Gap Excel 2003

Set the Gap width to 0.

   7.   Double-click the value axis to access the Format Axis dialog box:

Format Axis Excel 2003

Set the Minimum to 0 and the Maximum to 1 in the Scale tab.

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.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.