Two of the most frequently needed functions in Excel are SUM and AVERAGE. The AutoSum button, or the Alt+= keystroke combination, simplifies creating totals.
- [Instructor] Whenever you're trying to add up numbers in a column or a row, or come up with an average of those numbers, you rely on two of Excels most commonly used functions; sum and average. Now there's a menu button, there's a keystroke shortcut, and also some different selection techniques that are gonna make these functions easier to use. We're looking at the worksheet Sum, average in the workbook O3, creating formulas and functions. First, as a reminder, on the formulas tab in the ribbon, in the menu system, you will see ultimately all Excel functions.
There are over 450 of them. And no body needs to know them all, that's for sure. If you're looking for a particular function, you're not always sure where to begin, is it math and trig? Well maybe, maybe not. Slide over a function, you'll see a description. Nobody quite approaches looking for functions in that way. Most of the time we do it based on context. We want to add up data right here in cell G2, on this worksheet called Sum, average. We're in the workbook O3, creating formulas and functions. We can type in, and you might've seen this in an earlier movie, equal S U M.
But on the left side of the formulas tab, here's a button called Autosum. I'm going to click it. And we see what happens, Excel is about to add up that data, that looks good, we'll press enter. On the home tab as well, a tab we often have active. On the right side of the home tab is Autosum as well. Now after using that for a bit and recognizing how quickly it works, let's delete this cell. Let's do it again, this time let's click Autosum twice. Now again you can use it on the home tab or on the formulas tab. Click, click, there it is.
Becomes a little bit faster. And there's a keystroke shortcut. Alt, equal. Sometimes that'll be handy. The Autosum button is designed to add up data from the left or from above. So I've got a situation here, I wanna add up these numbers here. I'll click that Autosum button. Looks good, click it again, all set. Remember, could've double clicked it. Here's another thought, unnecessarily better, if you select the data ahead of time, then you can click Autosum once. We've got a total. Remember you can use it in either of its locations on the right side of the home tab or the left side of the formulas tab, same button.
Now, in regards to the keystroke shortcut, sometimes that's going to be better. We've got this data highlighted. Now granted you're moving your hand from the mouse to the keyboard, but it's Alt, equal. A keyboard shortcut for Autosum. Here's a rare situation. You've got data above the active cell and to the left, I'm in cell H7. So what happens here if we try Autosum? Using either the keystroke shortcut or the button, Excel looks upward first. Now if you say I wanna add up the cells to the left, what do you do with the mouse? Highlight these three cells.
Other words, you redirect it then press enter. And this list down below, if you wanted totals just on the right side, highlight these cells, use either technique, Alt, equal or the Autosum button. I'm pressing Alt, equal. That works fine. And similarly we can do the same thing across the bottom. Alt, equal. Or that button. Now I'm going to undo my last two actions and highlight the cells this way. We can put totals on the right and the bottom. Either with the button or the keystroke shortcut. So here's Autosum. Click it once, there we are.
We've got our totals. And there's another variation on this. Sometimes you'd rather do an average. So, once again I'll press Control Z to undo the last action. If you'd like to get an average of these both on the right and on the bottom, Autosum, in both of its locations, is accompanied by a tiny little drop arrow. Click it, and there's the choice called average. Let's do an average. Later we'll adjust the formatting, but we click on this cell, I'll double click right here to see it larger. And we get an average. Press escape, same thing up here is I'll double click and across the bottom.
So that's gonna be handy too. So although you can use the drop arrow to get the average, sometimes it will be a bit faster simply to type it. I'm gonna do an average here in cell B9. Equal average. Another shortcut is your typing. As soon as you see the function in the list here, now we're not seeing it yet, so as I put in V, there it is, I could double click, or tab, average pops into place. Then we'll highlight the data and enter, you don't have to put in the right parenthesis when you're only using the function by itself. So both of those functions are widely used as one of many many functions in Excel.
And if you have extra time you can and you will discover over time, as you work with data, there are functions for calculating median, counting cells that have data, counting number cells, come up with the second largest. So lots of different functions related to working with values in Excel.
- Navigating Excel tabs and menus
- 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