Skip to content
009: Excel for Accountants with Jeff Lenning episode artwork

Apr 21, 2016 46 min

009: Excel for Accountants with Jeff Lenning

from Learn Microsoft Excel with MyExcelOnline

Jeff Lenning is a CPA and a Microsoft Office Certified Excel Expert & Trainer and who has shown thousands of CPAs and accounting professionals how to use Excel to streamline their work and become more efficient. In 2012 he launched Excel-University.com, a Microsoft Excel Training website for CPAs and Accounting Professionals, has written various publications and published three books about Excel's features. In this episode Jeff tells us how you can save hours during your month-end closing procedures using Excel, expands on his online Excel courses and shares with us his favorite Excel features & tips! 👉 Join Our Academy Online Excel Course 👉 Show Notes: https://www.myexcelonline.com/podcast/009-excel-accountants-jeff-lenning-excel-podcast-from-exceluniversity/

Episode details come from the podcast's RSS feed.

Embed this episode

Ready to play

009: Excel for Accountants with Jeff Lenning

0:00 46:35
of matches

Transcript Auto-generated, may contain errors

This is the MyExcel Online Podcast, episode number nine. Be lazy. Wait, what? No, I'm serious. Be lazy. Because when you're lazy, it means you want Excel to do your work. You don't want to do your work. You want Excel to do your work for you. Welcome to the MyExcel Online Podcast, the need-to-know Excel insights, knowledge, and tips brought to you by the experts that know them best. Are you ready to explore your full potential and get better at Excel? It's time to stand out from the crowd with your host, John Mikaludis.

G'day guys, and welcome to the Microsoft Online Podcast with your host, John McElutis. In today's episode, we have a CPA who is a Microsoft Office certified Excel expert and trainer, and who has shown thousands of CPAs and accounting professionals how to use Excel to streamline their work and become more efficient. In 2000, he launched his consulting company called Click Consulting, and in 2012, launched ExcelUniversity.com, a Microsoft Excel training website for CPAs and accounting professionals. He's written in various accounting publications and has published three books about Excel's features. Let's welcome from South Dakota, Mr. Jeff Lenning. How are you, Jeff? Hey, John. Great, man. Thanks for having me. I really appreciate it.

Mate, great to have you on this show. And I've told our listeners a little bit about your background. Why don't you expand upon yourself personally and your journey to becoming an Excel expert? Absolutely. So basically, when I was in public accounting, everything that we did there was sort of done according to the Dilley method. Are you familiar with that? D-I-L-L-Y, do it like last year? Yes. So I mean, it's also known as like the Sally method, same as last year. And it was just crazy. And so that's kind of how my work progressed in public accounting. But when I really broke out into industry at Gateway Computers, I had to kind of quickly figure things out on my own. And Excel really became my secret weapon because I was able to apply it to my monthly procedures and processes and really just cut many hours out of that process and really streamline it. So that's when I really like got it. And that's when I really understood how to apply.

the Excel features and functions to my life as an accountant and really made things a lot better. So yeah, that was kind of my journey in a nutshell. Cool. And then you set up Click Consulting and after that, Excel University. Now, what did you decide to leave the professional world and go into the online teaching world? Basically, that kind of transition was pretty smooth because I just had developed like a passion for Excel and seeing its power and how it could really impact, you know, my life as an accountant and, you know, just things like able to apply the right features and functions to get our monthly close, which was taking about two weeks, you know, when I...

And by the time I left, we'd gotten that monthly close process down to two days. And that was just by figuring out really how to set up the Excel workbooks in the right way and the journal entries and which features and functions and that kind of a thing. And so just I developed this excitement and this passion for it. So what really started it was. I just wrote an article. I just opened up Microsoft Word. I typed out this article about Excel and I emailed it to the editor of the Journal of Accountancy. And to my surprise, he responded and he said, Jeff, that's great. Let's run it. And so we published that first article called Financial Reports in a Snap back in April 2000. And I got all kinds of emails saying, hey, Jeff, thanks, man. Good stuff, good tips. And so back in 2000, that's when I started to break away from being an employee and open up my own shop and really do the Excel. Really back then it was Excel consulting and development and training. But now 10, 15 years later, my training schedule just so...

busy and so great. I love the training part that I don't do the consulting or development macros kind of stuff anymore. It's really just 100% of my time is fun because I'm just talking about Excel and doing training. So it's a blast. I'm loving every minute of it. So you go around the country giving Excel lessons to accounting professionals, right?

