Join Dennis Taylor for an in-depth discussion in this video Using SUM and AVERAGE, part of Excel 2016 Essential Training.
- In this workbook, 03 - Creating Formulas and Functions, we're looking at a worksheet called Sum Average. Sum and Average represent two of the most commonly used functions in Excel, and there are some shortcuts here. If we're about to create a total in cell G2 we can use the function Sum. We can type =sum, left (, and highlight these cells, and press Enter, we've got an answer. Now, potentially a faster way to do this is to use a feature called the AutoSum button.
On the Home tab you'll see it here off to the right in the Editing group, there's AutoSum. There's a keystroke shortcut referred to below it, Alt + =. If we click AutoSum in cell G2 Excel says, "I see numbers to the left, let's add them." Press Enter, we've got our total. That's likely to be faster than typing in the entry. Now, you can get there a little bit faster. Why not just double click AutoSum, there we are, rather than pressing AutoSum and then Enter? If you press Alt + = you will have to press Enter.
But at other times that can be faster too. You'll also find this AutoSum button on the formulas tab. It's off to the left a bit larger. Double click it, remember, slightly faster. If you wanna see the formula, though, sometimes you do, you just click it once. Second time brings it in. We can do the same kind of thing in cell B9. The AutoSum button is designed to add up data from the left or from above. Now, we can position the active cell in B9 and press AutoSum, or Alt + =, or possibly we can even highlight the data ahead of time.
And in our previous example we could've highlighted the data that way too. The advantage of highlighting ahead of time is we need only click the button once or press Alt + =. Either way I'm pressing press Alt + =. There's a total on the bottom. An alternate way but no better, as I press ctrl + z to undo, is to highlight all these cells here. Press Alt + =, there's a total. Now, here's a situation where we've got cells above the empty cell and to the left. So, if we position the active cell right here in H7, what does AutoSum do? There's the AutoSum button.
I'm either pressing this or Alt + =. Do the same thing. It's trying to add data from above. I wanna add the data from the left I'll simply use the mouse and highlight these three cells. Now, there's a whole variety what it wants to do. Anytime Excel sees data above and to the left it favors the upward direction and tries to add up that data first. In the list down there is 12 through 15. If we only wanted totals here on the right, right here, we could highlight these cells, press Alt + = or the AutoSum buttons in either location, there's a total.
If wanted totals at the bottom we could highlight these cells. Do the same kind of thing. Alt + =, there we go. Let me undo both of those. If we knew ahead of time that we wanted totals both on the right and below, we could highlight the data this way and simply press AutoSum or Alt + = and we get totals that way. Now, there's some other aspects of this too that could be faster. Instead of a total up here, what if we wanted to know the maximum value? I'll just put in the heading there. I'm going to click not the AutoSum button itself but the error that's associated with it.
Here's it below AutoSum. On the Home tab it's to the right. Click the drop arrow, I wanna choose Max, meaning Maximum. Enter. There's the highest value on the list. So, we can use Max or Min, or how about going back here where we had totals here on the perimeter. Let's take out these, Delete. I like these, Delete, and select this area, but what if we wanted to do an average? And we're going to click the drop arrow associated with AutoSum and choose Average.
And now I've got averages on the right and below. We'd reformat those eventually but these are all averages. So, these are all averages of cells to the left like this. These are averages of the cells above. And the one in the corner, an average of the cells to the left. Now, if you wanna add data from different locations you can also do that with the Sum function. What I'm about to do would certainly be unorthodox because I would like to add some data from this group and from this group over here, and maybe from some data down here. And rather than typing = Sum, which I certainly could do, I'll just start with Alt + =.
I'm ignoring what Excel is suggesting, though, and I'm gonna highlight these two cells up here, D2 and E2, put in a comma, and over in column B highlight those two, put in a comma, and maybe come down to this lower list. Now granted, who would be adding up cells like this? Probably nobody. But it does point out the idea that we can build the Sum function to gather data from different locations, and if you start with Alt + = you just save yourself the effort of typing = SUM left (.
So, it makes it a bit faster and Enter. We've got our total. So, the AutoSum button, its keystroke shortcut, Alt + =, and the occasional use of that drop arrow, really makes tabulating sums and averages, and occasionally other functions, very fast and efficient.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros