Viewers: in countries Watching now:
In Excel 2010 Essential Training, Bob Flisser demonstrates the core features and tools in Excel 2010. The course introduces key Excel skills, shows how to utilize these skills with in-depth tutorials on Excel functions and spreadsheet formatting. It also covers prepping documents for printing, working with large worksheets and workbooks, collaborating with others, using Excel as a database, analyzing data, charting, and automating and customizing Excel. Exercise files are included with the course.
Now if you've completed the exercise on adding a whole worksheet at once or one of the AutoSum movies, you should have a worksheet that looks something like this. So we have our Totals column. We added across all the rows. We have the totals down here. We added down all the columns. But there are a few other things that we might want to know about these numbers. So let's click down here in Cell A15 and type the word Average. We want to know what the average is of these numbers. I'll just press Tab. So we'll use the Average function. I'll type =Average. Now by the time I type the first four letters of the word average, you see Excel is suggesting that I probably want the Average function.
So I'll just press the Tab key and Excel will fill in the rest. So we want the average of what? Well, we want the average of these numbers in January. So just drag over those numbers. Be sure you don't drag anywhere below Row 12. You just want San Francisco through Boston, and that's it. Just press Ctrl+Enter and it gives us that average. Let's just try one more time. Click over here in Cell C15. Type =Average. Just type the first four letters. You can press Tab and it fills in the rest of Average. And just select those numbers in February.
Make sure not to select any more or any less and just press this time Ctrl+Enter so we stay in that cell. And now when you put your mouse pointer on that heavy dot in the little right corner and you get the crosshair, you can just drag across to Auto Fill. And now we see what the average is of each of those columns. Now there are decimals in there, which you might want, you might not want. That's a formatting issue and we'll look at that in a little bit. But let's go down here under Average. Click in Cell A16. Maybe we want to find what's the highest number in the series. So type the word Highest and again press Tab.
The function that tells us what's the highest number in the series is =Max, and open up the parenthesis. And again, select those same numbers down the January column. So this is telling us, well, find the maximum value in B6 through B12. And just press Ctrl+Enter to stay in one spot. Put your mouse pointer on that dot in the lower right corner of the cell, so you get the crosshair, and drag the Auto Fill handle across to the right. And now you can see what's the highest number in each of those columns.
Well, if we find out what's the highest number, we might want to know what's the lowest number. So let's go over here into A17 and type the word Lowest and again press Tab. Well, if the =Max function finds the highest number in the series, what do you suppose is the name of the function that finds us the lowest number in the series? Yes, you're right. It's the MIN function. So we type =Min, open up the parenthesis. And again drag down those same numbers. Recognize a pattern here? And press Ctrl+Enter so we stay in the same spot.
Put your mouse pointer on that heavy dot, so you get the crosshair, and drag the Auto Fill handle across. And now we can see what's the lowest number in the series. One other thing we might want to know is not so much the value but how many numbers do we have? How many cells are filled in? So click over here. And let's simply type in the word Quantity and press Tab. The function that tells us how many cells are filled in is Count. So we say =Count, and you see it's already suggesting that to us, and open up the parenthesis and guess what? We drag those same numbers from San Fran down to Boston.
Press Ctrl+Enter so we stay in the same spot. And this of course is going to be 7 all the way across because we have seven in each one. And you might wonder, oh gee, that's really big news. But if you just click one, let's say we didn't go to Chicago in February, press Delete and over here we have only six numbers. And I'm going to undo it. I kind of like Chicago. So we now have all those seven. Now here's what's really pretty neat. And let's just maybe delete those numbers there in the Average row and go here to January.
Remember the AutoSum tool? If you click that down arrow, look what you have. In addition to AutoSum we have sort of like an auto Average sort of Count, not quite as automatic as Sum, but if you choose Average, you see it puts in the function but of course, you still have to manually drag across those numbers. And Ctrl+Enter and drag the Auto Fill. Or the same thing for the Lowest. Let's just delete those. Go back over here to Cell B17. And now if you click this down arrow here and you choose Min, all it does is it puts that in.
But it's selecting the wrong cells. So you still have to select that. Remember Excel can't quite read your mind. Maybe in the next version. And Ctrl+Enter and get your Auto Fill handle and drag across. So those are some of the most common functions in Excel and there are over 300 functions, but just because those are so common, I think you'll find these pretty handy.
There are currently no FAQs about Excel 2010 Essential Training.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.