This is an Eval Central archive copy, find the original at depictdatastudio.com.
I recently had the honor of speaking with Bill Jelen, a.k.a. Mr. Excel. He was a guest speaker inside our data analysis course, Simple Spreadsheets. He’s a big, big deal in the Excel community! I’ve internet-stalked him for years on his website and his popular YouTube channel. Frankly, I’m still shocked he agreed to come speak with us.
Watch Our Conversation Here
Here are some of the topics we talked about.
- Bill’s background. Bill’s a self-proclaimed “Excel geek” who’s written over 61 books and has more than 2,300 YouTube videos on his popular channel. He’s been an Excel consultant for 30 years and used to speak at more than 35 conferences a year. Though he doesn’t travel outside of Florida as much anymore, he admitted that he loves helping people problem-solve and that “nothing brightens my day more than a good Excel problem.”
- Bill’s first spreadsheet experience. Bill shared that his first time with a spreadsheet of any sort was in 1984 while he worked for a company that sold computer software. He also used Lotus 123 until the mid-1990’s when the company he worked for switched to Excel.
- His favorite Excel feature. His favorite Excel feature is to double-click the fill handle to avoid having to drag it. He said that when loves hearing people’s surprise when he shares this trick at conferences.
- His thoughts on Excel Tables. Bill hates Excel Tables because they turn off features that he uses all of the time like subtotals and custom views. He also dislikes that you can’t copy two sheets from one workbook to another.
- His least favorite Excel features. He said that he’s “convinced in Excel 2007 the icons for rows and the icon for columns were reversed.” He said it shows the wrong area where the data is going to be dropped and that it’s really frustrating, especially when teaching someone else how to do pivot tables. He also said while he loves the text join function, he hopes they’ll add the reverse: text split.
- How many of the more than 480+ Excel functions he uses (less than you think!). Bill said, “I know them all, but how many do I use? Very few. I probably use 20 on an ongoing basis.” He said that he’s written books where he wrote about every single function and admitted that some threw even him for a loop, like the square root of PI.
- How to use text join. At around the 18-minute mark of the video, Bill shares his screen and demonstrates text join in Excel along with other tricks and best practices.
- Audience Q&A. At around the 26-minute mark, audience Q&A begins where Bill answers questions.
- AutoFilter. Bill said he feels AutoFilter is a hidden gem of Excel and that he “can’t use Excel without this feature.”
- Excel features Bill says to skip. Bill said you can skip using the action pen, which allows you to draw the numbers instead of using the keyboard you already have. He’s also not a fan of the Excel app feature that allows you to take a picture of a piece of paper and then it turns that into a table. He said there are too many variables (for example, if your paper isn’t exactly square or has been folded) and that you’re better off starting from scratch.
- Features he hopes to see in the future. Bill said that he “has a whole bunch of things” he’d like to see. He said that he lobbied for six years for Microsoft to customize pivot table defaults. He hopes to see help functions for Lambda in the future and has some ideas of how to make power query better.
- His latest book. Bill’s latest book is MrExcel 2021: Unmasking Excel, which is an updated version of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference. Click here to learn more: https://www.mrexcel.com/products/mrexcel-2021-unmasking-excel/.
Connect with Bill