You asked for it and here it is: a Learn Excel from MrExcel Podcast Series covering the Most Popular Functions in Microsoft Excel.
Bill will be covering the Most Popular Functions in Excel in the series starting with "=SUM". Follow along with Bill today in Episode #1553 to learn a few tips - including how you can win a free copy of "Microsoft Excel 2010 In Depth" by Bill Jelen.
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel. Microsoft Excel 2010 In Depth by Bill Jelen
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Bill will be covering the Most Popular Functions in Excel in the series starting with "=SUM". Follow along with Bill today in Episode #1553 to learn a few tips - including how you can win a free copy of "Microsoft Excel 2010 In Depth" by Bill Jelen.
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel. Microsoft Excel 2010 In Depth by Bill Jelen
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1553 - Popular Excel Functions #1 Sum.
Well, hey we’re going to do a new series here at the podcast where I run through some of the most popular Excel calculation functions.
And I want you to help.
I want you to send them an email to mailto:bill@mrexcel.com.
Think about your favorite functions like SUM, AVERAGE, COUNT, MAX, MIN, the functions that you could not live without.
Just put those functions listed in the subject line.
We’ll choose one person at random on May 5th when all the entries are received, when a copy of Excel 2010 in depth.
This is my book that covers everything about Excel including every single Excel function.
There’s a useful tip for every function in there about 300 pages worth of functions.
So, it seems like an appropriate price.
Alright.
Function number one, the function that everyone learns at the beginning is the SUM function.
Now, don’t go away.
You’re going to learn some cool things here.
But, when I started using spreadsheets all the way back in 1985, I would add-- at the time was a plus sign.
I’d do =B2+B3.
And that worked out great and I was doing that all the time.
Even when I had more than two numbers to add, 3, 4, 5 numbers-- and one of the MBA students came up behind me and said, “You know, that’s not the best way to add because eventually, you’re going to get too many numbers.
And then, what you’re going to do, you’re going to have to like add up the whole range.” You’re not going to be able to do that for 100 rows.
And he taught me how to use.
At that time, it was ADDSUM.
But now, it’s =SUM.
So, =SUM, we want to add everything from B2 through B9 like that.
And it adds everything up.
Of course, you know, the beautiful thing here is you change the number and that sum automatically calculates.
Now, some new things, now, in Excel 2010, and I’m not actually sure when that started.
If we insert a new row here, and add a new number, watch that formula.
It’s actually going to animate and change the formula.
So, yeah, it is kind of became blue and now includes that extra row.
Now, that never used to happen a long time ago.
That’s a new feature, but obviously, very cool way to go now.
A faster way to enter that sum function is to use the AutoSum button.
The AutoSum icon is here on the Formulas tab.
It’s also on the right-hand side of the home tab.
But, I don’t use that at all.
I use this shortcut key of ALT + =. So, wherever you hit AutoSum, I hit ALT + =. Just press ALT + =. And it figures out what numbers it thinks we’re talking about and it gives us a chance to edit the formula, and then, press ENTER like that.
So, the AutoSum button, when I go to the next worksheet, AutoSum also works going across.
So, if you have some numbers going across, hit all equals or AutoSum.
It will add a cross.
What if you have a range that’s not anywhere your total.
So, I hit the AutoSum.
And it says, “Geez, I don’t know what you want.” Just take the mouse and highlight the correct range and it will add those numbers up.
Now, a couple of cool things is for those of you who are saying, “Oh, no.
He’s not going to cover up all AutoSum.
How boring is that?” Let’s say you have to add up all of these numbers.
We want a total row across the bottom and a total row at the right-hand side.
Watch how cool this is.
Choose the numbers you want plus the extra row, plus the extra column.
Either all equals, or AutoSum, and it puts the formulas all the way around.
So, totaling across or totaling down, next worksheet, this is a trick from Bob’s.
Unless we want to total all of the blank cells, check this out.
We choose everything.
CTRL + G for Go to Special, click Blanks, OK.
Now, we’ve selected those blank cells, hit the AutoSum, or ALT + =. And it puts totals in everywhere.
Very cool way to go.
Alright, so, in my opinion, the number one popular function in Excel is the SUM function.
There’s a lot of different ways to use it.
I invite you to shoot an email to mailto:bill@mrexcel.com with your favorite functions.
Include that in this podcast series.
Well, hey, I want to thank you for stopping by.
See you next time for another netcast of MrExcel.
Learn Excel from MrExcel podcast episode 1553 - Popular Excel Functions #1 Sum.
Well, hey we’re going to do a new series here at the podcast where I run through some of the most popular Excel calculation functions.
And I want you to help.
I want you to send them an email to mailto:bill@mrexcel.com.
Think about your favorite functions like SUM, AVERAGE, COUNT, MAX, MIN, the functions that you could not live without.
Just put those functions listed in the subject line.
We’ll choose one person at random on May 5th when all the entries are received, when a copy of Excel 2010 in depth.
This is my book that covers everything about Excel including every single Excel function.
There’s a useful tip for every function in there about 300 pages worth of functions.
So, it seems like an appropriate price.
Alright.
Function number one, the function that everyone learns at the beginning is the SUM function.
Now, don’t go away.
You’re going to learn some cool things here.
But, when I started using spreadsheets all the way back in 1985, I would add-- at the time was a plus sign.
I’d do =B2+B3.
And that worked out great and I was doing that all the time.
Even when I had more than two numbers to add, 3, 4, 5 numbers-- and one of the MBA students came up behind me and said, “You know, that’s not the best way to add because eventually, you’re going to get too many numbers.
And then, what you’re going to do, you’re going to have to like add up the whole range.” You’re not going to be able to do that for 100 rows.
And he taught me how to use.
At that time, it was ADDSUM.
But now, it’s =SUM.
So, =SUM, we want to add everything from B2 through B9 like that.
And it adds everything up.
Of course, you know, the beautiful thing here is you change the number and that sum automatically calculates.
Now, some new things, now, in Excel 2010, and I’m not actually sure when that started.
If we insert a new row here, and add a new number, watch that formula.
It’s actually going to animate and change the formula.
So, yeah, it is kind of became blue and now includes that extra row.
Now, that never used to happen a long time ago.
That’s a new feature, but obviously, very cool way to go now.
A faster way to enter that sum function is to use the AutoSum button.
The AutoSum icon is here on the Formulas tab.
It’s also on the right-hand side of the home tab.
But, I don’t use that at all.
I use this shortcut key of ALT + =. So, wherever you hit AutoSum, I hit ALT + =. Just press ALT + =. And it figures out what numbers it thinks we’re talking about and it gives us a chance to edit the formula, and then, press ENTER like that.
So, the AutoSum button, when I go to the next worksheet, AutoSum also works going across.
So, if you have some numbers going across, hit all equals or AutoSum.
It will add a cross.
What if you have a range that’s not anywhere your total.
So, I hit the AutoSum.
And it says, “Geez, I don’t know what you want.” Just take the mouse and highlight the correct range and it will add those numbers up.
Now, a couple of cool things is for those of you who are saying, “Oh, no.
He’s not going to cover up all AutoSum.
How boring is that?” Let’s say you have to add up all of these numbers.
We want a total row across the bottom and a total row at the right-hand side.
Watch how cool this is.
Choose the numbers you want plus the extra row, plus the extra column.
Either all equals, or AutoSum, and it puts the formulas all the way around.
So, totaling across or totaling down, next worksheet, this is a trick from Bob’s.
Unless we want to total all of the blank cells, check this out.
We choose everything.
CTRL + G for Go to Special, click Blanks, OK.
Now, we’ve selected those blank cells, hit the AutoSum, or ALT + =. And it puts totals in everywhere.
Very cool way to go.
Alright, so, in my opinion, the number one popular function in Excel is the SUM function.
There’s a lot of different ways to use it.
I invite you to shoot an email to mailto:bill@mrexcel.com with your favorite functions.
Include that in this podcast series.
Well, hey, I want to thank you for stopping by.
See you next time for another netcast of MrExcel.