Exactly. And I've been providing continuing education through a variety of different CPE sponsors over the year, national sponsors and things, AICPA and many others. And so a lot of those events had been sort of live over the years, hotel, conference rooms, training centers, stuff like that. And so I would travel a lot, but now just as the industry has shifted to more distance learning, online training, I travel a lot less now. So it's great to be home with the family a lot more instead of being out on the road. And so I'd say 95% of my sessions now are online webinars and on-demand training sessions.

Now, accountants are known to be a little bit dull and boring. I'm an accountant as well, so I can say that. I can say that. When you go to your live seminars, how do you make that session entertaining when you've got 100 CPAs or accountants there? How do you make that session entertaining?

That's funny, man. And by the way, accountants gone wild. It exists, man. When they get into their Excel workbooks and we figure out how to get our bank rec done fast and get our debits to equal our credits fast, it's exciting. But really, I just bring my passion and enthusiasm for the product. And I think the thing that is really interesting is that the training that I do isn't just generic tips and tricks. It's really designed around the accounting person. And so I could never...

go to a conference and teach, you know, engineers or scientists how to use Excel, because I don't know what they do. I don't know what features and functions they need, but I know, you know, what accounts do as my, you know, CPA background. And so therefore I'm able to really apply the right features and functions and show how to set up the workbooks in just the right way. And that's exciting because it's the application of, you know, how do we do the sum ifs function? I understand what it does, but how do we actually use it in our workbooks? And so to really apply the Excel features to, you know, the work papers and reports that we're working on, that builds a lot of excitement because they feel like they can go home now and make their lives better because they can get there.

same amount of work done in such a shorter amount of time that it really is impactful. So yeah, it's exciting. It's good stuff, man. Fantastic. Well, that's all we're here for, buddy. That's why you get to make people's lives a little bit easier and to make sense out of Excel and all its features. All right. Now, you also have a website called exceluniversity.com. And if you type it in there, it's excel-university.com, right?

It is. And what's funny about that is I bought the domain Excel with the dash, xl-university.com first. And then later I got xl-university.com without the hyphen. And so right now that just forwards it. But then the coolest domain I've ever acquired is xl.university. Oh, cool. There's no .com or .net. It's just xl.university. And so even though that's my favorite domain, I'd already gotten everything set up with the hyphen. So any of those three will get you to the same place. Awesome. Fantastic. And how did you come up with the name Excel University? Were you just sleeping and all of a sudden, bang, Excel University, that's the name. Is there a story behind it?

Actually, it's a funny story because originally when I was writing the first draft of the first volume, volume one, the original working title was Excel. It's all about efficiency because I wanted to talk about how to be more efficient and streamline our recurring use workbooks. And so that was kind of the working title. So volume one is your Excel book, yeah? Yeah, volume one is Excel University volume one. Exactly, the book. And so that was the working title. But then I'm a big Dave Ramsey fan. And so he just has this podcast that I listen to all the time. And he talks about ways to eliminate debt and that kind of thing. He's got these sequences of baby steps.

He's really been influential in my life as I've been trying to get my financial house in order, so to speak. And anyway, he has this program called Financial Peace University. And he talks about it all the time. And you can attend there and learn how to just do all these budgets and cash flow and those kind of things. And so I was like, Financial Peace University. I'm like, man.

that is really a cool name. I think it should be Excel University. So as soon as I got home, I logged in, tried to see if anyone else had it. And there is actually a university, I think it's in England, that is Excel University. But other than that, there wasn't much in the way of Microsoft Excel. And so I immediately registered that domain name and it all just clicked. And so, yeah, I'm happy that that's the brand to introduce. I love it. It sounds nice. And that university in England, it's not about Microsoft Excel, is it? No, it's an actual university. You go there and you get your degrees and stuff like that. So, yeah.

Awesome. Good stuff, buddy. All right. Now, you're an expert in Excel for CPAs and accounting professionals. Now, can you just expand a little bit about that and what it entails? Absolutely. You know, people think that Excel is great for accountants because it adds up numbers, right? And it's a good number cruncher. But really, that's not the power of Excel for us accountants because a lot of our work is recurring.

