Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In Excel for Mac 2011 Essential Training, author Curt Frye gives a comprehensive overview of Excel, the full-featured spreadsheet software from Microsoft. The course covers key skills such as manipulating workbook and cell data, using functions, automating actions, printing worksheets, and collaborating with others. Exercise files accompany the course.
One of the most common formulas you'll create in Excel is a formula that finds the sum of a column of numbers. Excel enables you to create these formulas, called AutoSum formulas, with just a few clicks of the mouse. Creating an AutoSum formula that finds the sum of a series of values is simply a matter of clicking the cell directly below of the values and then, on the toolbar, clicking the AutoSum button. So, I will do that. I have a set of values here that I want to add, from C4 to C15. I've selected cell C16, and I'll go up to the toolbar and click the AutoSum button.
When I do, Excel creates my formula, C3:C15. Press Return, and there is my value. The problem is there is a little mistake, and it can make a very big difference, depending upon the data you're working with. Let's look at that formula again. It finds the value, as you can see here on the formula bar, the sum of all the values in C3:C15. I wanted C4:C15. Now, why did that happen? That's because the year in cell C3 is treated as a value.
When you create an AutoSum formula, you need to make sure that the only numerical values in your column are the ones that you want to summarize in your calculation. So, for example, if I want to exclude the value in C3 from this calculation, the easiest way to do it is, before I even create the formula, change it to something like CY2008, CY2009, or CY2010. Now, this AutoSum formula doesn't change. It just wrote the formula, and it's not something that keeps checking to see where the numbers are.
Instead, if I delete it and then click the AutoSum button again, Excel creates the formula properly, finding the data C4:C15. When I press Return, I get the correct value. You probably noticed that the AutoSum button has a down arrow at its right edge. You can use the AutoSum button's down arrow to create formulas using functions other than Sum. So, let's say for 2009 I wanted to find the average monthly revenue. For that, I click cell D16, and I won't have the possibility of an error, because I now have CY, or Calendar Year 2009.
When I go up to the AutoSum button, I click the down arrow and I click Average. Excel creates the formula Average D4:D15, which I can accept by pressing Return, and I see the average value. Same thing for CY2010. I can click the AutoSum button's down arrow, and I can either select any of these items, or if I want to select another function, I can go to the function library by clicking More Functions.
In this case, I will just find the minimum value, or the lowest value, by clicking min. I have Min E4:E15. Press Return, and I see that the lowest value is 200,757. Creating AutoSum formulas quickly helps you summarize your data efficiently and get on with the more demanding tasks facing you in your workbook. Don't forget that you're not limited to the Sum function. You can select any function you like from the AutoSum button's down arrow list.
Find answers to the most frequently asked questions about Excel for Mac 2011 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.