A Cheat Sheet to get more out of Microsoft Excel
Ever wanted just the Cliff Notes version of Excel? We've got you covered with the Excel Power Users Quick Guide - and it's free.
Mike Excelisfun Girvin author of a book on Excel array formulas joins Excel TV host Rick Grantham and cohosts Microsoft Excel MVP Jordan Goldmeier and author and Microsoft Excel MVP Oz du Soleil on Excel TV.
Rick: Our Excel TV YouTube channel has been blowing up since Mike Girvin announced he was going to be on our show. Thank you, Mike Girvin, author of Slaying Excel Dragons and Control + Shift + Enter Mastering Excel Array Formulas. Mike has over 2000 YouTube videos. Say hello, Mike.
Mike: All the way from Des Moines, Washington near Seattle, at Highline Community College where I teach.
Jordan: Is this happening during a class?!
Mike: This is the greatest invention. Excel TV should have been invented a long time ago! What else do you do on a Saturday night but watch Excel TV?
Rick: Do you mind telling everybody a little bit about your story? How did your YouTube channel start off before it became a juggernaut? How did you go from teaching at a community college to a YouTube star?
Mike: A lot of hard work. I started back in 2008. I noticed there were some Excel videos by the cat videos on YouTube. I was already making Excel videos for my statistics class so I posted them to YouTube. It started to steamroll. I started to create playlists off videos. Then my Highline class videos. I created workbooks to go with the videos and notes. I spent a lot of time editing.
Rick: You’ve written a book on array formulas. Oz is on record during our Chandoo interview saying array formulas freak him out. Oz, do you have any questions for the guy who’s writing the book on array formulas?
Oz: I tried to buy the book but it is back ordered two months. Once in a while, I ask for help on an Excel forum and an array formula solution comes back. I used them, but they weird me out.Click To Get Mike’s Book
Mike: An array is when you are doing an operation on a bunch of items instead of just one. It just takes a matter of time to learn. I wrote the book about array formulas but there are people smarter than me about array formulas. The only reason I wrote the book is because there wasn’t one available. You can’t go to Excel help and find out anything about array formulas. In the beginning part of my book, I thank everybody from the Mr. Excel message board, Aladdin, Dominique, Don Quixote, PJ on and on. You start doing it and you will get it.
Oz: That’s right.
Jordan: Do you remember your first array formula that got you into the whole thing?
Mike: Pretty much! It was a data extraction. One of the common problems in Excel is that if you look something up and you want to return multiple items, there’s no built in function. You have to switch to an array formula. It was Aladdin and Dominique and a few others that helped me with my first array formula. I’m not a smart guy I just work hard!
Oz: You don’t do VBA code?
Mike: Yes. I can record macros and hack them and I use VBA but I’m not a code writer.
Oz: I appreciate knowing that. How did you feel compelled to go in the array formula direction instead of VBA?
Mike: I’m doing mostly statistics, finance and accounting. There are a lot of calculations and there are arenas where you have to do array formulas. The array formulas work when I am doing data extraction and I want to automate.
Oz: What do you call data extraction?
Mike: You have a data set and criteria for input. If you don’t want all the data, you set up the criteria to pull in the data you want. It’s a filter.
Oz: Right, left, mid filtering and all these technical functions. I’m trying to narrow down when you say data extraction. It could be 1000 cells and you need to fish out the content of only 10 cells. Or you could be talking about pulling things out of a PDF file where you have multiple items in one cell and you need to pull out only part of it out of the one cell.
Mike: With data extraction, you have a proper data set with records and you want just some of the records. But there are lots of non-array and array formulas where are you extracting data from a cell.
Oz: I’m looking forward to the book being updated so I can expand into the data extraction area.
Jordan: As someone who works with array formulas, what is your feeling with the Microsoft Excel error that happens when you try to change the array formula? Do you like the error that pops up and says you can’t change that?
Mike: I don’t know what you’re talking about?
Rick: Only you get that error, Jordan!
Mike: Oh, I gotcha. There are different ways to classify array formulas. One is a single cell where you get a single answer. There are other array formulas that deliver multiple answers and you’re delivering it to multiple cells. In that case, when you control + shift + enter, you cannot change one of the cells. You have to change them all. An array formula, at its essence, makes an array calculation and returns multiple items. So when you have multiple items, you can do an aggregate calculation on them, which means the array formula will go into a single cell. Or you can have multiple items delivered to multiple cells. And that’s where you get the error. When you are entering a single item even though it’s displayed in multiple cells. You are not allowed to change it.
Jordan: I want to resize it smaller. But if you grow it, you can’t resize it. You have to get rid of it and start over. Aside from that, I love array formulas. I love the control + shift + enter formulas. A lot of people are VBA focused, but the formula focus is very strong. I like it for Excel products because you can make one change and everything is updated automatically. That’s why I prefer them.
Mike: Not all array formulas are control + shift + Enter. The definition of an array formula is not that it requires control + shift + enter. It is that there is an operation on an array of items and when you evaluate it, it delivers multiple items. There are some really complicated array formulas where you just hit enter.
Oz: Do you cover that in the book?
Jordan: Which books are those? Ctrl+Shift+Enter Mastering Excel Array Formulas: Do the Impossible with Excel Formulas Thanks to Array Formula Magic by Mike Girvin.
Mike: The subtitle covers advanced formulas that are not array formulas. The book has a whole bunch of other stuff.
Jordan: Everyone should go get this book.
Oz: Eric Zeolla says thanks for sharing with the community!
Oz: Crystal Long has the answer to the Excel challenge and Isaac Girvin says he doesn’t have to go to bed!
Mike: My son is watching?! Isaac has four Excel videos posted to YouTube already by the age of six.
Rick: Nice! Can you talk about how you found a publisher? And how did you go from making the videos to your first book?
Mike: I was having fun at YouTube and Mr. Excel Bill Jelen contacted me. We were a natural combo. So we started making dual videos and he asked if I wanted to write a book. I said sure. I’m not very good at writing. I can’t spell. I can’t type. I’m not a very good book writer! It is difficult and painful for me. I like organizing, but the writing was near torture.
Oz: I want to go back to why you first started videos. People are in college and they’re learning this stuff and using the software of the college, but when they get out and get a job at firm there is no math lab to help. There is only Excel and they may not know Excel.
Mike: Before I started posting videos at YouTube, I was making videos and posting them on our college website on how to do statistics in Excel. I teach basic finance, accounting, and math. My goal is to teach everyone in class and the computer lab Excel. When you get out of college, you are wired to do it in Excel. My duty as a teacher is to prepare a student to work in the real world. So you have to teach statistics in Excel. That was the impetus to go to YouTube and put my classes there. YouTube was the best way to deliver lectures to the students.
Rick: I interviewed Bob Umlas and he mentioned you. Mynda Treacy mentioned the dueling podcasts with Mr. Excel. It’s pretty popular. Would you mind talking about how that came about? And for the people that might not be aware of it, explain what it is?
Mike: The number one influence has been Mr. Excel Bill Jelen. My second influence is the Mr. Excel forums. But I was reading Mr. Excel’s books and that is where I got the idea to post the videos. It was all from Mr. Excel. I started posting videos and he noticed me and contacted me. And we have a natural fit. The dueling podcast is where there is a question on Excel. Mr. Excel solves it one way and I solve it a different way and we go back-and-forth. It is amazing fun and the highlight of my week. You guys at Excel TV, ExcelIsFun, all of the amazing YouTube channels and video Excel channels are all because of Mr. Excel.
Jordan and Rick: Absolutely.
Jordan: Thank you, Bill! (Group hug!)
Rick: That is a great way to end the segment with homage to Bill Jelen.
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.