At the 2015 MVP Summit, I caught up with Mike Girvin and his 9 year-old son Isaac shows his 4th grade classmates how to create a multiplication table. In this video, you will learn about F4 Roulette, MMULT function, a great array function, and more.
Transcript of the video:
Bill: Hey, welcome back, live from Highline Community College, it's time for another Trueling excel podcast. I'm Bill Jelen from MrExcel, we're joined by Mike Girvin from Excel Is Fun, and, how old are you now? Isaac: Nine.
Bill: Nine year old Isaac excelisfunner! Our problem today: The kids in West Seattle Montessori fourth grade class need a multiplication table.
Isaac show us what you've got!
Isaac: So, everybody's too lazy to do math problems nowadays, because we all got our smart phones and all that stuff. So, we're just going to quickly type 1 and 2. We're going to highlight both of them, we're going to make an angry rabbit, AKA +, and then we're going to go to 12 for now, but then I'll show you how to do it a different way, get every single answer. Then you go like that again, do it sideways, highlight it, and then go all the way to M if you're doing 12, and then you're going to go do =, Up arrow, going to press F4, you can hold this down, make hare-brained(?) letters, but. YES I got it on the right one!
So, you got it a... Bill: F4 Roulette! Mike: Merry-Go-Round! Isaac: Yep! You got to make sure it's in the right place. So, you got to make the one, remember this is the first digit, well not if you're on negative numbers, I guess it's not. But, a $ sign goes in front of a number for the first one, and then you got to press the * symbol, and that means, well, times! In school we use this (X). And then you got to go A2, or you could press left arrow, and then you got to do the merry-go-round, until you get to not that, this! Alright, and then we got to do CTRL+Enter, and it shows up as 1! Yeah, I know you can just type that, but this is where the stuff gets MAD! I'm going to double click this, and this happens.
Yeah, I just blew your mind! And then I'm going to show you how to do it awesomely!
Alright, this is how we go to 100 fellas?
Bill: What letter is the 10th letter? Isaac: Let me see, CW, that's actually what I said earlier! Mike: Wow, how'd you know that? Bill: You looked at the tooltip. Mike: Oh yeah!
Bill: He saw it in a great video from excelisfun!
Isaac: Oh gosh, I went way over. Alright well, it's around here, 101. So we got to go right here, got to do the same thing, =B1, F4, F4, and then do A2. We got to do the merry-go-round, until you get to that, press the CTRL+Enter, and you have to drag it all the way over here, until you get to 100. Check that out! That's awesome! Mike: That's a lot of multiplying.
Bill: OK, there we go. Well hey, you know, I got a story to tell you: When my kids got to multiplication table, I taught them how to use Excel to make their homework go away, right, they were getting their homework done in 10 minutes. Of course, mom was at the grocery store or something like that, alright. But math homework went really, really good, they got A's on all the math homework, and then, when we got to the test, it was really bad, because they didn't have Excel on the test!
Mike: They didn't have Excel on the class- they weren't taking a class from me! Bill: Clearly they didn't have you!
Alright so, I'm going to do the same thing, I put the number 1 there, I'm going to hold down the CTRL key though, that way I don't have to put in the 2 and the 3 and the 4, and we'll go over to column M to get the numbers 1 through 12. I'm going to copy that, and do Alt E S E, Edit, Paste Special, Transpose, to make those go down the side! Alright, and instead of the great formula that Isaac put in, we are going to do, I'm going to select the whole range, the whole multiplication table, and put one single formula in. So it's =MMULT, stands for matrix multiplication.
Array 1 and array 2, the first array is this array over here, comma, second array is this array over here, ) to make the whole thing work, CTRL+Shift+Enter! BAM, there it is!
Let's even have a little bit of Conditional Formatting. So we can see those color scales as the... Isaac: Weird! Mike: That is amazing!
Bill: That was fun, let's see what you got!
Mike: Wow, I'm going to have to do that, what's that called? Conditional Formatting, that is too cool! Hey, I'm going to come down here, I'm actually going to have a start number.
So I'm going to say Start, 1, add the Increment, and we're going to make the table totally adjustable, right? So I'm going to start up here, equas that one right there, and I'm actually going to lock it in all directions, and the next form is going to be "Hey, look one cell above, plus the increment locked." F4, CTRL+Enter. And I'm going to copy it down, and it looks like I got 12 now. I'm going to copy this and I'm going to cheat, I'm going to copy MrExcel, I'm going to come up here.
These are formulas, I'm going to right click and go to Transpose, and BOOM, those formulas actually transpose! Now I'm going to highlight the whole inside, and I'm just going to do an array formula copy from(?) MrExcel, but without the MMULT, right? So CTRL+Shift+down arrow * up arrow, CTRL+Shift+right arrow, and then CTRL+Shift+Enter! Now, because we've linked these factors, 1-12, 1-12 down here, if I simply change this to 2 as the starting number, BOOM, everything updates. The increment is 2, now I have a multiplication just for 2s! CTRL+Z, multiplication tables, that's a lot of fun!
Bill: Alright well, there you go, this is our Truel number 6, right? Or Dueling Excel podcast #174! Mike: That's coming up next!
Bill: Oh, alright! Join us next time for another Trueling Dueling Trueling Excel podcast from MrExcel, excelisfunner and... Mike: excelisfun!
Bill: Alright!
Bill: Nine year old Isaac excelisfunner! Our problem today: The kids in West Seattle Montessori fourth grade class need a multiplication table.
Isaac show us what you've got!
Isaac: So, everybody's too lazy to do math problems nowadays, because we all got our smart phones and all that stuff. So, we're just going to quickly type 1 and 2. We're going to highlight both of them, we're going to make an angry rabbit, AKA +, and then we're going to go to 12 for now, but then I'll show you how to do it a different way, get every single answer. Then you go like that again, do it sideways, highlight it, and then go all the way to M if you're doing 12, and then you're going to go do =, Up arrow, going to press F4, you can hold this down, make hare-brained(?) letters, but. YES I got it on the right one!
So, you got it a... Bill: F4 Roulette! Mike: Merry-Go-Round! Isaac: Yep! You got to make sure it's in the right place. So, you got to make the one, remember this is the first digit, well not if you're on negative numbers, I guess it's not. But, a $ sign goes in front of a number for the first one, and then you got to press the * symbol, and that means, well, times! In school we use this (X). And then you got to go A2, or you could press left arrow, and then you got to do the merry-go-round, until you get to not that, this! Alright, and then we got to do CTRL+Enter, and it shows up as 1! Yeah, I know you can just type that, but this is where the stuff gets MAD! I'm going to double click this, and this happens.
Yeah, I just blew your mind! And then I'm going to show you how to do it awesomely!
Alright, this is how we go to 100 fellas?
Bill: What letter is the 10th letter? Isaac: Let me see, CW, that's actually what I said earlier! Mike: Wow, how'd you know that? Bill: You looked at the tooltip. Mike: Oh yeah!
Bill: He saw it in a great video from excelisfun!
Isaac: Oh gosh, I went way over. Alright well, it's around here, 101. So we got to go right here, got to do the same thing, =B1, F4, F4, and then do A2. We got to do the merry-go-round, until you get to that, press the CTRL+Enter, and you have to drag it all the way over here, until you get to 100. Check that out! That's awesome! Mike: That's a lot of multiplying.
Bill: OK, there we go. Well hey, you know, I got a story to tell you: When my kids got to multiplication table, I taught them how to use Excel to make their homework go away, right, they were getting their homework done in 10 minutes. Of course, mom was at the grocery store or something like that, alright. But math homework went really, really good, they got A's on all the math homework, and then, when we got to the test, it was really bad, because they didn't have Excel on the test!
Mike: They didn't have Excel on the class- they weren't taking a class from me! Bill: Clearly they didn't have you!
Alright so, I'm going to do the same thing, I put the number 1 there, I'm going to hold down the CTRL key though, that way I don't have to put in the 2 and the 3 and the 4, and we'll go over to column M to get the numbers 1 through 12. I'm going to copy that, and do Alt E S E, Edit, Paste Special, Transpose, to make those go down the side! Alright, and instead of the great formula that Isaac put in, we are going to do, I'm going to select the whole range, the whole multiplication table, and put one single formula in. So it's =MMULT, stands for matrix multiplication.
Array 1 and array 2, the first array is this array over here, comma, second array is this array over here, ) to make the whole thing work, CTRL+Shift+Enter! BAM, there it is!
Let's even have a little bit of Conditional Formatting. So we can see those color scales as the... Isaac: Weird! Mike: That is amazing!
Bill: That was fun, let's see what you got!
Mike: Wow, I'm going to have to do that, what's that called? Conditional Formatting, that is too cool! Hey, I'm going to come down here, I'm actually going to have a start number.
So I'm going to say Start, 1, add the Increment, and we're going to make the table totally adjustable, right? So I'm going to start up here, equas that one right there, and I'm actually going to lock it in all directions, and the next form is going to be "Hey, look one cell above, plus the increment locked." F4, CTRL+Enter. And I'm going to copy it down, and it looks like I got 12 now. I'm going to copy this and I'm going to cheat, I'm going to copy MrExcel, I'm going to come up here.
These are formulas, I'm going to right click and go to Transpose, and BOOM, those formulas actually transpose! Now I'm going to highlight the whole inside, and I'm just going to do an array formula copy from(?) MrExcel, but without the MMULT, right? So CTRL+Shift+down arrow * up arrow, CTRL+Shift+right arrow, and then CTRL+Shift+Enter! Now, because we've linked these factors, 1-12, 1-12 down here, if I simply change this to 2 as the starting number, BOOM, everything updates. The increment is 2, now I have a multiplication just for 2s! CTRL+Z, multiplication tables, that's a lot of fun!
Bill: Alright well, there you go, this is our Truel number 6, right? Or Dueling Excel podcast #174! Mike: That's coming up next!
Bill: Oh, alright! Join us next time for another Trueling Dueling Trueling Excel podcast from MrExcel, excelisfunner and... Mike: excelisfun!
Bill: Alright!