And that is the beauty of Excel because we do the same kind of report, maybe with some different numbers, but the same kinds of activities every day, week, or month, or quarter, or year, all in these accounting cycles and these accounting periods. And so for an accountant, when we invest our time to make these recurring use workbooks dialed in and operate faster, that's when it has a significant impact on our life. And when I was first the accounting manager, and it was taking two weeks to close the books. It was a very stressful time period because there was just so much to do. And I'm just, I was overwhelmed by the amount of time that it would take to get all this stuff done. And so I would work late hours. I'd work weekends, come home. I was cranky, grumpy. I was grouchy because I was just stressed out. And then I said, you know, it can't be like this. And I do the same things over and over every month. So that's when I said, you know what? It's got to change. So I just spent time the first month figuring out how to automate this, literally like this one journal entry. I was going to figure out how to automate. So I figured out the features and functions to get that done. And then next.

month, that journal entry took a little bit less time. So instead of wasting that extra time, I just reinvested it to another report, another workbook, another thing. And so after about 16 months, I could breathe. I had plenty of free time and it was great. No more light nights and long weekends. And yeah, so that's kind of how I got involved with the accounting and making the Excel work better for us. Yeah. With accountants, you do the same thing each month. You do the month and closing every month for 12 months. And it's the same thing over and over again. And I know that a lot of people use, a lot of accountants use Excel and probably spent

lots of hours where they can actually do it quicker and they don't and they don't they just waste hours waste 10 hours on or more three days on a month and close where they can actually reduce it to to one day accountants are heavy users of excel but most of them don't know the shortcuts or they don't know how to insert a macro to make their life easier and and that's what you come in right it is it's not only like the shortcuts and which functions to use it's also kind of bigger picture than that it's almost like um how do you think about excel do you think about it as like just a 10 key or really do you think of it more as a platform for automation? And so I take my students from one mindset all the way over in a very progressive and organized and logical manner. I've spent a lot of time thinking about how to teach it, but then to really think about it as what it really is, it's a platform to automate virtually any data-driven task and reporting task. And so really there's work that I put into taking you through the Excel features in the right order. And the whole Excel University series really is progressive and it builds on itself because that's really what a real world workbook is. It's not just...

oh, just use the subtotal function. It's more like, oh, there's 10 or 15 different things going on in this one worksheet. And so they're hopefully working in harmony together to meet our objectives. So yeah, that's kind of more the way I think about it. And what's an Excel automation feature that you've used in the past to make your life easier when working with month end closers or accounting data? You know, a lot of it has to do with using formula-based reports, using pivot tables, and all of that stuff can save so much time before you even get to macros. It's like...

People just jump right to macros, but there is so much efficiency and automation that can be achieved before we even get to macros. And it's things just like, how do we set up the workbook? We export a trial balance from a big accounting system or an ERP system. We export some transaction listing from a website. And then a lot of times the approach is, okay, then we take that actual export and we spend time reformatting it, deleting columns, inserting columns, adding rows, adding subtotals. And so we actually take that. And then next period, we export that same trial balance and then we manually reformat it. And that's not the right approach. If your goal, of course, is automation.

approaches to split the report from the data so that you're exporting that trial balance into a table on a data worksheet and then formulas, clever formulas, pull the values from that and place it into the exact cells where they need to go. And that way you can just sort of paste and go, you know, you just paste and go and the reports are done. And so, you know, it's things, it's thinking about Excel and those kinds of, those kinds of opportunities. Sounds remarkable. And it's not that hard and you explain it well in your courses. And another way to clean up your data is through Power Query, new feature. And I had Ken Pools on this podcast show in episode number two, and I also attended his

seminar his workshop and man the things that power query does is amazing you can you can put your data you clean it up and just the way that you want to work with the data and then the next month all you got to do is just press a button and it's like a macro you press a button and it gets that information back there again so you don't have to go through all the steps again you set it up once you have all your rules there and it's an amazing new feature and power query is going to be it's going to be huge in the next um in the next couple years Yeah, and back in my day, before we had those kinds of tools, we had to use the basic external data feature of Excel to grab stuff from other data sources. But it's been so amazing to watch the investment that Microsoft has made into the Excel product and all these big BI tools. It really is quite, it's amazing. So yeah, it's fun to see how they're developing the product and continuing to do so. And Microsoft knows that because I know that the users are importing data from external ERP systems and the data they're getting is all messed up. So that's why they brought Power Query out, just to make it easier on the user to do analysis.

