How to Make a Thermometer Chart in Microsoft Excel

by Tricia Goss

The basic function of Microsoft Excel is to create efficient spreadsheets, but the application also allows users to make charts and graphs easily from the data in those spreadsheets. Excel offers more than a dozen types of instant charts, with even more subtypes, making it simple to create things such as pie charts and column graphs with a few clicks of the mouse. These can be further modified to create a custom chart, such as one that resembles a mercury-filled thermometer.

Step 1

Open a blank worksheet in Excel. Enter headings for two columns of data, such as "Month" and "Total Clients" in cells A1 and B1. Enter the appropriate data into the column cells. The data should be building toward a goal.

Step 2

Enter the goal number at the bottom of the column and enter the actual number reached in the cell beneath the goal amount, which will be the number in the last cell.

Step 3

Skip a cell below the totals, and enter a formula to determine the percentage reached in the next cell. In the Formula Bar, use the formula total amount/goal amount. For example, the formula could be "=B17/B16." Right-click the cell, select "Format Cells," select the "Number" tab and choose "Percentage."

Step 4

Select the cell with the percentage amount. Click the "Chart Wizard" button in Excel 2003 or earlier and select a clustered column chart. In later versions of Excel, select the "Insert" menu and choose a clustered column. In step 3 of the chart setup, go to the "Axes" tab and clear "Category (x)." On the "Legend" tab, clear "Show Legend," and on the "Data Labels" tab select "Value." Click "Finish."

Step 5

Double-click the column of the chart. The Format Data Series dialog will open. Click the "Options" tab and change the "Gap Width" to "0." Go to the "Patterns" tab and click on "Fill Effects." On the "Gradient" tab, select two colors, such as black and red, and select "Horizontal" in the shading styles. Click "OK" twice to apply changes and close the dialog.

Step 6

Drag the left side of the chart toward the right, making the graph thinner. Right-click the chart and select "Format Chart Area." On the "Patterns" tab, select "None" for both Border and Area. Click "OK" to apply the changes.

Add a circle auto shape at the bottom of the chart by selecting the autoshape from the drawing toolbar in Excel 2000 to 2003, or from the "Insert" tab in later versions of Excel. Right-click the circle and select "Format Shape." Fill the circle with the same gradient colors used in the chart.

More Articles