Today, coming down to the last in our "Don't Fear The Spreadsheet" series on Learn Excel from MrExcel, Tyler wants to know what some of Bill's Favorite Excel Functions are. Follow along with Episode #1645 as Bill covers a variety of Excel Functions that are useful, effective, efficient and worth learning as you grow your knowledge of Microsoft Excel.
If you are just starting out in Excel or you want to go back and find the things that you missed when you started using Excel, then "Don't Fear the Spreadsheet" is the 'Beginner Oriented' Excel How-To book for you. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Check out "Don't Fear the Spreadsheet" - Don't Fear the Spreadsheet
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
If you are just starting out in Excel or you want to go back and find the things that you missed when you started using Excel, then "Don't Fear the Spreadsheet" is the 'Beginner Oriented' Excel How-To book for you. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Check out "Don't Fear the Spreadsheet" - Don't Fear the Spreadsheet
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Don't Fear the Spreadsheet podcast episode number 19: favorite excel functions.
Hey, welcome back to the Don't Fear the Spreadsheet podcast.
I’m Bill Jelen from MrExcel.com.
Today's question: Tyler Nash.
Tyler: Hey MrExcel, what are some of you favorite Excel functions?
Bill: Well, Tyler, you could ask 20 different people what their favorite Excel functions are and you are going to get 20 different lists.
There’s 400 different functions in Excel.
This is kind of tough to answer.
I'm going to run through a few of my favorites.
Now, next episode we're going to talk about VLOOKUP so I won't talk about that one today.
You know, so here I need to count how many X's there are.
So we have some people in some weeks and most people think that this would be =COUNT( but COUNT won't work with this because COUNT only counts numbers.
So one of my favorite little tricks is if you need to count text or numbers then use =COUNTA( and it counts anything that’s non blanks.
=COUNTA( B2:B11) There's 19 X's there and if we add another X you see it increases.
All right, so COUNTA function number one.
Another thing because I'm writing books all the time, I need to create large data sets that just kind of have random values.
So, you know, let's say that, you know, you have a department staff meeting and one of the people in the department have to run to get lunch, right?
We're just going to pick their name out of a hat.
Well, not pick their name out of a hat, what we can do is we can say =INDEX( of this list of people.
Now there's ten people there so we need a number between one and ten and so I'm going to use =INDEX(E2:E11),RANDBETWEEN(1,10)).
Put two closing parentheses, one for the RANDBETWEEN, one for the INDEX and there, Dixie is the person today.
Tomorrow press F9 and then it's Jake, the next day Andy then Ike then Geena then Dixie.
See now Dixie came up twice before everyone else was picked.
That happens.
It's random.
It's like their name gets thrown back in the hat.
All right, here's another one.
I'm always doing date math and I'm going to talk about three different functions here.
I'm going to talk about the DATE function, which is incredibly versatile, the YEAR function and the MONTH function.
So the way that DATE works is =DATE(2012,8,15) and that will give us the 15th of the month corresponding to this.
But we don't want to have to enter the year and the month all the time so I'm going to use two more functions.
I'm going to say =DATE(Year(A2),Month(A2),15.
The year I just want the year as a function of what's over there at A and then I want the month of what's over there at A and then for the day I want the 15th all the time.
Double click to shoot that down and we always end up with the 15th of the month.
DATE is incredibly versatile.
Now I could go on and on and on but we should get a different perspective and for the different perspective I'm going to invite a guest podcaster.
Mike ‘Excel is Fun’ Girvin wrote the book that you should go to next.
Slaying Excel Dragons is a beginner's guide to conquering Excel’s frustrations and making Excel fun.
Mike actually teaches Excel at Highline Community College and this is the textbook he uses all semester to go through and really get you up into level 5, the top level of Excel but he starts at the very beginning.
So, Mike, what are your favorite Excel functions?
Mike: Thanks Tyler and MrExcel.
Let's take a look at the IF, AVERAGE, and COUNTIFS and SUMIFS.
These functions here are awesome for beginners or anybody at any level for that matter.
So we'll start with the IF function.
Now the IF function is great.
You just think of it as it puts one of two things in a cell.
So here we have a bonus situation.
Sioux’s sales are 48,500 and we need to compare them asking the question are Sioux’s sales greater than or equal to the hurdle?
If they are, which they are in this case, we need the word Yes in this cell but if the sales are actually 47,500 we compare this.
Is this greater than or equal to that?
We want the word No in this cell.
So anytime you have this situation, one of two things are always going into the cell, you can use the IF function.
So I'm going to say =IF( logical test.
That's where we need to compare so we click on the sales.
Are Sioux’s sales greater than or equal to?
=IF(F3>= Now this is called a comparative operator.
We're comparing the number 47,500 to the hurdle =IF(F3>=I3 Now, when you do greater than or equal to, there's no single comparative operator for that so you have to put the greater than symbol and then second put the equal sign.
Now that this is a logical test it only comes out TRUE or FALSE.
Now watch this.
I'm going to type a comma =IF(F3>=I3, and this screen tip is totally polite.
Hey, it says “What value do you want in the cell if it's true?” Well, we want the word Yes so I'm going to type in double quotes =IF(F3>=I3,”Yes” All text that you put into formulas, if you want it to go into the formula, you have to put it in double quotes.
Now I type in comma =IF(F3>=I3,”Yes”, and the screen tip is totally polite and it says, “Hey, what do you want me to put in the cell if it's false?” No and close parenthesis.
=IF(F3>=I3,”Yes”,”No”) Now I'm going to Ctrl + Enter.
Right now it says No but check this out, totally automatic.
When I changed the sales number to above the hurdle it tells me Yes.
Now you can do the same thing for numbers.
I'm going to say if sales greater than or equal to the hurdle =IF(F3>=I3, logical test comes out true or false comma what do you want if it's true?
The 750 bucks bonus comma =IF(F3>=I3,J3, otherwise what do you put in the cell if it's false?
Zero.
I'm just going to type that one in.
=IF(F3>=I3,J3,0) Ctrl + Enter.
Now let's test it.
Now, we test it above, below, and you always want to also test it exactly right on when you have that equal sign.
And there we have it.
Looks like it's working perfect.
One or two words in a cell or one or two number items.
Now another function that's great: the AVERAGE.
It adds them all up and divides by the count.
So students want to know their quiz average.
Now I'm going to type =AVER and notice AVERAGE function is blue on the little drop-down.
As soon as you see your function highlighted in blue, you can hit Tab.
I'm going to take my selection cursor and highlight =AVERAGE(F7:H7 Watch this.
Ctrl + Enter.
I don't even have to type that ending parenthesis when you have a simple function like AVERAGE or SUM or COUNT.
You can leave that last parenthesis off and it'll put it in for you.
Now I'm going to take my selection cursor and point very carefully to that little box in the lower right hand corner and when I see my cursor turned to a crosshair or angry rabbit I'm going to click and drag.
That copies the formula down.
I'm going to click on the last cell and hit F2.
=AVERAGE(F10:H10) Check that out.
It got the average for Tina perfect.
Another great function is COUNTIFS and SUMIFS.
Now MrExcel did COUNTA that counted all the non-empty cells but COUNTIFS is great.
We can say count just Sioux and it will look through this whole list it won't count all of them.
It'll just count the Siouxs.
This example with COUNTIFS, we have a count which means count if-- which means there's some condition in S which means you can have one or more conditions or criteria.
So I'm going to type =COUNTIFS( and then Tab.
The screen tip is very polite.
It says, “Where's the criteria range?” That's all the values comma =COUNTIFS(E18:E23, and then it's saying, “What's the criteria?” You simply click on Sioux.
=COUNTIFS(E18:E23, E15 Now I'm going to hit Tab and check this out.
It'll count three perfectly.
SUMIFS is not for counting with a condition.
It’s summing or adding.
So I'm going to do =SUMIFS( and there's just one extra argument before we get to criteria range and criteria.
It says, “What values do you want me to add?” So I highlight those comma =SUMIFS(F18:F23, criteria range comma =SUMIFS(F18:F23,E18:E23, and criteria =SUMIFS(F18:F23,E18:E23,E15 Notice reading the screen tips really helps.
Ctrl + Enter.
Totally perfect.
It added up just Sioux.
Now watch this.
I click on this cell and then I'm going to hold the control key and highlight those.
I can check my number down here looking at the sum and it looks like it got it right.
Now you can take SUMIFS and COUNTIFS one step further or as many steps as you want.
Let’s do two criteria.
I want to count Sioux and Product 1 so if you look through the list you should see that we should get the Count 2.
So =COUNTIFS( criteria range one, just be sure to read the screen tips, comma =COUNTIFS(E32:E37, criteria one comma =COUNTIFS(E32:E37,E28, criteria range two-- whoa check that out =COUNTIFS(E32:E37,E28,F32:F37 and then comma criteria two =COUNTIFS(E32:E37,E28,F32:F37,F28 Tab.
It got it right.
SUMIFS is the same =SUMIFS( We just have to add an extra first argument sum range.
So these are the values I want to add =SUMIFS(G32:G37, and then check this out.
The rest of the screen tips are exactly the same as the COUNTIFS.
Just read.
Be careful to read your screen tips as you type your commas and boom.
All right, I’ll throw it back to Tyler and MrExcel.
Tyler: Thanks for stopping by.
We’ll see you next episode.
Check out Don’t Fear the Spreadsheet.
This book makes Excel for Dummies look like it was written for rocket scientists.
Don't Fear the Spreadsheet podcast episode number 19: favorite excel functions.
Hey, welcome back to the Don't Fear the Spreadsheet podcast.
I’m Bill Jelen from MrExcel.com.
Today's question: Tyler Nash.
Tyler: Hey MrExcel, what are some of you favorite Excel functions?
Bill: Well, Tyler, you could ask 20 different people what their favorite Excel functions are and you are going to get 20 different lists.
There’s 400 different functions in Excel.
This is kind of tough to answer.
I'm going to run through a few of my favorites.
Now, next episode we're going to talk about VLOOKUP so I won't talk about that one today.
You know, so here I need to count how many X's there are.
So we have some people in some weeks and most people think that this would be =COUNT( but COUNT won't work with this because COUNT only counts numbers.
So one of my favorite little tricks is if you need to count text or numbers then use =COUNTA( and it counts anything that’s non blanks.
=COUNTA( B2:B11) There's 19 X's there and if we add another X you see it increases.
All right, so COUNTA function number one.
Another thing because I'm writing books all the time, I need to create large data sets that just kind of have random values.
So, you know, let's say that, you know, you have a department staff meeting and one of the people in the department have to run to get lunch, right?
We're just going to pick their name out of a hat.
Well, not pick their name out of a hat, what we can do is we can say =INDEX( of this list of people.
Now there's ten people there so we need a number between one and ten and so I'm going to use =INDEX(E2:E11),RANDBETWEEN(1,10)).
Put two closing parentheses, one for the RANDBETWEEN, one for the INDEX and there, Dixie is the person today.
Tomorrow press F9 and then it's Jake, the next day Andy then Ike then Geena then Dixie.
See now Dixie came up twice before everyone else was picked.
That happens.
It's random.
It's like their name gets thrown back in the hat.
All right, here's another one.
I'm always doing date math and I'm going to talk about three different functions here.
I'm going to talk about the DATE function, which is incredibly versatile, the YEAR function and the MONTH function.
So the way that DATE works is =DATE(2012,8,15) and that will give us the 15th of the month corresponding to this.
But we don't want to have to enter the year and the month all the time so I'm going to use two more functions.
I'm going to say =DATE(Year(A2),Month(A2),15.
The year I just want the year as a function of what's over there at A and then I want the month of what's over there at A and then for the day I want the 15th all the time.
Double click to shoot that down and we always end up with the 15th of the month.
DATE is incredibly versatile.
Now I could go on and on and on but we should get a different perspective and for the different perspective I'm going to invite a guest podcaster.
Mike ‘Excel is Fun’ Girvin wrote the book that you should go to next.
Slaying Excel Dragons is a beginner's guide to conquering Excel’s frustrations and making Excel fun.
Mike actually teaches Excel at Highline Community College and this is the textbook he uses all semester to go through and really get you up into level 5, the top level of Excel but he starts at the very beginning.
So, Mike, what are your favorite Excel functions?
Mike: Thanks Tyler and MrExcel.
Let's take a look at the IF, AVERAGE, and COUNTIFS and SUMIFS.
These functions here are awesome for beginners or anybody at any level for that matter.
So we'll start with the IF function.
Now the IF function is great.
You just think of it as it puts one of two things in a cell.
So here we have a bonus situation.
Sioux’s sales are 48,500 and we need to compare them asking the question are Sioux’s sales greater than or equal to the hurdle?
If they are, which they are in this case, we need the word Yes in this cell but if the sales are actually 47,500 we compare this.
Is this greater than or equal to that?
We want the word No in this cell.
So anytime you have this situation, one of two things are always going into the cell, you can use the IF function.
So I'm going to say =IF( logical test.
That's where we need to compare so we click on the sales.
Are Sioux’s sales greater than or equal to?
=IF(F3>= Now this is called a comparative operator.
We're comparing the number 47,500 to the hurdle =IF(F3>=I3 Now, when you do greater than or equal to, there's no single comparative operator for that so you have to put the greater than symbol and then second put the equal sign.
Now that this is a logical test it only comes out TRUE or FALSE.
Now watch this.
I'm going to type a comma =IF(F3>=I3, and this screen tip is totally polite.
Hey, it says “What value do you want in the cell if it's true?” Well, we want the word Yes so I'm going to type in double quotes =IF(F3>=I3,”Yes” All text that you put into formulas, if you want it to go into the formula, you have to put it in double quotes.
Now I type in comma =IF(F3>=I3,”Yes”, and the screen tip is totally polite and it says, “Hey, what do you want me to put in the cell if it's false?” No and close parenthesis.
=IF(F3>=I3,”Yes”,”No”) Now I'm going to Ctrl + Enter.
Right now it says No but check this out, totally automatic.
When I changed the sales number to above the hurdle it tells me Yes.
Now you can do the same thing for numbers.
I'm going to say if sales greater than or equal to the hurdle =IF(F3>=I3, logical test comes out true or false comma what do you want if it's true?
The 750 bucks bonus comma =IF(F3>=I3,J3, otherwise what do you put in the cell if it's false?
Zero.
I'm just going to type that one in.
=IF(F3>=I3,J3,0) Ctrl + Enter.
Now let's test it.
Now, we test it above, below, and you always want to also test it exactly right on when you have that equal sign.
And there we have it.
Looks like it's working perfect.
One or two words in a cell or one or two number items.
Now another function that's great: the AVERAGE.
It adds them all up and divides by the count.
So students want to know their quiz average.
Now I'm going to type =AVER and notice AVERAGE function is blue on the little drop-down.
As soon as you see your function highlighted in blue, you can hit Tab.
I'm going to take my selection cursor and highlight =AVERAGE(F7:H7 Watch this.
Ctrl + Enter.
I don't even have to type that ending parenthesis when you have a simple function like AVERAGE or SUM or COUNT.
You can leave that last parenthesis off and it'll put it in for you.
Now I'm going to take my selection cursor and point very carefully to that little box in the lower right hand corner and when I see my cursor turned to a crosshair or angry rabbit I'm going to click and drag.
That copies the formula down.
I'm going to click on the last cell and hit F2.
=AVERAGE(F10:H10) Check that out.
It got the average for Tina perfect.
Another great function is COUNTIFS and SUMIFS.
Now MrExcel did COUNTA that counted all the non-empty cells but COUNTIFS is great.
We can say count just Sioux and it will look through this whole list it won't count all of them.
It'll just count the Siouxs.
This example with COUNTIFS, we have a count which means count if-- which means there's some condition in S which means you can have one or more conditions or criteria.
So I'm going to type =COUNTIFS( and then Tab.
The screen tip is very polite.
It says, “Where's the criteria range?” That's all the values comma =COUNTIFS(E18:E23, and then it's saying, “What's the criteria?” You simply click on Sioux.
=COUNTIFS(E18:E23, E15 Now I'm going to hit Tab and check this out.
It'll count three perfectly.
SUMIFS is not for counting with a condition.
It’s summing or adding.
So I'm going to do =SUMIFS( and there's just one extra argument before we get to criteria range and criteria.
It says, “What values do you want me to add?” So I highlight those comma =SUMIFS(F18:F23, criteria range comma =SUMIFS(F18:F23,E18:E23, and criteria =SUMIFS(F18:F23,E18:E23,E15 Notice reading the screen tips really helps.
Ctrl + Enter.
Totally perfect.
It added up just Sioux.
Now watch this.
I click on this cell and then I'm going to hold the control key and highlight those.
I can check my number down here looking at the sum and it looks like it got it right.
Now you can take SUMIFS and COUNTIFS one step further or as many steps as you want.
Let’s do two criteria.
I want to count Sioux and Product 1 so if you look through the list you should see that we should get the Count 2.
So =COUNTIFS( criteria range one, just be sure to read the screen tips, comma =COUNTIFS(E32:E37, criteria one comma =COUNTIFS(E32:E37,E28, criteria range two-- whoa check that out =COUNTIFS(E32:E37,E28,F32:F37 and then comma criteria two =COUNTIFS(E32:E37,E28,F32:F37,F28 Tab.
It got it right.
SUMIFS is the same =SUMIFS( We just have to add an extra first argument sum range.
So these are the values I want to add =SUMIFS(G32:G37, and then check this out.
The rest of the screen tips are exactly the same as the COUNTIFS.
Just read.
Be careful to read your screen tips as you type your commas and boom.
All right, I’ll throw it back to Tyler and MrExcel.
Tyler: Thanks for stopping by.
We’ll see you next episode.
Check out Don’t Fear the Spreadsheet.
This book makes Excel for Dummies look like it was written for rocket scientists.
