Today, with Dueling Podcast #1339, Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen take a look their Newly released book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun". Included in this episode are a few 'How-Tos' straight from the book! Learn a few tricks in Excel and take a look at Mike & Bill's new book!
Transcript of the video:
Well, hey! Welcome back to another dueling excel podcast.
I'm bill Jelen from Mr.Excel and we have live this time Mike Gervin from Excel Is Fun.
Mike, how are you doing?
Doing great.
Bill: All right.
Hey we got a special episode today.
It's not so much about dueling.
We are announcing Mike's brand new book. You can see it there on my screen.
It's called Slaying Excel Dragons.
Mike how's it feel to have a book almost out?
Mike: Absolutely great. It's been a long time.
Yeah, baby start all my years of teaching beginners and also entertaining questions from the working world seeing how people really use Excel.
This book is for the beginner however, the book really tries to tell a story from beginning to end without leaving any holes in the story.
So, for example, sometimes when people talk about formatting, they forget to tell people that states or serial numbers.
So, this book is great for the beginner also great for the expert.
You know, a lot of us kind of just learn on the fly and as we learn we have lots of holes, but hopefully this book will tell a great story from beginning to end about how to use Excel.
Mike: Yeah, you know, that's right as I was going through you know, finishing up the the layout and doing the INDEX.
I was amazed as I would keep finding stuff that I had never known before like, for example, let's say you enter a number hero .25 in your realize, oh! It'd be really good if I would label this number, right?
Well, you know.
A Great way to go up is to hold down Shift when you press Enter and then you're right up there in the, the Label cell, I was like wow that's wild. I never knew that one so even though this is a book that's targeted for the Excel beginner.
It was a book that you know, I got a lot of great little gems from.
Mike: I gotta thank you very much for...
Give me the opportunity to write this book.
Bill: You know as I was reading the book I could tell, I could tell.
So, you teach this stuff quarter after quarter in your class and I could tell the spots where you knew that the students were going to have trouble.
You know, you, you're able to this is something that...
You know, you need to pay attention here or something like that and so, it's not just someone who's using Excel in the real world, but someone who, watches a lot of people who are new to Excel, learn about Excel, and you're able to kind of call out the spots that they need to pay more attention to right?
Mike: Yeah, absolutely You know, like when we talk about percentage number format.
There's six different ways to do percentage number format, and you know, very few people know all six ways, but if you know all six ways, you are not going to get in trouble when it gets to that topic.
Bill: Yeah, this is, this is really good. I you know, I can see where if people to go through the book, what is about 500 pages 550 pages and actually absorb this material.
They will be able to walk in and get a great job or a better job or impress their boss.
There are even a few points in the book where you said you know, hey, during your Excel interview, during your job interview show the guy this trick and he'll fall right out of character and be amazed that you know that trick.
So, just a really good luck. Hey, Mike, let me throw this over to you and why don't you show us a few tricks that are your favorites from the book.
Mike: First thing, I was going to show you is, Chapter 5, Formulas and Functions. You know, we look at right off, I describe what our formulas, and I give you the five types of formulas you might encounter, Calculating, Look up, Text, Logical and Array.
So, again the idea that you get a complete list right off the bat not only that, but Elements, you know most of us think of Equal sign, Cell references, functions, but there's a bunch of things that you can actually put into a formula and let's just start out.
I'll show you an example of a Calculating and a Text formula and again.
This is one of those examples, this is on page 136 to 140.
We have a little data set and you know, in the real world you have a big data set, but we need to count the number of cells above 500.
So, of course, we use the COUNTIF function.
The COUNTIF function and we go ahead for the count if we say hey, here's all the data point comma and then we need to tell it the criteria.
Now, we'd just click right here, we'll just count 500, but we need greater than 500.
So, we're going to put in double quotes a greater than. Now, we have to join that comparative operator to this 500 that becomes greater than 500.
That's an example of a Calculating formula, where we get you know, some sort of number answer if we change this to 300, boom, we get six, but wait a second the label didn't change.
So, we can actually do a Text formula here to have our Data Analysis formula, really amazed anyone who's going to use it.
Now, this, this is all text I went ahead and put it into Edit mode, but you can actually put all this text into a formula and have part of it variable.
So, I'm going to put an equal sign and then double quotes, text always in a formula goes into double quotes, and then again we're going to use that ampersand shift seven that joins.
Now, I'm going to click on this.
So, we've joined two things some text, that's one thing you can put in a formula and ampersand which is the join symbol is another thing and finally, a cell reference.
Now, when we go ahead and change them back to 500, we can see our label and our Text formula and our Calculating answer, both update.
Now, one final detail we could since, if we wanted this format it, we could use the Dollar function.
No way, there's Dollar function, the dollar function takes that number and formats it for a Text formula.
Now, my default the decimals are too. So we're just going to leave that argument number.
Now, so there we have a label and a calculated formula again you know, most people don't think of doing this kind of detail, but if you do that, boy, you got a nice little data analysis set up.
Another one... Yeah.
Bill: Mike, that was, that was another one. I've always used the Text function for that, where I have to put in quotes there's dollar signs 0.00 and what I have read about the Dollar function, that was one where I was like. I've never used that. That's why that's good.
Mike: Well, you know, the the Text function is you've done many many of your podcasts of the year, it allows any number format as you know, this one just is currency and it's actually annoying because it fit me you put a minus here It shows up as a as nothing.
Now, it shows us up as parentheses, so that's where you might want to do the Text function like usually show, but dollar you know, if you got positive numbers, that's a nice little trick.
All right, there was kind of three examples from the book. So, there's, there's...
Bill: What we have. We have the print edition.
It should be out you know, late February at your favorite bookstore. The e-book is available right now on, mrexcel.com, if you haven't...
Mike: It's available right now?
Bill: Right now! It's available right now go go buy it.
Mike: I am going to go to mrexcel.com, right this second and buy an e-book.
Bill: And I'm sure they're going to love it. Mike, I'm glad that we finally, finally got this book to market. I think there were many times where you thought it was never going to happen, but we're here, so.
Mike: No, but it did and you know, couple other things. I'm going to make videos that show the entire book and that's going to be available on a CD through your site, right?
Bill: Yeah, that's right. We're going to get that out. Absolutely Mike: And for teachers who might decide to use this book, it's gonna...
I'm going to have a whole a set of homework problems that you could download and use for each chapter in the book. That'll be out in a couple weeks also.
Bill: Wow! That's wild, that's a great idea.
We'll make it really easy. You know, Mike, one of the things I read in your bio and I love this, you don't just teach Excel, you teach out what Excel and Accounting and Statistics.
Mike: Yeah! No, I teach Statistics, Finance, Math, Accounting, all these Content classes.
So, student signs up and they think they're getting five credits a really hard quantitative class, but then I slap three extra credits of Excel into each class also.
The thing is though Excel makes over calculations so much easier they never mind it and more importantly, all of them know that to get a job in this teenage you have to do Finance and Accounting in Excel not on paper.
Bill: Yeah, that's brilliant. I love the fact you teach all of those classes completely in Excel, All of the homework in Excel, really that's the way that life should be done everything should happen in Excel.
So, that's a perfect scenario there.
Mike: That has got to get all the teachers on the planet Earth not only in business, but in Physics and Chemistry and on and on and on to use nothing, but Excel.
Bill: Well, that's good we want every teacher, teaching Excel to be using this book.
So, if you know anyone out there, who's either trying to learn Excel or who teaches Excel hey, send them, send them our way and they will love, love, love this book.
Mike: Yeah! Well, MrExcel, all I can say is thank you a hundred, two hundred, three hundred percent, and I will format that three hundred percent correctly.
Bill: Excellent there we go.
Oh! hey, I want to thank everyone for stopping by.
We'll see you next week for another dueling excel podcast from MrExcel and Excel Is Fun.
I'm bill Jelen from Mr.Excel and we have live this time Mike Gervin from Excel Is Fun.
Mike, how are you doing?
Doing great.
Bill: All right.
Hey we got a special episode today.
It's not so much about dueling.
We are announcing Mike's brand new book. You can see it there on my screen.
It's called Slaying Excel Dragons.
Mike how's it feel to have a book almost out?
Mike: Absolutely great. It's been a long time.
Yeah, baby start all my years of teaching beginners and also entertaining questions from the working world seeing how people really use Excel.
This book is for the beginner however, the book really tries to tell a story from beginning to end without leaving any holes in the story.
So, for example, sometimes when people talk about formatting, they forget to tell people that states or serial numbers.
So, this book is great for the beginner also great for the expert.
You know, a lot of us kind of just learn on the fly and as we learn we have lots of holes, but hopefully this book will tell a great story from beginning to end about how to use Excel.
Mike: Yeah, you know, that's right as I was going through you know, finishing up the the layout and doing the INDEX.
I was amazed as I would keep finding stuff that I had never known before like, for example, let's say you enter a number hero .25 in your realize, oh! It'd be really good if I would label this number, right?
Well, you know.
A Great way to go up is to hold down Shift when you press Enter and then you're right up there in the, the Label cell, I was like wow that's wild. I never knew that one so even though this is a book that's targeted for the Excel beginner.
It was a book that you know, I got a lot of great little gems from.
Mike: I gotta thank you very much for...
Give me the opportunity to write this book.
Bill: You know as I was reading the book I could tell, I could tell.
So, you teach this stuff quarter after quarter in your class and I could tell the spots where you knew that the students were going to have trouble.
You know, you, you're able to this is something that...
You know, you need to pay attention here or something like that and so, it's not just someone who's using Excel in the real world, but someone who, watches a lot of people who are new to Excel, learn about Excel, and you're able to kind of call out the spots that they need to pay more attention to right?
Mike: Yeah, absolutely You know, like when we talk about percentage number format.
There's six different ways to do percentage number format, and you know, very few people know all six ways, but if you know all six ways, you are not going to get in trouble when it gets to that topic.
Bill: Yeah, this is, this is really good. I you know, I can see where if people to go through the book, what is about 500 pages 550 pages and actually absorb this material.
They will be able to walk in and get a great job or a better job or impress their boss.
There are even a few points in the book where you said you know, hey, during your Excel interview, during your job interview show the guy this trick and he'll fall right out of character and be amazed that you know that trick.
So, just a really good luck. Hey, Mike, let me throw this over to you and why don't you show us a few tricks that are your favorites from the book.
Mike: First thing, I was going to show you is, Chapter 5, Formulas and Functions. You know, we look at right off, I describe what our formulas, and I give you the five types of formulas you might encounter, Calculating, Look up, Text, Logical and Array.
So, again the idea that you get a complete list right off the bat not only that, but Elements, you know most of us think of Equal sign, Cell references, functions, but there's a bunch of things that you can actually put into a formula and let's just start out.
I'll show you an example of a Calculating and a Text formula and again.
This is one of those examples, this is on page 136 to 140.
We have a little data set and you know, in the real world you have a big data set, but we need to count the number of cells above 500.
So, of course, we use the COUNTIF function.
The COUNTIF function and we go ahead for the count if we say hey, here's all the data point comma and then we need to tell it the criteria.
Now, we'd just click right here, we'll just count 500, but we need greater than 500.
So, we're going to put in double quotes a greater than. Now, we have to join that comparative operator to this 500 that becomes greater than 500.
That's an example of a Calculating formula, where we get you know, some sort of number answer if we change this to 300, boom, we get six, but wait a second the label didn't change.
So, we can actually do a Text formula here to have our Data Analysis formula, really amazed anyone who's going to use it.
Now, this, this is all text I went ahead and put it into Edit mode, but you can actually put all this text into a formula and have part of it variable.
So, I'm going to put an equal sign and then double quotes, text always in a formula goes into double quotes, and then again we're going to use that ampersand shift seven that joins.
Now, I'm going to click on this.
So, we've joined two things some text, that's one thing you can put in a formula and ampersand which is the join symbol is another thing and finally, a cell reference.
Now, when we go ahead and change them back to 500, we can see our label and our Text formula and our Calculating answer, both update.
Now, one final detail we could since, if we wanted this format it, we could use the Dollar function.
No way, there's Dollar function, the dollar function takes that number and formats it for a Text formula.
Now, my default the decimals are too. So we're just going to leave that argument number.
Now, so there we have a label and a calculated formula again you know, most people don't think of doing this kind of detail, but if you do that, boy, you got a nice little data analysis set up.
Another one... Yeah.
Bill: Mike, that was, that was another one. I've always used the Text function for that, where I have to put in quotes there's dollar signs 0.00 and what I have read about the Dollar function, that was one where I was like. I've never used that. That's why that's good.
Mike: Well, you know, the the Text function is you've done many many of your podcasts of the year, it allows any number format as you know, this one just is currency and it's actually annoying because it fit me you put a minus here It shows up as a as nothing.
Now, it shows us up as parentheses, so that's where you might want to do the Text function like usually show, but dollar you know, if you got positive numbers, that's a nice little trick.
All right, there was kind of three examples from the book. So, there's, there's...
Bill: What we have. We have the print edition.
It should be out you know, late February at your favorite bookstore. The e-book is available right now on, mrexcel.com, if you haven't...
Mike: It's available right now?
Bill: Right now! It's available right now go go buy it.
Mike: I am going to go to mrexcel.com, right this second and buy an e-book.
Bill: And I'm sure they're going to love it. Mike, I'm glad that we finally, finally got this book to market. I think there were many times where you thought it was never going to happen, but we're here, so.
Mike: No, but it did and you know, couple other things. I'm going to make videos that show the entire book and that's going to be available on a CD through your site, right?
Bill: Yeah, that's right. We're going to get that out. Absolutely Mike: And for teachers who might decide to use this book, it's gonna...
I'm going to have a whole a set of homework problems that you could download and use for each chapter in the book. That'll be out in a couple weeks also.
Bill: Wow! That's wild, that's a great idea.
We'll make it really easy. You know, Mike, one of the things I read in your bio and I love this, you don't just teach Excel, you teach out what Excel and Accounting and Statistics.
Mike: Yeah! No, I teach Statistics, Finance, Math, Accounting, all these Content classes.
So, student signs up and they think they're getting five credits a really hard quantitative class, but then I slap three extra credits of Excel into each class also.
The thing is though Excel makes over calculations so much easier they never mind it and more importantly, all of them know that to get a job in this teenage you have to do Finance and Accounting in Excel not on paper.
Bill: Yeah, that's brilliant. I love the fact you teach all of those classes completely in Excel, All of the homework in Excel, really that's the way that life should be done everything should happen in Excel.
So, that's a perfect scenario there.
Mike: That has got to get all the teachers on the planet Earth not only in business, but in Physics and Chemistry and on and on and on to use nothing, but Excel.
Bill: Well, that's good we want every teacher, teaching Excel to be using this book.
So, if you know anyone out there, who's either trying to learn Excel or who teaches Excel hey, send them, send them our way and they will love, love, love this book.
Mike: Yeah! Well, MrExcel, all I can say is thank you a hundred, two hundred, three hundred percent, and I will format that three hundred percent correctly.
Bill: Excellent there we go.
Oh! hey, I want to thank everyone for stopping by.
We'll see you next week for another dueling excel podcast from MrExcel and Excel Is Fun.
