Transcript
Excel’s Autosum functions help you to automate your calculations. when it comes to coming up with answers for complex tables like this, well, this is not too complex, but it's definitely a step up from the basic calculations.
What are the Autosum functions?
Well, they are the five most commonly used functions in Excel. There are two places you can go to access the autosum functions. First, you can go over to the home tab and then when you click on the home tab you'll head all the way over to the right and you'll go to the editing group. in the editing group, you'll see this Greek symbol with Auto sum. Now if you're just looking to sum values you can simply click on autosum. if you want to find the other functions that are in the package of Auto sum functions just click the little drop-down arrow and you'll see average, count numbers, Max, and min. you can also access more functions, but these are the five most commonly used ones.
The other place that you can go to is the formulas tab. If I go to the formulas tab this time over to the left, right after the insert function command, is Auto sum and just like with Auto sum functions on the Home tab I can click this drop-down triangle and access the other Auto sum functions available there.
SUM
Now let's talk about this table of information. We have 12 students who have taken five tests. I'm gonna pretend that one of those students is Alex and he's been bugging me to get his test scores because he wants to apply to college. I've been a little slow in getting him his scores so I am now going to focus on that. I'm gonna head over to the first cell underneath the total header and I want to come up with the total of Alex's scores. Now I could type equal and then type sum open parenthesis then I could take my mouse and select all the values that I want to sum. then I can put in the closed parenthesis and press enter this will give me the total, but it takes some time. The autosum functions are gonna automate this for me because if I want to get the same answer all it takes is one click and one press of a keyboard key. The one-click is a click on autosum and then the keyboard key I'm gonna press is enter and now I have the total much quicker than the prior method.
Average
Now I want to get the average so I'm gonna head over to the average since this worked so well for summing values let me click the drop-down and select the appropriate function, average. I'll click and then press ENTER and all of a sudden I have the average just like that. I can't wait to use this in my calculations except I have the wrong answer here. I have the wrong answer because what I'm doing is also known as going on automatic pilot. I'm making the assumption that what I did last time will work in this situation but let's replay that. I'll press Ctrl Z to undo. when I click the drop-down and then when I select average I should take note of what is currently being selected. Excel is not only selecting the 5 test scores but it's also selecting the total. What I usually say when this occurs is that Excel is very good at finding numbers but it's not really good at reading headers so it doesn't know the difference between a test and a total. while Excel is gonna do most of the work it's not gonna do all of the work.
MAX
Let me head over and just select the test course now I can press Enter, or tapping tab would be more efficient because it would take me to the next cell that I need to be in to calculate the high score. Now I'm gonna try this again I'm gonna head over to the autosum function I need or the function I need. In this situation it is max so I'll click max. again I'll notice Excel is very good at picking up numbers but I'm not gonna press ENTER so quickly. I'm gonna make sure to only select test scores that I want to use for this function then I'll press tab.
MIN
I want to get the low score now. We could repeat what we just did the last two times but I'm gonna show you a different method. Just to exaggerate this point, we are not going to start where you want the result to be, we are not going to start where we want the result to be. if you didn't get it the first two times we are not going to start where we want the result to be so where are we going to start. we're going to start by selecting the values we want to calculate so I'm only selecting the values. then I'll go to the autosum functions and choose min that is the appropriate function to choose. to get the minimum amount from all those tests all it takes is one click and now I have the low score for all those values. now you're saying wait a minute is it including 395? No, we don't have that little animation and those dashed lines. this is just something that happens whenever you use this method. you might say well let's do this one more time. Okay, so I'm gonna do it one more time with the number completed. Again I'm not going to start here because sometimes you see people start off the right way but then they go and click in that cell and then go right back to the function. now you're gonna select the values then go to the command that you want and then you'll choose count numbers. with one click you'll be done so this method is the opposite of the last method you're gonna select the cells first not after.
Autofill
Now that I've gotten Alex's scores, Alex tattletales to all the other students and I have 11 kids running to my door wanting to get their total, average, high, low score and number completed. I'm not gonna repeat the same stack steps for that I did for Alex because that'll take forever. What I will do is select all the values and use autofill. I'll look for the black plus sign and I'll click and drag down this efficiently gives me the total, average, high, low, and number completed for all the other students very quickly. Now at this stage, we would have students in the class come up with the total for test one, the average, the high, low, and number completed all for test one and then repeat the same process for test two, three, four, and five. Let's take a look at a couple of different ways you can come up with the same answer now, first of all, I usually ask students what is the keyboard shortcut for autosum.
Now if you don't know the keyboard shortcut for autosum here's an easy way to figure it out all you have to do is hover your mouse over the autosum function and you will see it says alt equal. you'll ignore the plus sign because that's just telling you it's alt and equal so in that cell I'll hold on to alt and press equal and when I press ENTER I get the total. Now if I wanted to copy this formula over to the right I could by holding on to the shift key or using my mouse and selecting the cells to the right the keyboard shortcut to copy that formula. to the right is going to be ctrl R as in the direction I'm going in when I want to copy the formula. now if the keyboard shortcut to copy the formula to the right is ctrl R I bet you can imagine what the keyboard shortcut is going to be to copy the formula going down. that's going to be ctrl D.
Autosum Multiple Columns
so those are a couple of keyboard shortcuts you can use when you're working with your calculations. I'll show you one more tip. Normally what I could do is select the entire column just like I use for the second method in the last exercise and click autosum. what will happen is I will get the total at the bottom that is not a surprise but I'll ask you to imagine what you think will happen if you select all five columns and click autosum. Well, we'll see now with one click I get two total for all five tests. what this means is if I select all five columns and then choose average I'll get all the averages. In a way, this method is actually faster than the method you would think I would use here because I'm getting the answers all the way across. I will not have to use autofill when I get to the bottom. I'll choose min here to get the low score and then I'm gonna select everything and then finally choose count numbers. In this section, we took a look at how you can use the autosum functions to quickly come up with the results for some average Max and min in a table of information.