Because cleaning up data, it shouldn't take more than a few minutes, half an hour, an hour to the max. And what people should spend the time on is doing the analysis and seeing where the business opportunities are and also, you know, the trends and how they can improve their business. They shouldn't spend time on cleaning up data. So, you know, that's where Power Career comes in. And that's where you come in to explain all the information and how you can clean all that data. All right. Now, you've used Excel for many years. What is the weirdest or maybe coolest thing that you've seen done?

with excel well you know john it is perfectly fine to use excel for personal yeah for personal reasons right so um I love looking around the web for cool workbooks, and I love to bring those into the training sessions that I do. One guy, Tatsuo, he blows me away. He's in his 70s, and he uses Excel to create these amazing pieces of digital art. And, you know, it's funny because in the courses I do, I say, you know, jokingly, but Excel is the only program you need. I mean, it's got its own built-in web browser. Seriously, it's the only program that you need. And, you know, it's pretty funny, but to see what he can do with Excel in terms of artwork, if you haven't seen it, just do a quick Google form. It's absolutely amazing. And to see what guys like Kerry Walken are doing with, you know, building video games inside of Excel.

It's just amazing. So the whole Excel application really is amazingly powerful. And when creative types get in there and mess around with it, it's fun to see what they can do. It's a weird and wonderful Excel world, isn't it? What's the name again for those two people? It's Tatsuo. And I don't know how to say the last name. Horiuchi, H-O-R-I-U-C-H-I. And he's the artist. And in terms of the video game, it's Kerry Walken, W-A-L-K-I-N. He's doing some incredible things. We have links in the show notes. So if you can't type it in now.

Just go to MicrosoftOnline.com slash podcast and you can click on the links and go straight to that page. All right. Now, what do you see Excel in 10 years time? Because Excel's coming out with 2016 now and every couple of years they're bringing new upgrades. We've got the power business intelligence tools. What do you see in 10 years time, Jeff? Yeah, you know, I am hoping that it's still going to be a viable product and still around and that people are still going to need training on it. It's hard to imagine a day when people wouldn't use Excel or digital spreadsheets because there's so many.

places where you need to do analysis in between the gaps. And so, you know, regardless of the number of reports that these online or desktop ERP and accounting systems can create, there's always things that we need to fill in the gaps and do our analysis and things that just these applications don't need. So I would still think it should be thriving and useful and used, you know, especially by accountants as we go through time. But to see what Microsoft has been investing into the product and the changes that they make, I can only imagine it's going to keep improving and keep getting better and easier to use and more powerful. So yeah, I'm excited to see what Microsoft does over the next 10 years. I can't wait.

Never can I. All right, guys, let's take a short break and we'll be back with your course and your favorite Excel tips. Can you analyze this spreadsheet using Excel pivot tables? Sure. If you can show me how to. Introducing the Extreme Pivot Table online course, which teaches you how to analyze business data and highlight key metrics so that you can make insightful business decisions, create interactive analytical dashboards that will reach top management.

and set you apart from your PE, which will make you more important and increase your chances of a promotion or pay rise. Developed over 15 years through extensive professional use and including real business case studies is the ultimate pivot table course. with over 200 short downloadable video tutorials accessible online anywhere 24-7, 12-month personal support if you ever get stuck, and a 30-day money-back guarantee. Visit myexcelonline.com today and give your professional development the boost it deserves. Okay, we're back here with Jeff Lenning from Excel University. Now, you have an online course. It's called Excel University Volumes 1, 2, and 3. Now, what did you decide to create this course? The reason why I decided to create the course is because in the live...

sessions that I do and in the live webinars that I do, I find that getting the exact right pace is a real challenge. And when I have a room of a hundred accountants and I'm talking about V lookups and some ifs and data validation, these things, I just find that it feels like half the class thinks I'm going too slow and half the class thinks I'm going too fast. And so that was a real challenge. And the other challenge, quite frankly, is that Excel is... is big. And there's just, I got a lot to say about it, quite frankly. And so there's these time constraints. And so you feel like you're kind of rushing through things that really you should spend more time on. And so I decided to just kind of start from scratch and write a curriculum from scratch. And so that's resulted in volumes one through three.

And I really spent a lot of time thinking about the right order to introduce the features. And it's built on modern versions of Excel from the get-go. So one of the first chapters talks about tables, and that's our foundation. And then we build on from there. And so the course is designed to be engaging and interactive. A lot of problems with CPE in terms of self-study. So the Excel University qualifies for continuing education under the self-study delivery format. And historically, the self-study is basically they ship out a book.

