Creating a glossy thermometer chart

Excel 2016 2010 2007 2003
The thermometer chart is an excellent indicator of the process with well-defined measures, e.g., task status. How to create a simple thermometer chart, see Creating a simple thermometer chart. This tip will teach you how to make it more appealing for your audience.
Glossy thermometer chart Excel 365

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

Glossy thermometer chart data in Excel 365

For example, a worksheet set up to track monthly progress toward a goal: 1000 new sales in a year. Cell C15 contains the goal value, and cell C17 contains a simple sum formula:

= SUM (C3:C14)

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

= C17 / C15.

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

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

   1.   Select cell C19 if you want to create a percentage axis or select cell C17 if you want to create a volume axis (in this example, used C17).

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

   2.   On the Insert tab, in the Charts group, click the Insert Bar or Column Chart button:

Charts in Excel 365

Choose Clustered Column:

Clustered Column Charts in Excel 365

   3.   Remove the horizontal (x) axis and the title.

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

Gap Width for Data Series in Excel 365

Set the Gap Width to 0.

   5.   On the Format Axis task pane, in the Axis Options tab, set the Minimum to 0 and the Maximum to 1 (if you use a percentage axis) or to 1000 (for this example):

Format Axis in Excel 365

You can also hide the zero point in the axis (see How to hide points on the chart axis) and the axis line.

   6.   On the Insert tab, in the Illustrations group, select Shapes:

Shape in Excel 365

   7.   On the Shapes list, in the Basic Shapes group, choose Oval or Chord:

Chord in Excel 365     Oval in Excel 365

   8.   Format the shape and insert the data label (see How to insert cell content to the shape).

You can then make any other adjustments to get the look you desire.

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

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