Auto-SUM is pretty straight foward - it resides on the Ribbon of Excel. But what about 'Auto-Average'? Today, the student enlightens the teacher when Bill finds out that 'Auto-Average' will reside on the Ribbon as a one-click function. Follow along with Episode #1557 to find out how to add Auto-Average, Min, Max and others to the Ribbon of Excel for one-click functionality.
...This episode is the video podcast companion to the book, "Don't Fear The Spreadsheet" by Tyler Nash and Bill Jelen. The Soon To Be Released Excel Book, "Don't Fear The Spreadsheet" [Due Late May, 2012] is for beginners, by a beginner. Nothing is assumed and all of the basics are explained in "Don't Fear The Spreadsheet"
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode is the video podcast companion to the book, "Don't Fear The Spreadsheet" by Tyler Nash and Bill Jelen. The Soon To Be Released Excel Book, "Don't Fear The Spreadsheet" [Due Late May, 2012] is for beginners, by a beginner. Nothing is assumed and all of the basics are explained in "Don't Fear The Spreadsheet"
"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 1557: “Auto Average”.
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen, coming down the final steps here, writing this book: “Don't fear the spreadsheet.” Now “Don't fear the spreadsheet” is for the absolute beginner.
I like to say that it makes Excel for dummies look like it was written for Excel gurus all right and the way this is working as Tyler who has never used spreadsheets before open Excel and came up with a hundred questions just looking at it from a, hey I've never used Excel before.
I'm going through these questions and some of them just, you know, make me laugh.
You know, so obviously we have AUTOSUM up here right and one of the things I show people a lot is you know it's not just AUTOSUM but there's average count max and min and 1 times questions is hey can I change this button so that we want to click it I'm going to get the average or the count instead of the SUM.
Can I change what the default is, you know, my answer is like, no.
But then I started to think about it, like I wonder if you can.
So I came up here to the Customize Quick Access Toolbar.
I'm sure it's not popular.
I've never even heard of it.
I'ma say all commands and I kind of cruise down here towards the Avs right and there's Average Values and Average.
So I hover to see what the Average is.
Sure enough Home Tab, Editing group, the Auto Sum Average, there it is.
So there is a one-click way to do the Average all right and let's see what's the other one - Count Numbs.
There it is add that and then Max and Min.
Max and min, now unfortunately in Microsoft's infinite wisdom here they made the icon for all four of these be the green crystal ball.
I hate that green crystal ball, it's like you have to be a fortune teller to figure out what the heck it is.
But if I need to Average this now I got four crystal balls up there.
They're arranged alphabetically.
Just hit the first one for Average and we good to go.
No need to open the drop-down.
In fact there let's just talk about, will do this, create large area here, =RAND() and we need to =AVERAGE the whole thing.
So we take the whole set of numbers, one extra row, one extra column hit the =AVERAGE and bam, AVERAGE form does all the way around.
A very very cool feature, one click for AUTO AVERAGE, AUTO COUNT, AUTO MAX and AUTO MIN.
Thanks to Tyler for even coming up with that crazy question.
Okay if you know someone who's completely new to spreadsheets, maybe a new hire over there in your office who claimed that you used Excel all the time and turns out never used Excel watch for this book, will be out about four to five weeks designed for the absolute beginner.
Get stopped by next time, MrExcel.
Learn Excel from MrExcel, podcast episode 1557: “Auto Average”.
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen, coming down the final steps here, writing this book: “Don't fear the spreadsheet.” Now “Don't fear the spreadsheet” is for the absolute beginner.
I like to say that it makes Excel for dummies look like it was written for Excel gurus all right and the way this is working as Tyler who has never used spreadsheets before open Excel and came up with a hundred questions just looking at it from a, hey I've never used Excel before.
I'm going through these questions and some of them just, you know, make me laugh.
You know, so obviously we have AUTOSUM up here right and one of the things I show people a lot is you know it's not just AUTOSUM but there's average count max and min and 1 times questions is hey can I change this button so that we want to click it I'm going to get the average or the count instead of the SUM.
Can I change what the default is, you know, my answer is like, no.
But then I started to think about it, like I wonder if you can.
So I came up here to the Customize Quick Access Toolbar.
I'm sure it's not popular.
I've never even heard of it.
I'ma say all commands and I kind of cruise down here towards the Avs right and there's Average Values and Average.
So I hover to see what the Average is.
Sure enough Home Tab, Editing group, the Auto Sum Average, there it is.
So there is a one-click way to do the Average all right and let's see what's the other one - Count Numbs.
There it is add that and then Max and Min.
Max and min, now unfortunately in Microsoft's infinite wisdom here they made the icon for all four of these be the green crystal ball.
I hate that green crystal ball, it's like you have to be a fortune teller to figure out what the heck it is.
But if I need to Average this now I got four crystal balls up there.
They're arranged alphabetically.
Just hit the first one for Average and we good to go.
No need to open the drop-down.
In fact there let's just talk about, will do this, create large area here, =RAND() and we need to =AVERAGE the whole thing.
So we take the whole set of numbers, one extra row, one extra column hit the =AVERAGE and bam, AVERAGE form does all the way around.
A very very cool feature, one click for AUTO AVERAGE, AUTO COUNT, AUTO MAX and AUTO MIN.
Thanks to Tyler for even coming up with that crazy question.
Okay if you know someone who's completely new to spreadsheets, maybe a new hire over there in your office who claimed that you used Excel all the time and turns out never used Excel watch for this book, will be out about four to five weeks designed for the absolute beginner.
Get stopped by next time, MrExcel.