And the student has to read through the book and just work through the course like on their own. And so I decided to change all that with Excel University because it's lecture videos where you watch pause, replay as needed, work through the lecture videos at your own pace. And then there's a homework section that's a series of engaging homework kinds of activities. So for example, of course, you're going to get the Excel files with exercise one, exercise two, three, and the exercises are very specific. But then there's a lot of other things like polling questions, blog posts, forum discussions, formula challenges, unlockables, achievements. For example, if you get a perfect score on a quiz, you unlock this just fun little video. And so like gamification, I don't know if you're familiar with that term, but it means bringing those kinds of elements that video game developers use into online education. And so I spent a lot of work on that as well. And so to me, I'm so proud of that product. And I really...

believe it's the most effective way to learn excel these days it sounds like a fun course and do you have any bonus rounds bonus rounds well like when you're playing a game you get into a certain stage and you have like a bonus stage actually it's funny because yeah at the end there um if you ace some of the quizzes in the exam then you unlock like these little achievements that are otherwise locked out you can't get into them so those are kind of the bonus and at the end you earn with this badge and so then you can throw that on your resume or link to it on your blog and and so i've done some things like that but yeah good stuff all right so now this is for accounting professionals and also non-accounting professionals. So he qualifies for CPE and you also have the course with non-CPE credit. So anyone can take the course. That's true. And what I discovered is that there are many people who don't need CPE credit.

And so what I did was I created what I call the light version of the course, and that's going to be the same content. So it's going to be the same videos, the same, you know, homework exercises, but without the CPE required components. And so, for example, there's no feedback quizzes, there's no exam, no certificate completion and stuff like that. So it's the same content, same materials, just without the CPE delivery mechanism. All right. Now let's talk about these three volumes briefly. Executive University, Volume 1. What modules do you have in that? Yeah, let's talk about it. And I can make it real brief. The arc or the basic idea for Volume 1 is I'm setting the foundation on which we can build. And so the foundations are going to...

include things like workbook best practices, design principles, tables, named ranges, conditional formatting, data validation, subtotal function, things like that. Things that I think are really going to apply to any accountant's work, no matter kind of what role they're involved in. So that's the foundations volume. And then volume two, we go into what I call formula-based reports or hands-free reporting. And these are going to be things like, how do we paste data into the workbook and have the balance sheet be done?

you know, paste and go done, just hands-free. And so we're talking about lookup functions, VLOOKUP, moving beyond VLOOKUP because of VLOOKUP's inherent limitations, moving beyond it with index match, stuff like that. We're doing some ifs functions. We're doing remove duplicates. We're doing a lot of error checking, list comparisons, mapping tables, all kinds of good stuff like that. And then in volume three, we go to pivot table reports. And so we just spent a bunch of time in volume two talking about formula-based reports. And then in volume three, we compare that with the formula or the pivot table feature. And we reproduce many reports using formulas and then with the pivot table throughout that third volume. And it's super fun to compare those.

side by side and see the strengths and weaknesses of both formula based and pivot table reports. And then we play at the end with some external data, getting data from external data sources, databases, websites, some pivot charts, stuff like that. So yeah, that's basically at a high level what's involved with those courses. And you have a volume four in the pipeline. I do actually. And I got to say, it's the thing that holds together volume four is what I call the work papers volume. And I am so happy with the way that it's coming out. I've just about finished with the draft and all the exercises and all the chapters. So I figured out exactly what I want to say. And now my next few times through is going to be dialing in the language, the verbiage, get it copy edited, stuff like that. So we can release it soon. But in that we go to things like hyperlinks and just really ways to make our analytics and our analysis.

streamline those kind of work paper kind of reports, not like reports, but more like work paper kind of workbooks. So that's really what holds that volume together. And it's going to be great. You're also going to be bringing out more volumes on the track, five, six, seven, and so forth. What's your plan on that? Yeah. So the plan is, and this is cute. So just bear with me, but volumes one through four are the undergrad series, right? That's cute. And then five and six, that'll be sort of the graduate level stuff. And so in there, we're going to do things like macros. We're going to do a bunch of dashboard work. We're going to do, you know, rate formulas, kind of stuff like that. So that'll be, that'll be five and six. And I've got plans up through six at this point.

