You’ve set a goal but now it’s time to start tracking your progress against that goal. One great way to do this is by creating a thermometer chart in Microsoft Excel. This article will show you how to create a thermometer chart in Microsoft Excel 2010.
You have something you want to track over the course of a few months and want a simple, effective way of keeping everyone in the loop. What should you do? In many circumstances, a simple rising thermometer chart made in Excel will serve your needs nicely.
A thermometer chart only keeps track of a single variable, whether it is a sales goal, fundraising goal, weight loss, number of cookies sold or customers retained. The thermometer is simply displaying a percentage of the goal. For example, if your sales goal is $1,000,000 and you have already made sales of $500,000, you could have a thermometer half-full showing you are at 50% of your sales goal.
With that out of the way, let’s get down to building our thermometer in Excel 2010. If you are using Excel 2007, the process is similar. There is an excellent article on BrightHub you can look at on how to create a thermometer chart in Excel 2007.
Setting up the spreadsheet
For the purposes of illustration, I’m going to make a thermometer chart showing how far we are to meeting a sales goal. You can use your own values and goal. Keep in mind that you don’t need to use multiple columns like I am going to do. The only piece of data you need is a single value - a percentage of your goal.
- Open Excel 2010.
Column A will list out the number of months in a year (1-12). Column A will not figure into our formula, but will make it easier for the person updating the spreadsheet. Enter a label called Month in A1.
Column B will be our sales figures for each month. Enter in a few sales figures and label B1 with a label of Sales Made.
At the bottom of Column A, label the cell Total (A14). Cell A15 should be labeled Goal and A16 should be labeled Percent to Goal.
Create a simple formula for B14. It should be something like =sum(B2:B13). You should now have a total of the sales you entered into column B.
- In B15, enter a sales goal.
In B16, enter a simple formula like =B14/B15*100. This will give you a percentage to use in the thermometer chart. This is known as your “key" for the chart.
Stop for a second and make sure your table is set up properly. If you have been following along, your spreadsheet should look something like Figure 1. Next, we’ll use our percentage to create the thermometer.