Off on holiday? Running a marathon? Building that dream house deposit? Keep track of your financial goal with an Excel Thermometer Chart. It is a simple, effective way to track a single variable, and one that you can share with your team, partner, friends, or just keep to yourself. Let your own progress inspire you to do more!
We’ll be building in Excel 2013, but you should be able to follow along with earlier versions without issue. This tutorial will explain how to make a basic thermometer chart with an overall percentage and overall total of the set goal. We’ll then look at how to use the SUM and SUMIFS functions to track our progress during a specific period.
Spreadsheet Setup
Before we construct our thermometer, we need to establish our goal. In this case, I’m collecting funds from around the MakeUseOf virtual office for our long-awaited team-building trip to Andorra – but you can use your own financial goals and markers.
Open Excel 2013, and open a new worksheet. For this part of the tutorial, we’ll only be using two columns: one for months, and one for the amount deposited. Mine looks like this:
You’ll note the target, total, and percentage cells underneath our table. This is where we will create the formulas for our thermometer.
In cell B20 you’ll need to write =sum(B5:B16), using the equivalent for your table position. This formula gives us the total for the numbers in column B. Once you’ve established your current total, we can work out your progress as a percentage using another very basic formula.
In cell B21 I’m going to type =B20/B19 and press enter. It will give you a decimal amount. We can easily change this to our desired format – percentage – by right clicking the cell in question, and selecting Format Cells. This should immediately open the Format Cells context box on the numbers tab. Change the category to percentage, and press OK. Your worksheet should now look similar to this:
Thermometer Setup
Now we’ve taken care of the table, total and percentage, and set our formulas up, we can focus on the thermometer.
Head to Insert > Column > 2D Column > Cluster Column. This will open an empty chart next to our table. Add data to the chart using Select Data. Select the cell containing the percentage of your total. For me, this is cell B21. Press OK to populate the chart, and you’ll arrive at this screen:
Now we can strip the chart back. Right-click the chart title, and delete. Do the same for the column title, and the horizontal lines. Double-click the y-axis (percentages) to open the dialogue box. From here you can change the minimum and maximum bounds of the chart to 0.0 and 1.0, respectively. While you’re here, scroll down and select Numbers. Change the decimal places to 0.
Right click the column and select Format Data Series. Adjust the Gap Width to 0. This will ensure your column fills the chart area, instead of trying to hide in the corner. You can now reduce the chart to a more thermometer-like size. Finally, head back to the Insert tab, select shapes, and find a nice oval. Draw an oval, and add it to the bottom of the thermometer chart, then resize the chart area. It should fit nicely around the bell of the thermometer, like so:
We’ve done it! You can change your thermometer to red by right-clicking and altering the fill-color.
Expanding your Thermometer
If you’re tracking a large amount of money over a prolonged period of time, it can be useful to look back at which days you’ve raised the most cash. This can be especially useful for charity drives – you can analyze what your team did differently on those occasions and tie it into your next fundraising event!
First, we are going to alter our data table. We need a more detailed table, including dates. We’ll also include names, because tracking our donators is fun. I’m not going to detail the transformation from small table to big table, but you should end up with a table akin to the one below. I’ll show you how to obtain this neat table format in a moment.
Remember: you need separate columns for the date and amount received. This way, we can monitor each variable. We’ll also need to set a Dynamic Named Range. Named ranges are handy for giving us power of a set of cells without having to constantly update our formulas. We can automatically ask our formula to account for any additions to our table.
Dynamic Named Range
To make things easier for us later on, I’ve turned our basic table into an official one. Do this by selecting the entire area of your table. Select the Insert tab, and you should immediately see Table. Select OK. You’ve made a searchable table, with headers. The headers will come in handy later!
Remember our Target, Total, and Percentage cells from earlier? We can now link our table to our total. In your total cell, input =SUM(Table1[Amount]). This formula asks the cell to total the Amount column. The Percentage information can still be found by dividing the total by the target, and is still linked to our thermometer.
Select the contents of your Amount column. For me, that’s C26:C38. Select the Formulas tab, and locate Name Manager. Click New. Note the Refers to box. If you had the Amount column selected, =Table1[Amount], should appear. We need to add to this formula:
OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C),1)
Each time you add a value to the Amount column, your total will automatically increase.
Your formula should be very similar to this:
=Table1[Amount],OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C),1)
You might have to adapt the column letter; here it’s C.
Adding Dates Using SUMIFS
SUMIFS is a powerful formula that lets us correlate information from two or more sources. We are going to use SUMIFS to find out how many donations we took within a 14 day period, between two specified dates. This is what the end product will look like:
Enter your required start date. For me, this is cell B10. In cell B11, type =B10+14. Excel will automatically insert the date for you, and keep updating it based upon cell B10. Adjust the time period by altering +14 to whatever number you need.
Cell B12 will contain our SUMIFS formula. In the cell, type:
=SUMIFS($C$26:$C$95,$A$26:$A$95,”>=”&$B$10,$A$26:$A$95,”<=”&$B$11)
Whoah – that’s a lot of numbers, what do they do? I’ll show you!
- $C$26:$C$95: The range of cells we want to include. I’ve asked it to include cell C95, in case our donations extend that far.
- $A$26:$A$95,”>=”&$B$10: Tells SUMIFS to check column A for any dates on or after
- $A$26:$A$95,”<=”&$B$11: Tells SUMIFS to check column A for any dates on or before
Cell B12 should now express the value of donations received between your specified dates.
Roundup
We’ve achieved some nice things today. You’ve used basic formulas, edited the chart area and axis, created tables, learned about dynamic named ranges, and had a taste of the awesome power of SUMIFS.
Though as my friend often says, SUMIFS is just a poor man’s SUMPRODUCT; but that’s another Excel story, for another day.
Tracking your charitable cause? Hopefully this has helped? What Excel formula or function would you like MakeUseOf to explain for you? Let us know below!
Image Credits: thermometer via Shutterstock