Creating a glossy thermometer chart

Thermometer chart is nice indicator of the process with well-defined measure, e.g. task status. How to create simple thermometer chart, see Creating a simple thermometer chart. This tip will teach you how make it more appealing for your audience.
Glossy thermometer chart Excel 2003

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 2003

For example, a worksheet set up to track monthly progress toward a goal: 1,000 new sales in a year period. Cell B16 contains the goal value, and cell B18 contains a simple sum formula:


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


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 in 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 or change the chart title on the Titles tab
  • Uncheck the Category (x) axis on the Axes tab to removes the category axis from the chart.
  • Remove Major gridlines on the Gridlines tab
  • Remove the legend on the Legend tab.
    Legend in Excel 2003

    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 in Excel 2003

Set the Gap width to 0.

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

In the Format Axis dialog box, on the Scale 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 2003

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

    8.    Click the AutoShapes AutoShapes button (or choose Insert -> Picture -> AutoShapes).

    9.    On the AutoShapes toolbar, in the Basic Shapes group, choose Oval:

Oval in Excel 2003

    10.    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.

Thank you for visiting OfficeToolTips

We are glad to help you in your work. However, you are using ad blocker and our efforts will not be rewarded.

Please consider disabling ad blocker before continuing the reading.