Off on holiday? Running a marathon? Building that dream house deposit? Keep track of your financial goal 10 Amazingly Useful Spreadsheet Templates to Organize Your Life 10 Amazingly Useful Spreadsheet Templates to Organize Your Life Is your life a hotch-potch of missed deadlines, forgotten shopping and reneged commitments? Sounds like you need to get organized. Read More 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 Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Sometimes, a simple spreadsheet format isn't engaging enough to make your data accessible. A dashboard allows you to present your most important data in an easy-to-digest format. Read More , 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 Find Anything in Your Excel Spreadsheet with Lookup Functions Find Anything in Your Excel Spreadsheet with Lookup Functions In a giant Excel spreadsheet, CTRL+F will only get you so far. Be clever and let formulas do the hard work. Lookup formulas save time and are easy to apply. Read More 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 Mini Excel Tutorial: Using Advanced Counting and Adding Functions in Excel Mini Excel Tutorial: Using Advanced Counting and Adding Functions in Excel Counting and adding formulas may appear mundane compared to more advanced Excel formulas. But they can help you save a lot of time when you need to collect information about the data in your spreadsheet. Read More , 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 How to Use an Excel Pivot Table for Data Analysis How to Use an Excel Pivot Table for Data Analysis The pivot table is one of the single most powerful tools in the Excel 2013 repertoire. It is frequently used for large data analysis. Follow our step-by-step demonstration to learn all about it. Read More 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 Mini Excel Tutorial: Use Boolean Logic to Process Complex Data Mini Excel Tutorial: Use Boolean Logic to Process Complex Data Logical operators IF, NOT, AND, and OR, can help you get from Excel newbie to power user. We explain the basics of each function and demonstrate how you can use them for maximum results. Read More . 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 Need Help with Excel Formulas? 7 Resources to Consult Need Help with Excel Formulas? 7 Resources to Consult Excel is the spreadsheet gold standard. If you are required to use Excel and yet have to familiarize yourself with it, these resources will quickly introduce you to the basics and more. Read More . 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
Dear Mr. Phillips,
I appreciate that you put this out there for us. I just downloaded excel a few minutes ago to see if I could create a fundraising thermometer with a car outline. I am tired and lost and don't even know for sure if I can put it in facebook. I'm "wasting" so much time trying to make widgets, infographs and such that I'm not focusing on the actual fundraiser :(
If you have a minute to respond, would you please tell me if what I want to do is feasible? I don't even know if this is an apt question. Thank you for your time, Gale Sue
Really helpful... I only wish that the veil could be completely ripped away so that I could behold that glorious mystery that is the power of excel... but alas only bit by bit. Thank you for this tutorial... Made my own thermometer for a Water Tank Project in Kalimpong West Bengal India. Thanks again.
Very neat and simple, as any other genius idea... Thank you!
Very helpful! Thank you!
Learn something everything day... Thanks!
No problem - thanks for reading!