Man, that's a lot of Excel. And what's the feedback that you got from your students? You know, I got to say it's been overwhelmingly positive and I just get random emails saying, oh my gosh, Jeff, thank you. I just applied something from this chapter to my job and it's like so much better now. And so I've been very happy for this to be my contribution. I mean, there are so many talented and smart Excel trainers and authors, and this is just my little contribution, but I'm happy for it because of the feedback that I've been getting. It's a great course and you guys should definitely check it out, whether you're an accountant or finance analyst, whatever.

getting there some awesome material and some great tutorials. Volume one, two, three is available now and four will be available soon. And there's also books. You've got three books based on volume one, two, three. How was that? Exactly. And really the books are, if you just want to get the book and read it, then they're available on Amazon. If you need the CPE credit and you want sort of the videos and more of a structured way to learn on the schedule, then the online courses teach the content of the books. Where can they find these courses?

These are all available at the XL University website. So, xl-university.com, xl.university, and xluniversity.com. They're all right there. And you click in the upper right, and the course list is the link for that. Do you have any gifts for our awesome listeners? Actually, I do. I have a 20% voucher code for 20% off. All right. And so, that is going to be simple and easy to remember. XL20.

And feel free to post that, share it with your friends. Excel 20 was good for 20% off. So hope that helps. All right, that's Excel 20, the number 20. And you can find the links to this course by visiting the show notes on myxcelonline.com slash podcast. Now, Jeff, our listeners want to know what your Excel favorites are because you've been around the country teaching Excel to many people, a lot of professionals, a lot of accountants, and you've probably come across or learned some cool tips and shortcuts. And in this section here, I want to find out a little bit about that.

Now tell me, what's your favorite keyboard shortcut? John, actually, that's a tough question, but I will tell you the shortcut that I probably use the most is F2, you know, to show the formula and then escape to back out of that. I find that that's probably my number one most used. I don't know if it's my favorite, but I definitely use that one most frequently. All right. What's another one in there? One that you probably love. You know, another one that's right up there is F4 to do the dollar signs and change our sell styles from relative to absolute mixed. So I've got to say F4 is probably right up there for time saving as well. And then just the navigation ones. So it's going to be the arrow keys along with control to do the jumping and then the shift with the arrows to extend your selection. I find that I use those all the time for setting up.

formula, you know, function and range references and worksheet navigation. So those are probably my top favorite keyboard shortcuts. All right. Thanks for that, buddy. Now, what's your favorite Excel tip? My favorite Excel tip has got to be...

use the table feature, man. It was rolled out in 2007 and it's the way to store data in your workbooks. And we don't want to store data in ordinary ranges. Whenever possible, we'd rather convert those into a table and use the auto expansion feature of tables so that we don't have to continually rewrite our formulas. And it's part of that paste and go strategy. If we can store the data in a table, next period when we paste the new transactions and there's 10 or 100 additional rows, as long as we use the structured table reference to refer to those values in our formulas, we don't have to rewrite the formula. So that will save us time.

And also, it's a great way to update your pivot table without extending the range reference. Absolutely. And that works for storing data for pivot table data sources, charts, formulas, pretty much everything. It's great. Man, I love Excel tables. Whether I'm using two rows of data or thousands of rows of data, I always put into an Excel table. And also because I like the colors that come up there. I put in blue, I put in red. It just lines up that dull workbook. Exactly. Everyone should start using Excel tables. If you have a set of data, click on it, press Control-T.

Press OK and bang. You'll come to the wonderful world of Excel tables. Jeff, what's your favorite function and why? You know, in terms of Excel functions, I have got to say that my favorite Excel function is the SUMIFS. And that's S-U-M-I-F-S. It's that plural of SUMIFS. SUMIFS has been around for a long time, and it lets us do one condition summing. Beginning with Excel 2007, Microsoft rolled out the SUMIFS function, and that is a multiple condition summing function. And it totally revolutionizes our formula-based reporting workbooks. It changes everything. It's amazing. It is an amazing function.

Apart from your blog, what's another great online resource for learning Excel? My recommendation for learning Excel online is really try to find industry-specific training. And here's why. Microsoft has done an amazing job of making Excel relevant to all kinds of different disciplines and all kinds of different industries. And so, you know, like I said earlier, I don't know what engineers and scientists need. So really, I think that to find the training that isn't generic, but is specific to the stuff that you're working on, I think that's the big key to see how to apply which functions to your actual work and how you're using it. To me, I think that's the key. All right. And also a blog that you always read.

