Join Dennis Taylor for an in-depth discussion in this video Using SUM and AVERAGE, part of Excel Essential Training (Office 365).
- [Instructor] Whenever you're trying to add up numbers in a column or a row, or coming up with an average of those numbers, you can rely on two of Excel's most commonly used functions, sum and average. Now there's a menu button, there's keys to a shortcut, they're gonna help in your use of these functions. We're looking at this worksheet called sum average. It's in the workbook zero three, creating formulas and functions. We wanna total in cell G two, we wanna add up the data. You might've seen in an earlier movie, we can start typing equal S-U-M, left parenthesis, highlight the data and so on, nothing wrong with that, but there's a better way, there's a faster way, two faster ways.
On the home tab, in the editing group on the right side, you'll see a button called autosum. If you click this button, Excel looks upward for data, then it looks leftward, in this case, no numbers appear above it, but certainly leftward, we click this button, Excel shows us what it's about to do, we're gonna press enter. After using that for a few times, you become a little bit more confident with it, and you can speed it up a little bit. We can double click autosum. Click, click, there it is. There's our total. And what has it done for us? It's put in the sum function.
If I double click in the cell here as a reminder, you can also see this in the formula bar, we see what it's done. Similarly, in cell B nine, we could use that same button. And by the way, that button also appears on the formulas tab in the ribbon. If I click the formulas tab it's on the left side. So it's in two locations. It's also got a key stroke shortcut. I'm gonna press alt equal, looks good, I'll press enter. Same general idea, same approach. Another thought here, too, as I delete this entry, if you highlight cells ahead of time, you can either press the autosum button once or alt equal once, I'll press alt equal this time.
Total in the bottom, could've pressed the autosum button. Sometimes you'll run into a slight conflict, not a major one here, what happens here if you press autosum, or alt equal? Here's autosum, Excel looks upward, but I wanted to add up the data to the left. What do I do? I'll simply highlight those cells instead, Excel sees that, enter, all set. If we want totals on the right side, in rows 12 through 15, we can highlight these cells. And we'll take one of two actions. We'll either click the autosum button or we'll press alt equal and in either case that's it.
I don't have to do anything else, that's it. I'll delete that, similarly across the bottom we could do the same thing. And a surprise perhaps, if we highlight the data this way, in other words include the empty cells on the right and on the bottom, we can click either autosum button or alt equal, either way, we get our totals on the right. So for example, this cell right here is doing what? It's adding up these cells, same thing down here, so on, and down here in row 16, we're adding up this way.
Cell in the corner is doing this. So that's easy, and here's another action. I'm gonna press control Z, if you wanted an average on the right hand side, autosum has a little drop arrow right here and what do we see? Average, now we're going to be getting an average on the right side and on the bottom. And there are decimals there, eventually we would change the format to produce a more standard look. Now, I'm gonna make a statement here with the, might sound a little strange, never type equal S-U-M. Well why not? It's certainly not wrong to type it, we could fill in other data here.
But, when you press alt equal, Excel does that for you. Now it might make a bad guess, I don't wanna add that up but I can point to other cells that I do want to add. Maybe wanna add up these cells over here. Point is you can redirect it and why type equal S-U-M, when Excel does it for you by pressing alt equal. So these are valuable tools as you work with formulas in Excel, there's no question that the sum function and the average function are widely used by most excel users. The autosum button, it's key stroke shortcut, alt equal, and occasionally use that drop arrow next to alt equal, all these make tabulating sums and averages and occasionally other functions, too, really fast and efficient.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting your data
- Adjusting rows and columns
- Finding and replacing data
- Inserting and deleting sheets
- Sorting and filtering data
- Creating charts and PivotTables
- Printing and sharing worksheets
- Protecting worksheets and workbooks
Skill Level Beginner
Q: This course was updated on 1/7/2019. What changed?
A: A new video was added that covers working with Excel Ideas.