Master Excel's AutoSum functions to quickly and efficiently perform calculations on large datasets. Learn practical tips and shortcuts to simplify tasks involving sums, averages, and identifying maximum or minimum values.
Key Insights
- Utilize Excel's AutoSum functions—Sum, Average, Count Numbers, Max, and Min—to rapidly perform calculations by accessing them via the Home or Formulas tab, significantly reducing the need for manual formula entry.
- Enhance efficiency by selecting data first before applying the AutoSum function, ensuring accuracy of calculations such as determining the highest or lowest test scores among a range of student results.
- Apply keyboard shortcuts like Alt+Equal for AutoSum, Control-R to copy formulas horizontally, and Control-D for vertical copying to speed up data processing tasks within Excel.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
Autosum functions. 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.
So, what are the autosum functions? Well, there 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 autosum. 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 autosum 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 Autosum.
And just like with autosum functions on the Home tab, I can click this drop-down triangle and access the other autosum functions available there. Now, let's talk about this table of information. We have 12 students who have taken five tests.
I'm going to 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. So, I'm going to 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 parentheses. Then I could take my mouse and select all the values that I want to sum. Then I can put in the close parentheses and press ENTER.
This will give me the total, but it takes some time. The autosum functions are going to 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 going to press is Enter.
And now I have the total, much quicker than the prior method. Now, I want to get the average, so I'm going to head over to the average. Since this works 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 Control Z to undo. When I click the drop-down, and then when I selected Average, I should take note of what is currently being selected.
Excel is not only selecting the five test scores, but it's also selecting the total. So, 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.
So, while Excel is going to do most of the work, it's not going to do all of the work. So, let me head over and just select the test scores. Now, I can press ENTER or Tab.
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 going to try this again. I'm going to head over to the AutoSum function.
The command I need, or the function I need in this situation, is Max. So, I'll click Max. Again, I'll notice Excel is very good at picking up numbers, but I'm not going to press ENTER so quickly.
I'm going to make sure to only select the test scores that I want to use for this function. Then, I'll press TAB. Now, I want to get the low score.
Now, we could repeat what we just did the last two times, but I'm going to show you a different method. Now, 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 going to do it one more time with number completed. Again, I'm not going to start here because sometimes I see people start off the right way, but then they go and click in that cell and then go right back to the function. No, you're going to 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 going to select the cells first, not after.
Now that I've gotten Alex's scores, Alex is tattletaled 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 going to repeat the same steps 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 1, the average, the high, low, and number completed all for Test 1, and then repeat the same process for Test 2,3, 4, and 5. 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 down 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 Control-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 Control-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 Control-D. 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 used 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 the total for all five tests.
So, what this means is if I select all five columns and then choose Average, I'll get all the averages. So, 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 going to select everything, and then finally choose Count Numbers. So, in this section, we took a look at how you can use the AutoSum functions to quickly come up with the results for Sum, Average, Max, and Min in a table of information.