You know, as opposed to just following many Excel blogs, I think I'm just more of a generic Google guy when I try to find a specific question. And there's so many amazing blogs and amazing writers out there. It's really hard to just kind of recommend or just pick one. So I'm just gonna have to say the generic Google search, man. That's how I get around. Excel, google.com. I should get that web name.

That'd be awesome. And I reckon, I truly believe that most of Excel users go to Google to find their answers and then they direct it into the right path or the right blog. So yeah, thank goodness for Google. All right, Jeff, if you could recommend one Excel book for our listeners, what would it be? Oh, I will tell you a couple of books. I know you said one, but there's just two. One is an actual Excel book and the other one is not, but I'll just put it out there anyway. But my favorite Excel book is from John Walkenbach and it's the Power Programming, the VBA book that he puts out. It's big, but it is amazing because...

I learned something on virtually every page of that book. It's an amazing resource. Take me all the way from just little baby beginning macros all the way through to custom add-ins and building your own add-ins and how it all fits together. And I have to say, Been through that book twice because it's so good. So that's my top. And then the other one isn't an Excel book, but it really helped me figure out when I'm doing a data visualization kinds of things, how to really set that up in Excel. And it's Stephen Fuse, Information Dashboard Design, Volume 2. Grab a copy on Amazon. But I love that book. I learned so much. And again, it wasn't like the Excel mechanics as much as just the general ideas. So those would be a couple of resources that I would.

Definitely recommend. Man, that's awesome resource there. Now I'm always buying Amazon books and look, I don't have those books and I've always been meaning to buy them. And a lot of people have said about John Walkenbach's programming book and it's just super, super incredible. So I'm going to buy that. I'm going to also get Stephen Hughes' book because a lot of people have said that that's a must-have book on your desktop. All right, guys, you can find the links to these books and everything we've been chatting about today, including tutorials on Jeff's favorite shortcuts and tips by visiting myxonline.com slash podcast.

Okay, Jeff, we've come to the end of the show and I've got an Excel scenario for you. Now, let's say that I'm an Excel beginner or intermediate user and I need to learn the most important features of Excel within a week for an important job interview. Now, with all your vast Excel knowledge and all the online and offline resources available, what will be the quickest way for me to start learning Excel and preparing for this important job interview? Well, question, are you going in for an accounting position? Yeah, I know where you're going with this. Yeah, it depends. But let's say that, yes, okay, I'm leaving my financial analysis job and I want to go into an accounting job.

Okay. Well, I mean, obviously, Excel University is a great resource, but setting that aside, I think that the best way to learn Excel and the fastest way to learn Excel is by hands-on doing it. And it's one thing to read a book or watch a YouTube video or something like that. That's great, but it's the practice. And so I'd recommend doing these hands-on kind of exercises. And that is the way to really quickly develop a core Excel proficiency. That is super, super important what you've just said. And that is how I learned Excel.

I look, I visualize, and then I apply it. And I make a mistake. And then I get frustrated. And I apply it again. And then bang, on the third go, it sticks. And then the whole concept sticks. And to all the listeners out there, that is the quickest way to learn Excel is to apply it. If you watch a YouTube video, you're just going to be watching. And then you think, okay, you've got it. No, you don't. You've got to actually open your workbook, do that formula or apply that pivot table, and then you will get it. So that's a pretty, pretty incredible tip and one that everyone should use.

Now, what's the best Excel feature that I should start learning in order to prepare for this job interview? Well, you know, there are so many different features and there are so many different ways people use Excel that my best advice there is, John, to be lazy. Wait, what? No, I'm serious. Be lazy. Because when you're lazy, it means you want Excel to do your work.

You don't want to do your work. You want Excel to do your work for you. And that means you're going to take the time that it takes to figure out which feature and which function that you need to use to automate this task and to get Excel to do it. That means we're delegating our manual steps to Excel. So, John, be lazy. Oh, man, my wife calls me lazy all the time, so I am lazy.

All right. Okay. Now, I get your point and you have to be lazy. You have to be lazy. You have to make Excel work for you. Spit out the numbers. What would you recommend as one of the best features to go into prepare for this interview? Yeah, you know, you're really going to need to know how you're going to use Excel. But I will say this, that in interviews, you know, kind of...

