- Excel Speedo charts, Excel Speedometer charts, and Excel Thermometer charts. Looking for Excel Temaples for charts & graphs?
- Here is a collection of free Excel templates. Every template has a brief description with the download link. If you want to learn more about the templates, click on the read more link. This section is updated frequently with new Excel Templates. FREE Excel Templates. Below is the category of Excel templates available for download.
- Goal Thermometer Template Excel 2013
- Excel Thermometer Chart Template Download Pdf
- Excel Thermometer Chart Template Download Free
With the use of Excel Chart Templates, making premium charts is seriously child’s play which saves on your precious time and energy. You can also see Pie Chart Templates. It helps you to focus on your data and takes away the worries from you about formatting the chart in accordance with your needs. This free excel download weight loss.
Off on holiday? Running a marathon? Building that dream house deposit? Keep track of your financial goal10 Amazingly Useful Spreadsheet Templates to Organize Your Life10 Amazingly Useful Spreadsheet Templates to Organize Your LifeIs 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 teamVisualize Your Data & Make Your Spreadsheets User Friendly With An Excel DashboardVisualize Your Data & Make Your Spreadsheets User Friendly With An Excel DashboardSometimes, 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 functions4 Excel Lookup Functions to Search Spreadsheets Efficiently4 Excel Lookup Functions to Search Spreadsheets EfficientlySearching a large Excel spreadsheet isn't always easy. Use lookup formulas to save time and search spreadsheets efficiently.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:
https://booksrenew488.weebly.com/blog/audacity-for-mac-os-x-el-capitan. 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) Www logitech com download k400r. , 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 upMini Excel Tutorial: Using Advanced Counting and Adding Functions in ExcelMini Excel Tutorial: Using Advanced Counting and Adding Functions in ExcelCounting 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 chartHow to Use an Excel Pivot Table for Data AnalysisHow to Use an Excel Pivot Table for Data AnalysisThe 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 tableMini Excel Tutorial: Use Boolean Logic to Process Complex DataMini Excel Tutorial: Use Boolean Logic to Process Complex DataLogical 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 formulasNeed Help with Excel Formulas? 7 Resources to ConsultNeed Help with Excel Formulas? 7 Resources to ConsultExcel 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.
Goal Thermometer Template Excel 2013
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. https://renewfinance476.weebly.com/blog/james-stewart-calculus-early-transcendentals-7th-edition-pdf-free-download. 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
Explore more about: Microsoft Excel, Office Templates.
- Check out this great blog! So many great topics about fundraising thermometers!insidecharity.org/2018/03/07/combine-fundraising-thermometers-and-events-to-take-donor-engagement-to-new-heights/
- Check out this great blog! So many great topics about fundraising thermometers!nonprofitsharfordcounty.com/category/articles-for-nonprofits/page/2/
- Check out this great blog! So many great topics about fundraising thermometers!trust.guidestar.org/combine-fundraising-thermometers-and-events-to-take-donor-engagement-to-new-heights
- Check out this great blog! So many great topics about fundraising thermometers!trust.guidestar.org/combine-fundraising-thermometers-and-events-to-take-donor-engagement-to-new-heights https://renewfinance476.weebly.com/blog/korean-movie-eng-sub-download. Yoo sung eun twilight download for mac.
- Check out this great blog! So many great topics about fundraising thermometers!nonprofitsharfordcounty.com/category/articles-for-nonprofits/page/2/
- Check out this great blog! So many great topics about fundraising thermometers!trust.guidestar.org/combine-fundraising-thermometers-and-events-to-take-donor-engagement-to-new-heights
- Check out this great blog! So many great topics about fundraising thermometers! The book of jubilees download.nonprofitsharfordcounty.com/category/articles-for-nonprofits/page/2/
- 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 - Download xforce keygen autocad 2014 mac. 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!
- TurboFuture»
- Computers»
Introduction
The genesis of my latest Excel 2007 article is a little unusual. I was looking at some of the searches people used to find my other Excel 2007 articles and saw a search for thermometer chart Excel 2007. I had never heard of such a chart and promptly looked it up. Curiosity well and truly tweaked, I decided it would be fun to build my own. Along the way, I learnt not only how to create what I think is a very nice thermometer chart, but also learnt some new Excel 2007 tricks along the way. As Homer said, “the Journey is its own reward”. Our goal is to create a thermometer graph as shown below, so let’s get cracking.
Creating a Thermometer Chart
- Into a cell in your spreadsheet, type in the current total raised.
- Select your cell and then
- Select Column from the Charts group on the Insert tab
- Select 2-D Column and then Stacked Chart
- Excel will create a chart similar to the one shown below
- Select Series1 (the legend) and hit Delete
- Click on the graph so that the Chart Tools tabs appear and select the Axes button from the Axes group on the Layout tab. Select Primary Horizontal Axis and thenNone
- Again on the Layout tab,selectthe Gridlines button in the Axes group. Select Primary Horizontal Gridlines and select None.
- Right click on the blue column in your graph and select Format Data Series
- On the Series Options tab, select a Gap Width of zero
- Click on the Fill tab and select Gradient fill and select Fire from the Preset colours
- Next, select Border Colour and click Solid line. Choose a colour (I used a dark red)
- Click Close to return to our graph or chart
You can see from the figure below that our chart / graph has now changed a lot
- Select the whole graph and resize it to make the column thinner and longer as shown below
- While still selecting the whole graph, right click and select Format Chart Area
- Select the Border Colour tab and selectNo line
The next step is a little fiddly. Select the numbers on the vertical axis so that your graph / chart looks similar to mine below Download aol 10.1 for mac.
- Right click and select Format Axis and then on the Line Colour tab, select No line. Keep the dialogue box open for the next step
- Now we are going to adjust the numbers on the vertical axis. Click on Axis Options. Depending on your total you may wish to adjust the Maximum and the Major unit to suit, select the Fixed button and adjust as you see fit. In my case, I changed the Maximum to 1,000,000 and left the others fixed at their default values
- Next, we need to remove the zero on the vertical axis. Once again, select the vertical axis and right click and select Format Axis. Click on the Number tab and select Custom. Type in 0;0; and click Add. Select it under Type and click Close. It should have looked like my screenshot below and the zero should disappear
There is one final change you can make if it suits your purposes. You can have the numbers of your target (the vertical axis) either on the left or the right. To do this,
- Select the graph and on the Chart Tools Layout tab, select Axes and then Primary Horizontal Axis. Select Show Right to Left Axis and the numbers will flip to the right hand side.
Now we are finished with the graph and we will begin to add shapes to make it look like a thermometer. First we will add the circle at the bottom.
- On the Insert tab, select Shapes in the Illustrations group. In the Flowchart group, there is a circle that we will use for the base of the thermometer.
- Place your circle at the base of your column and resize until it looks right.
- Right click and select Format Shape. Select the Fill tab and choose Colour. Next click on Line Colour and select the same colour for the line
Next, we are going to add a rectangle above the column (otherwise all that white space looks a little weird)
- Again on the Insert tab, select Shapes in the Illustrations group. Select a rectangle from Rectangles and draw a rectangle that extends your column upwards towards your goal.
- Right click your rectangle and select Format Shape. Select the Fill tab and choose No fill. Click on Line Colour and use the same colour as you used for the line around your column. Also, click on Line Style and change the Width to 1pt.
- To add check marks to each milestone (in my case every 100,000) click on the Insert tab, select Shapes in the Illustrations group and choose a line in the Lines group.
- Move the line to the correct position and then right click on it and select Format Shape
- Select Line Colour and select Solid line and choose white. Click Line Style and change the Width to .75 pt
- Repeat the above two steps to create as many check marks as you need
- Finally, we will create the frame for the thermometer and remove the gridlines.
- On the Insert tab, select Shapes in the Illustrations group. Select a rounded rectangle from Rectangles and draw a rectangle the covers your thermometer
- Right click your rounded rectangle and select Format Shape. Select the Fill tab and choose No fill. Click on Line Colour and select a colour that suits (I chose Orange)
- Finally turn off gridlines by clicking on the Gridlines checkbox in the Show / Hide group on the Views tab
Now we have finished our thermometer and it should look something like mine below.
Excel Thermometer Chart Template Download Pdf
Conclusion
Excel Thermometer Chart Template Download Free
I hope you enjoyed creating your thermometer chart or graph and that you also hit your target whatever it happened to be. Along the way, I also hope that you learnt a few new tricks in Excel 2007 as I did while creating mine. If you would like to know more about creating shapes or using graphs in Excel 2007 I have two additional articles, the first is on creating charts and graphs in Excel 2007 and the second is on using shapes in Excel 2007. Please feel free to leave any comments below and thanks again for reading!
This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.
No comments yet.