Creating a Thermometer Chart in Excel 2007

click to enlarge

For example, a charity organization may have a particular yearly goal that it is trying to achieve in member donations. A thermometer chart can be used to keep track of the donations received throughout the year so that, at any point in time, someone can look at the chart and see what percent of that goal has been met. In fact, we'll use this very same scenario as an example for describing how to create a thermometer chart in Excel 2007.

For our example, we'll use a simple table that has been entered in Excel that includes the charity's yearly donation goal and fields in which to input each month's donations in dollars. This table is shown in the screenshot below. (Click any image for a larger view.)

Most of the fields in this table are fixed, but there are a couple that contain formulas – namely cell B15 which contains the Year to Date Contributions in dollars and cell B16 which contains the Percent of Goal. The formula we are using in cell B15 is =SUM(B2:B13) so that all months, January through December, are included in the total, even if no values have been input for the month yet. For the Percent of Goal, in cell B16, we are using the formula =B15/B14 and formatting that result as a percent.

Step 1: To begin, we want to insert a "blank" column chart. We'll do this by selecting any blank cell in the spreadsheet and opening the Insert tab on the Excel ribbon. Then, in the Charts group on the ribbon, click on Column and choose the first column chart type under 2-D Column.

You should then see a blank chart area in your spreadsheet as in the screenshot below.

Step 2: Right-click anywhere over the blank chart area and choose Select Data.

This will open up the Select Data Source window shown below. Click on the Add button in the Legend Entries (series) portion of this window.

Now, a new window entitled Edit Series will appear. With the cursor in the field under Series name, click on cell A16 – the cell that contains the text Percent of Goal. Then, tab down to the Series values field and click on cell B16 – the cell that contains the actual percentage value.

Click OK to return to the Select Data Source window, and then click OK again to go back to your Excel chart. We've still got some work to do, but at this point your chart should appear as in the screenshot below.

Step 3: The next thing we want to do is get rid of the legend to the right of the chart and the 1 at the bottom of the column that is acting as a horizontal axis label. To do this, right-click on each item individually and select Delete.

Here's what our chart looks like now.

Continue on to the next page for the remainder of this tutorial on how to create a thermometer chart in Excel 2007.