the default go-to to measure sort of one's Excel proficiency is the VLOOKUP function. And it's kind of like this. Oh, yes. Okay. Do you know Excel? Yes, I know Excel. Okay. Do you know VLOOKUP? It's just kind of strange, but that's kind of a measure of one's Excel proficiency these days, as wrong or as right as that may be. So I would say you got to get comfortable with the lookup functions of Excel. These are going to be VLOOKUP index and match. You're going to need to be comfortable with the tables feature.

And you're going to need to be comfortable with pivot tables. That's another way that interviewers might gauge your level of proficiency is, hey, do you know how to use pivot tables and things like that? So, yeah, those are my thoughts there, man. And be lazy. All right. Now, the interviewers, most of the times, don't know anything about Excel. That's why they're hiring people there. So if you can talk the talk, I'm sure you can get the job. Don't you agree? You can get the job. I don't know if you can keep the job, but yeah, I think you can get the job. Well, the first thing is to get the job. Then they're going to go and enroll in one of your courses. And I'm sure that they're going to be able to keep the job.

after going through your volumes one, two, and three. So no worries about that. Mate, what's the best way for our listeners to connect with you? Hey, definitely subscribe to the blog, xluniversity.com slash blog. And that way you'll get the posts delivered fresh to your inbox. Also hit me up on LinkedIn. I would love to be your Excel friend on LinkedIn. So definitely connect with me there. I would just love that. Thanks, buddy. You're bloody amazing. You've done a lot of great work over the years and have helped a lot of people. So I've got to say thank you for that. And you're continuously bringing out new courses, which is just going to make people.

better at excel and make them more efficient at their work and hopefully get them a better paying job or at least they can go home early so thank you for your great website your blog your courses and mate all the best with volume four and one day i want to get you back on this show to talk about that next volume maybe volume number 10 how about that that'd be awesome Really appreciate your invitation to be here, man. And you're doing good stuff too. So keep up the good work. And absolutely, let's come back online when volume four is done. That would be super cool. We'll have a giveaway. I can give away some signed copies of it. It'll be a whole big party thing. It'll be great. All right, buddy. Sounds remarkable. Thank you very much, my friend. And we'll speak soon. Thanks, John. Bye. Cheers, buddy.

Hey guys, I hope you enjoyed this podcast show. Thank you for listening. Now, I need your help. I need you to please leave an honest review for the MyExcelOnline podcast on iTunes. Now, ratings and reviews are extremely helpful and greatly appreciated, and I read each and every one of them. So if you go onto iTunes and search for the MyExcelOnline podcast, you can leave a review and also subscribe so you can receive all the new episodes as soon as they come out. Also, if you go over to my blog at MyExcelOnline.com slash blog, you can subscribe to receive weekly Excel lessons for free. Now, these are going to be delivered to your inbox each and every Friday, containing a lesson on formulas, pivot tables, charts, and...

analysis. By signing up, you also get a copy of my free Excel ebook called The Ultimate Excel Resource Guide. In this ebook, I show you and review the best Excel blogs, the best Excel YouTube channels, the must-have Excel forums, for you to succeed. I handpick and review the best Excel online courses. I'll show you the various Excel books that are available. And I'll also explain the different time-saving add-ins that you must have in order to get the full potential out of Excel. Plus I have much more information. Now this ebook is jam-packed full of value and it's free if you sign up to my weekly Excel lessons. So go over onto my Excel blog and subscribe to receive your free ebook and also get your awesome weekly lessons. All right, until the next episode, keep excelling.

We hope you enjoyed this episode of the My Excel Online podcast. Be sure to subscribe and share this with your colleagues so they can reach their full Excel potential. You can also visit myexcelonline.com slash podcast for show notes as well as John's online Excel courses and free tutorials to help you stand out from the crowd. Until next time, keep excelling.

Frequently asked questions

How long is this episode of Learn Microsoft Excel with MyExcelOnline?

This episode is 46 minutes long.

When was this Learn Microsoft Excel with MyExcelOnline episode published?

This episode was published on April 21, 2016.

Is there a transcript available for this episode?

Yes, a full transcript is available for this episode. You can read the complete transcript on the episode page.

Can I download this Learn Microsoft Excel with MyExcelOnline episode?

Yes. Use the download control on the episode player to save the publisher-provided media file.