Hi. In this tutorial we'll go over how to make a goal thermometer in Excel by creating a thermometer to track total dollars against a
goal. You can use a goal thermometer to track goals like paying off debt, donations for
fundraising, weight loss tracking and other goals that can be quantified.
So the example that we see here is that of debt being paid off over the course of a year. Each month the debt is reduced by a different
amount. And, while we can look at the data and read the progress being made, we can create a goal thermometer and it'll help make
this a little more fun and little bit more visually interesting.
To create a thermometer that tracks how many dollars have been put towards the debt,
we'll need to summarize this information down to just 2 numbers - 1. We'll need a goal. This will be the total amount of debt to pay off. And
2. The progress so far. This will be the total of what we've already paid towards the debt. And this is what you see up here on the top left.
So, highlighting these two numbers, we'll need to create a chart.
So we'll go up to the Insert tab and, under Charts,
we'll go to All Charts and make sure you're in Columns and you're going to choose Stacked Column. Now, were ultimately going to create
what you see in the preview here to the right, but for now, I'm going to choose the one on the left because some versions of Excel default to
this. However, if you have this version and you see this preview, please go ahead and choose the
one on the right and will catch up if you with you in a second. So, once you've chosen your version,
click OK and then we'll need to move the chart so it's not covering
our data.
Once you've created a chart, the Chart Tools menu appears. This menu is only available when your chart is selected. Deselect the
chart the area by moving your cursor and clicking anywhere out of it and the Chart Tools menu disappears.
So, to go on, we'll click back on our chart.
So, under the Chart Tools and Design tab, click on the Switch Row / Column.
So this will stack your columns together creating that other chart that we saw in the preview a moment ago.
So now we have some things to fix on our chart. So, notice that, if I select this bottom section, it corresponds with the goal amount.
And this top section corresponds with the amount paid so far. Well, we need to switch
these so that as we continue to pay more, we see that column rise.
So to switch these, we're going to come up here to the Chart Tools / Design tab and click on Select Data. And you see that these labels
are listed on the left under Legend Entries. And we just need to switch their order. So, here on these arrows, the Move Down and Move Up,
just move them.
Click OK on this now restacks our data so that the Amount Paid So Far is at the bottom
and our Goal is at the top.
Next, we need to update the axis. To do that, left click on the axis to select it and then right click and choose Format Axis. When your
options pop up, under Bounds, make sure your minimum is set to zero
and change the maximum to 10,000. The reason for this is that 10,000 is our goal and that's as high as we need for this thermometer
to go. So you'll click Enter. We'll exit out of this. And you see that now your axis
is ranging from $0.00 at the bottom up to 10,000 at the top.
Next, we can update the color of our thermometer. I'm going to make this thermometer red so that it resembles a
medical thermometer. So we'll first click on the bottom number, in the bottom series, and we'll change this fill to a solid red, and
make sure that we have a red outline. And then the top number, our goal, we actually want that to have no color so it looks like there's
nothing there as the temperature rises. So we're going to change the fill to have No Fill and then we're going to make sure,
double sure that we have a red outline. That way it looks like the border of the thermometer once we're done.
So, to make our thermometer thinner, you'll want to click again on your data series - I've selected the bottom, but you can just as easily
select the top one - and right click and choose Format Data Series. And this brings up your Series Options. Now, the Gap Width increases
the gap between columns, but in
doing so it also makes each column thinner. And, since we only have the one column, it'll just
make it look a little bit thinner. So, we'll change this from 150% to 350%
and click Enter. And you see that now our thermometer is looking a little bit thinner.
So, back in our Chart Tools menu, this time we'll want to choose the Format tab. And here you'll find a section labeled Insert Shapes.
So click on the Oval and when you do that and you come down
here you see that now your cursor has changed shape and it looks a little bit like a +.
So now you're ready to draw an oval. So what we'll do is we'll just come to the bottom of
our chart and we'll draw an oval. And make sure it's somewhat centered. And then, once we do this, we can come in here and recolor
the oval. So, the Shape Fill, we'll choose the same red that we chose for
our Total Paid So Far and then I'm actually going to just get rid of the outline.
and you see that now
we've created a thermometer using Excel.
So to clean up the thermometer a little bit, I'm going to get rid of these grid lines. So selecting any of them,
right click and Format Grid Lines and, under Line, choose No Line
and this gives us a little bit more open space around the thermometer. Now to test this - this is tracking the Total Paid So Far. If I change
this number, the $3,600 to, say, $7,000, you see that the temperature (or the amount paid) also rises.
So we'll undo that.
So, lastly, if you want to print this thermometer, or if you want to just have this be in its own worksheet, you can highlight / select your chart
area - where only the outside borders are selected,
and right click and choose Move Chart. And you get these options
to put it into a different Object or add it to a New Sheet. So we're going to create a new sheet and click OK.
and this gives it its own, um, worksheet formatted just for the chart. Now, we'll do just some slight fixing here of the bulb. We'll center
that again. And now this should be perfectly sized for printing. So, if we go under the File Menu
and Print, you see that this takes up a full page and is ready to be printed.
So, I hope you found this tutorial helpful to learn how to create a goal thermometer using Excel. If you enjoyed it, please make sure to
give it a Like and don't forget to subscribe to this channel. Thanks!