This is the MyExcel Online Podcast, episode number 11. As users of Excel, no matter what level you're at, you're going to have to explain your worksheet to somebody else at some point, whether you're training them, whatever it is, you have to be able to explain that, even if you're not using BBA, to a user base. So that kind of communication can really help you in your career as well. 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 Mika-Lewis.
G'day guys, and welcome to the MyExcel Online Podcast with your host, John Michaloudis. In today's episode, we have an Excel MVP who is an expert in VBA. He's been using Excel in corporate finance roles for over 10 years and develops VBA applications that help solve problems and save time. He has a website called excelcampus.com, which has been running for more than five years. There, you can find in-depth blog posts on different Excel features, several of his time-saving Excel add-ins, as well as his newly created courses on VBA and Lookup. Let's welcome from Orange County in the US, Mr. John Acampora. Hello, John. How are you, buddy? Hey, John. Great. How are you doing?
Good. Thank you. How is the weather in California? Oh, I can never complain. Probably make people jealous. It's wintertime here and it's sunny and warm. So yeah, but thanks for having me. Yeah, no, it's good to have you on here, buddy. Thank you for accepting the invitation. And I know our listeners are going to get a lot of value from yourself and your expertise on your website. Now I've told our listeners just a little bit about your background. Why don't you tell us more about yourself personally and your journey to becoming an Excel and VBA expert?
Yeah, sure. Well, I think my journey started just right out of college. I majored in finance and got an accounting job right out of college. And the first company I started working for just did everything in Excel. It was a small company. And they basically just did everything you could think of in Excel. Inventory control, AR, AP, accounts receivable. Basically using Excel as a communication tool as well as just all these management tools for managing data. So I was really inundated with it just right out of the bat and got good at it. The people that worked at that company were very good at Excel. So they really taught me a lot about how to use it, how to use keyboard shortcuts.
all that kind of stuff. So that really helped me kind of get my start was just really just using Excel all day, every day, just not getting away from it. But then, you know, after that job, I went on to other jobs and other roles and realized that companies, just about every company I've worked for has relied heavily on Excel for day to day operations. It's just something you can't get away from, which is a good thing, but also made me realize that it's really important to learn Excel and to kind of master some of these techniques and also save time doing it because we can really. kind of get lost in some of these tools within Excel. And sometimes they take us a lot of time to do a very simple task. So it's good to work efficiently and also find ways to automate things. So that's how I got into VBA was just, I get really bored doing the same repetitive task over and over again, whether that's just copying and pasting data or creating a report or whatever it is. So I just got bored doing some of those tasks. So I started learning VBA and learning how to write macros. And then from there, I started developing applications and stuff. But when I first started out, it was really just simple processes that I could automate instead of...
having to do the manual labor all right interesting stuff all right so so you've learned um vba you know whilst you're working because you wanted to not do all these repetitive tasks and which is a good thing because you know with vba with a pressable button you can do a lot of cool stuff and where did you learn vba or how did you learn it was it books was it internet how did you get into learning vba it was a lot of google search you know the forms on the online forms really helped me a lot especially starting out because And books as well. But I think really, once you start learning it, you're working on a specific project and you need code for that specific solution that you're trying to create. So a lot of the time that means just Googling that specific need and trying to find an answer of someone that's come before you and asked the same question or solved it through a blog post or a form. So that was really, I think Google saved my life in terms of learning VBA. And was it a lot of trial and error? Because I'm sure that the first time you put in the code.
It probably didn't work. And then you had to do a few fixes. Is that trial and error you learn as you're doing it? Yes. That's a huge part of learning BBA no matter what. And I think it's one thing that people struggle with because you get errors. And there's definitely, especially at the beginning, a lot of trial and error for me and a lot of frustration too, because you're trying to implement this code. Maybe it's code you've copied off the web.
And you're not really sure exactly how it works. I know when I first started, that was a big issue for me. I just didn't understand exactly what was going on. So, and you get an error and you're not 100% sure why and you got to fix that error. So yeah. And then, you know, with coding, there's going to be errors in any programming language that you code with really important to understand how it works and how to fix those. So it's always trial and error. But once you start understanding the coding language and some of the mechanics, it becomes much easier to fix those errors. Sure. And how long did it take you to become comfortable in writing code? Did it take you six months, a year, two years?
I would say, yeah, a few years at least. Although, you know, it depended on some jobs I wasn't doing writing macros every day. So that's when I first started. I was doing more. I ended up doing a little bit more application design at kind of a higher level and then writing some macros to automate some processes behind that a bit. But yeah, it took me a while. A lot of trial and error. That's good stuff because people that want to get into that back then.
There weren't many courses. Now there's online courses. So you can obviously learn VBA quicker and we can talk about that later on. But let's move on to your website, excelcampus.com. It's a beautiful website, a lot of great resources, in-depth blogs with pictures and just lots of detail. And every time you do a post, I'm always like, wow, this guy knows his stuff. And he puts a lot of effort into his post and his work, which I like because I like to think that I do the same thing as well with my work. And yeah, I love your blog. It's a reference guide for me. If I don't have anything to post, I'll go on there and steal one of your ideas.
It's free for the taking. It is, it is. No, it's a beautiful website and I like it. Now, Excel Campus, how did you come up with that name? Yeah. Well, thank you. First of all, I really appreciate that. Um, Excel campus, the name, uh, I originally, I was trying to think of names for a site and campus to me kind of like, uh, sounds like a school or, you know, kind of a community too. It kind of has that feel of a community or a place where you go to learn, but also to hang out, you know, kind of like a college campus. And actually I came up with a name when I was driving home, passing a college campus, uh, one day from work. So, uh, that's how I came up with the name, but that's kind of the feel I try and give to the website is that I want it to be a place where people can learn and also come and feel like.
They can be part of a community through leaving comments on the blog post and asking questions and it being a little more interactive. It's hard to do online, but I try and do as much of that as I can to help people learn and help them. People feel like they can learn from each other as well. You have lots of subscribers as well. You have about 20,000. How many do you have at the moment? Yeah, it's just over 25,000 now. That's good. That's good. Yeah, it's growing very quickly, which is great. Fantastic. All right. Now.
Just a bit about the site. How has it evolved over the years? When you first started, you know, you bought the domain, excelcampus.com. What ideas did you have when you first started out and how has it changed or evolved over the last five years or six years? Yeah, it's been a long time. When I first started the site, it was really, I wanted to share some tips, but I also wanted to share some of the add-ins and the tools that I developed in Excel with macros and BBA and that kind of stuff. So I posted a few of those tools up there. They were mostly automation tools for helping format reports. I had one for like the color palette conversion.
Back when Excel 2007 came out, there was issues with the new color palette. So I had one for that as well. And then I developed a few more add-ins. I think I have six or seven add-ins total up there. Some of them are free and then some of them are paid. So that was the main focus when I started was the add-ins and, of course, sharing tips and tutorials. And I've changed my focus a little bit, especially lately, to be more geared towards learning and sharing tutorials and a lot of videos to help people learn Excel. Because a lot of people like add-ins and they like the...
problem that they solve, but they don't solve every single problem. So a lot of people just want to learn how to work more efficiently in Excel, you know, so try and balance it. So you first started out with more VBA and add-ins, and now you have posts on pivot tables and formulas and Power Query and Power Pivot, right? Yeah, I cover just about every aspect of Excel that I can. And because we tend to have to use all those tools, you know, as a data analyst, financial analyst, even accountants, just about everyone that's using Excel needs some exposure.
at least understand what those tools do so they know which tool to use for the job. Because Excel gives us such a wide variety of tools to choose from that you can solve a problem a million different ways and come up with the same result. So I do try and cover it all. Yeah, and with Excel bringing out Power Query and Power Pivot, people want to learn that. And I asked them at least that recently. And I asked them, what did I want to learn this year? And I said, Power Query, Power Pivot. So now I'm going to bring out more of those, for example, some basic stuff, how to install Power Query and Power Pivot, and also just a few of the features. And you always got to be changing and evolving because Microsoft...
brings out all these new features and people don't know it yet. A lot of people are still using Excel 2007, 2010. So with 2013 and 2016, you have all these new features. 2016, you have new charts. So people are asking, you know, these new charts, how to do that. And then, you know, we got to come out there and show you that, how to do it. It's not that hard. It's just getting it out there and showing it. Yeah, it is. And it's great that all these new features are coming up, especially the power tools. It's great to hear that your readers are interested in learning that because they could, those tools can really save a lot of time and really help analyze data in new ways that you just couldn't do before.
in Excel. Or you could, but it takes a lot of work to get there. So it's great to hear that people are interested in that and learning more about it. Power Query has been out since probably 2011, 2010, 2011, but it's just now kind of getting more popular. So it does take some time. It's great to hear that. Yeah, it does take some time. It's about a five-year lag. Yeah, but that's, you know, I think especially with Excel and the finance world, you know, if you're using Excel for finance and accounting, that's going to typically be an issue because you're...
you built a system that might be based on Excel and you don't want to break it, you know? So it takes time to implement change to a system that's existing that you don't want to break. And you also have to find time between month end close and quarter end and all these things to then try new stuff, you know? So it does take time to implement some of these things. Well, you know this because you're working full time as well as running excelcampus.com, right? Yeah, yeah, I'm trying. How is that? How is that? It's a lot of work, but it's great. You know, I find that with Excel Campus, I learn a lot from the readers. I just had a blog post yesterday about,
how to attach Excel files to an email. And I had so much response from that. I learned so much in like, you know, a few hours from just the response of other readers that have their own ways of doing that, that are way different than mine. So I find it a great learning resource too. So putting in that time also helps me with my day job, you know, helps me learn and apply those new techniques. So it's great, yeah. All right. Now, where do you see Excel Campus in the next few years? What's the plan there? Yeah, well, I launched a course. Well, I have two courses now, online courses. And I'm going to continue to do that as well.
publish more courses. I've had a lot of good response on those for people that like, and I know you have courses as well, people that want to get more training and get a more structured training. It's a great way for people to be able to do that. So there'll definitely be more courses. I tend to publish a video about once a week or so. So I have a lot of YouTube videos as well and plan to do more of that too, because that's another area that people enjoy and makes learning easy. It surely does. All right, we're going to be looking.
forward to all those in the coming months and in the coming years. And I'm sure you're going to bring out some amazing and incredible content and a lot of people are going to enjoy it. So yeah, watch out for excelcampus.com in the near future. If you haven't signed up, go on his website and you can sign up and you're going to receive his weekly emails and with the tips and also, yeah, you'll be learning lots of new stuff. All right, John. Now let's go on to the next subject, which is VBA. You know, you're the expert in VBA. I'm no expert in VBA.
I dabbled in that and I can't get my head around it. And I don't want to spend a year trying to learn it because I want to learn. I've learned formulas and pivot tables and everything else there. And I thought, let me do that first. But VBA is just another beast in Excel. And yeah, I'm going to leave that to the end. When I learn everything, I'm going to end up doing VBA and say, okay, now I'm the master of Excel. At the moment, I'm no master because I have no ideas about VBA. I can record a macro, but that's about it. But I'm ending it and putting variables and objects in there. I cannot do it. And yeah, I respect the people that know VBA.
VBA is helpful. Now, for our listeners out there, can you tell them what is VBA and what is a macro for those people that don't know it? Yeah, sure. Well, I'm excited to hear that you're eventually going to learn VBA, John. That's awesome. It is not easy, though, to start out. So a macro is basically the code that you can write or record to automate a process in Excel. So VBA is the programming language that we use to write macros. And it's basically kind of built into all the Office applications. So you can get to the VBA editor by hitting Alt F11 on the keyboard. That's the keyboard shortcut in Windows versions of Excel that will open the VBA editor and allow you to just start writing a macro.
So you don't have to install anything extra or anything like that on most installations of Excel. But then basically you can then start writing code to automate Excel. So it's tasks like maybe something as simple as copying one column of data to another sheet. If you do that every Monday, that's something you could potentially automate with VBA. And like you said, the macro recorder is a great place to start. You can just hit a button and start recording a macro. And the macro recorder basically records all of your actions that you take in Excel and then creates the code for those actions. And then you can go look at it.
and modify it edit it and kind of adapt it to what you need so so an action will be click a cell or enter and go from one cell to the next one a simple a simple action like that correct that's right yeah so everything that you do yeah just about almost everything but it but it does something as simple as that or if you change a field in a pivot table or some number formatting you know all those kinds of actions or events that take place in the worksheet will be recorded and code will be created for that So that's, I think, one thing that makes VBA so accessible compared to maybe other programming languages where it might be more complex or it would take you a long time to just get started with it. With Excel, you could just do whatever you'd normally do, prepare a report, record all that in a macro, and then go look at the code and see what the code is. That's how I got my start. Beyond just the Googling and stuff was just recording some of that code or those actions and trying to figure out what that code meant.
The thing that, like you alluded to, though, is that it becomes a little more challenging because that code isn't necessarily efficient. So it doesn't record loops, for example, and loops are a way to really automate a process. If you wanted to loop through each sheet in the workbook and pull some data out of each sheet to consolidate it, the macro recorder isn't going to give you that code. So it kind of starts with the basics, but then you really do have to learn more coding principles and techniques to really start automating more in-depth processes. Okay. So the macro recorder does the simple stuff and then the VBA, which is in the back end, just records everything. And also you can expand on that and add some other variables or some other actions. Yeah. The nice part about VBA is that you can create full on applications. You can create user forms that will pop up in a new window where a user can actually type in data or make choices, selections, that kind of stuff with dropdown menus and checkboxes and all those kinds of things that you would be used to in a normal, any normal desktop application. You can actually create that in VBA and create an application around Excel.
that versatility makes it just amazing you know i think that's probably one of the reasons that the world kind of relies on excel or uses it so much is because you could just create an application in a matter of hours that would otherwise take months to implement and you know get it to install and all this kind of stuff you can have someone whip up an application that automates a simple process you know in a day and have it done so that's the good thing about excel is that you can do that with bba other applications like tableau and salesforce you can't you cannot do that you can't add stuff on there but with excel you can definitely add on different add-ins, which is the coolest thing, which is the coolest thing about that. And let's just go quickly into the workbook, okay? And obviously, you guys cannot see it. I'm using Excel 2013, and I have the developer tab on top there. Now, the developer tab is not there when you install Excel 2013. You've got to go in the back end and tick a couple of boxes so you can come up there. John, can you tell us how to bring that developer tab there? Yeah, sure. So basically, you can right-click on any tab in the ribbon. The ribbon is that top menu bar up at the top.
I always call it the ribbon. And like where you have your home tab, you can right click on the home tab and then choose the option, customize the ribbon. And that'll bring up the Excel options window. And from there on the very right side, you'll see a column of a bunch of all the tab names, basically all the main tabs. And you'll see the developer is one of those checkboxes. You just check that box that says developer and hit OK on that menu. And that will add the developer tab up into the ribbon. And then the developer tab has the record macro button. And then you can also view all of your macros and you can jump into.
Visual Basic Editor from there. All right. So you guys there, he explained that because I couldn't. So that's why I asked him. And that's, yeah, exactly right. Spot on. So it's all there. Now, why wasn't that checked from as a default when you install Excel in your computer? That is a great question. I don't know the answer other than probably the feeling is that most people don't use it and it just adds space. So it does add one more option up there and maybe makes it look a little cluttered. But it's a great question because that's...
like how you enable it is, you know, it's kind of a mystery to a lot of people. Why isn't it there? And most people don't know you can customize that ribbon completely. You could remove the home tab if you wanted to, although I wouldn't recommend it, but you can customize that ribbon and move those tabs around. So that's a blog post in the future. Yeah. You talked about that forms, you can create forms. Can let's talk about the things that you can do with VBA, just briefly, a couple of things you can do with VBA. Firstly, forms, what are they? And what are the benefits of creating a form? Yeah. So a form is basically a pop-up window.
that you would see on top of Excel. And there's some actually built into the application. If you ever use the name manager or if you're creating a chart, that window that appears that allows you to select the data, the data source for the chart or even a pivot table, those are all forms. And you can actually create those in BBA. You can create a form totally customized to whatever you want to have there. So a lot of people will use those for data inputs. They'll bring up, maybe click a button on the spreadsheet and it brings up a form that allows you to input data or allows the user to input data or even loads some data up there.
maybe from a database or something like that. So all of my add-ins or most of my add-ins are actually forms that the user can interact with to do certain things and automate certain processes within Excel. Okay. So a form you can put in, a user can put in, for example, a person's name, employee number, when they started, when they finished, the salary and press okay. And then it'll put it in a Excel table, for example. And then the next entry, the same thing, and it'll go on the second row of that Excel table.
Correct? Yeah, that's right. You could definitely program a form to do that. There's also a data entry form that's built into Excel that actually does that as well. I don't think it's in any of the menus anymore in any of the modern versions of Excel. There actually is one built into Excel that does that as well. And that might be a topic for a blog post. But you could also program those. So like you're saying, you could have data input. And then when the user hits OK, it could add it to a sheet. It could also create an email or copy something to a PowerPoint or Word. That's one of the nice things about BBA is it interacts with all the Office applications as well.
really create some customized solutions with forms. Sounds very powerful. Very, very powerful. What have you streamlined in VBA at your work or at home? What are the things that you've done or you've seen done? I've done a lot of projects with VBA. Some of the biggest ones that I work on are automating budgeting. So budget templates and kind of a budget process, which is a process that most companies go through. And it can be very just labor intensive and manual because the company I work for, we create a budget template that's just an Excel file for each department within the organization.
every year and we have probably close to a thousand departments in the organization around the world so creating a file that you know with their data their headcount roster their pnl can be very labor intensive if you do that manually so that's one thing i've automated uh with vba created a entire application that does that that allows you to populate you know to create excel files for specific departments to populate them with data to update those when things change that kind of stuff those those kinds of processes where you can basically automate a single process on a bunch of different files can really save a lot of time from having to do that manually. So that's a very common one because that's typically, if you're in accounting or finance, you end up doing a lot of that, just working on different files, trying to consolidate data and trying to make sense of it and create a report. And you can do that with VBA. And how much time have you saved doing that application using VBA at your work? That particular one, oh man, I don't have the numbers on me, but I feel like we save hundreds of hours of time every year.
with that one budgeting process. I mean, it's a massive undertaking to begin with. And yeah, so just being able to have BBA automate all that can go from a month's worth of work just down to a few hours, you know, because if you think about it right now, if you want to try and create a thousand Excel files with different data in each one, it would just take you a long time to do each one. And so when we did that project, we figured out how much time it'd take to do one file per person manually, which is five to 10 minutes to prepare it. And, uh, with VBA, we got it down to like, I think 12 seconds or so. Yeah. So it's, so, you know, and of course it's just lightning fast when you do that with VBA. So save a lot of time. Wow. And you've got that bargaining power. Now, every time you want to go for a promotion, you say, Hey, the hours that I've saved. Yeah, absolutely. I mean, that is, that's a huge part about learning VBA is.
employers are definitely looking for people with these skills, not just because it saves time, but because someone like me has worked there before and created this application. And if I leave that company, the company is now desperate to find someone that can run that process and maintain it and update it and understands it. Right. So, you know, companies are very eager to find someone that has good BBA skills for that reason alone, not to mention it can just save you so much time and headache trying to do this stuff manually. So.
So guys out there, learn VBA. It's going to pay off in the end. It'll give you those promotions and open up lots of doors in your company and out in the whole world. So yeah, look, I'm not working at the moment for a, you know, I'm self-employed, so I don't need to learn VBA. For all you guys that are working in the corporate environment, I must, isn't it, John? I must learn VBA. Yeah, it'll really help. You know, I think it's one of those skills that if you can add it to your skill set, it's kind of one of those recession-proof skills, I would say, because not everyone can do it. So it's a unique skill.
That's not easy, but it definitely helps a lot of people. And that's really the rewarding part of it, too, is you get to help a lot of people when you create this solution or application that saves time. Obviously, if someone's doing that manually, they're going to be appreciative of it being automated and it'll help out a lot. So yeah, there's a lot of good reasons to learn PBA. So I recommend it, too. All right. Awesome stuff. And yeah, guys, go out there, learn it, and you'll be in the top 1% of the Excel experts out there. And you'll always have a job.
Trust me. Now, what's the weirdest or coolest thing that you've seen done with VBI or Excel in general during your time working in the corporate environment? Hmm, weirdest, coolest. I see a lot of weird bubble charts and pie charts. You know, I created this chart that it's kind of like a calendar that shows the length of day. I surf, so that's like my hobby. And, you know, as a surfer, you're always trying to figure out how much time you can squeeze in before work to go surf. So when the sun's going to come up and all this stuff. So I created this kind of calendar or wall clock that shows that. I should publish that. I kind of forgot about that. Yeah, it's pretty cool. It just kind of shows the length of day around the year.
And you can set it to different, uh, longitude and latitudes to show in your location. So that's probably really nerdy. Not that cool, but, uh, yeah, that's somewhere between weird. cool and nerdy. All right. If you have that, let's put it on the show notes. Okay. Yeah. Yeah. I'll get something up. All right. And you've also mentioned that with VBA, you can do a lot of Excel add-ins and you've created a lot of add-ins. You said that you first started off creating add-ins and publishing them and also writing about them. Can you tell us what are add-ins and also expand upon your add-ins that you've created?
Sure. So an add-in is basically an Excel file that contains macros that you install into your version of Excel or your instance of Excel on your computer. And the advantage to that is that you can usually run that add-in on any file you have open in your computer. So for example, I have this add-in called TabHound. And once you install it, you'll see up in the ribbon, you'll see a new tab called Excel Campus. And there'll be a button there that says TabHound on it.
When you click the TabHound button, a form appears or a window appears that just lists all the sheets in the current workbook. So whatever workbook you have open, that window is going to show all the sheets in that workbook. And then right above that list, it has a search box that allows you to type a search for any sheet and filter down the list. And then you can hit enter or select a sheet and it'll jump right to that sheet. So if you have workbooks with a lot of worksheets in them, I have users that are using TabHound that say they have...
workbooks with 90 to 120 worksheets in the workbook, which to me is insane. I don't know how you navigate through that. But TabHound allows you to navigate through that because it'll just bring up a window. You can type a search, just like a Google search, for the sheet name, find it, hit enter, and it'll jump right to that sheet. And that works on any workbook you have open because with an add-in, it works on any workbook you have open. So with macros, sometimes you'll create a macro-enabled workbook where that code just lives inside that single workbook. And if you don't have that workbook open, you can't use the macro. With an add-in,
you can use that code in any workbook you have open. So that's the main difference between an add-in and a workbook that contains macros. Hope I didn't confuse you. No, no, no, it makes sense. It makes total sense. And the tabhound sounds interesting. So you have a lot of sheets. You can go, you can search them and go directly there. Now I've installed the tabhound and I love it. Another great feature is that flip back. Add-ins are things that Excel doesn't have.
and adding features added on top of Excel. And usually you think, you know, issues that people have, I want to do this and Excel doesn't do it. I'm going to create an add-in so you can make it happen. And the flip back is a great, a great feature that you've used in this add-in. And can you explain that to our listeners? Sure, yeah. And that was a good explanation of add-ins, like adding features that Excel doesn't have. One of those is this flip back feature that allows you to flip or jump back and forth between the two worksheets that you're working on. So it basically flips back.
to the last worksheet that you are working on so again in that example of a workbook that has a lot of worksheets if you're you know looking or working on a formula maybe or a pivot table on the first sheet in the workbook and then you have to navigate to a source data sheet that's way in the back of the workbook you can then just hit that flip back button and it'll take you back to that sheet you were just looking at so that saves a lot of time because otherwise you might be just navigating or scrolling horizontally through the worksheets i can hear people now saying oh my god yeah i that's that's exactly what The reason I created it, there's no back button in Excel. There's nothing that just jumps you back to where you were. And I don't know why they don't do it. It's not that hard, is it? Yeah, and even TabHound itself just requires a search box to jump to the sheet, and there's really nothing. You could kind of do it with a go-to menu, but it's not straightforward. So yeah, it's one of those things. It's an add-in. It just solves a simple problem that we struggle with on a daily basis. Sure, and you also have PivotPal. What is PivotPal?
Yeah, so PivotPal is another one, I think, and your students of your course probably know this better than anyone. Once you really start getting good with pivot tables and using them and relying on them, you start to realize that there's some things in working with pivot tables that take a long time. One of those things can be formatting the numbers. When you add a cell, I'm sorry, when you add a field to the values area in the pivot table, the number is never formatted the way you want it to be formatted, right? You always have to go take an extra step to format that number. Depending on how you do it, it can be quite a few mouse clicks to format the number.
PivotPal has a bunch of automation features built into it. So with number formatting, one of those is when you add a field to the values area, it automatically detects the number formatting and the source data and applies that number formatting, or you can have it apply one of your favorite number formats. So basically, PivotPal looks a lot like the built-in field list or kind of mimics some of the features of the built-in field list where you build your pivot table, but it has more automated features, maybe a little more intuitive or features that will save you time when you're working with a pivot table. That is really cool because a lot of people ask that, you know, how do we get the number format from the data onto our pivot table? And I go, there's no way. You've got to buy an add-in and that'll do it. And this add-in has helped a lot of my students that are doing the extreme pivot table course and they absolutely love it. Another great feature is the search.
function to search for the field lists in PivotPal. Yeah, that's actually the feature that originally got me, that I built it for, was being able to search for the field that you're looking for in the field list. Because if you're, especially if you're exporting data from a system, you can have 50 to 100 fields, no problem. And trying to find a field that you want to work with or add to the rows area, the values area can be very time consuming to scroll through that list. So PivotPal has a search box and it's very simple, just like a Google search, type the field name you're looking for and it'll filter down the results and then you can add it to any of the fields you're working with. Now, actually in Excel 2016, they added that feature. Yes, I was going to say that. Did they copy you? What happened there? Well, I was pushing for it. I don't know if they, you know, I definitely requested it, you know, which is great. It's great to see that they're making changes and adding these features to the product. And you didn't get any commissions from that? I don't know about that. We'll see. All right. So yeah, 2016, you have that option of the search.
box for the pivot table fuel list if you don't have 2016 you can just install the pivot pal and you'll get that straight there those are pivot pal and taphound you're gonna pay for them it's not a lot how much do they cost yeah taphound's 29 and pivot pal's 37 right around there all right yeah okay now you also have some free add-ins that people can just go into your website and download now Yeah, I have quite a few, actually. One of the most popular ones, I have a few. Chart alignment basically allows you to use the arrow keys to align the elements inside your chart. So that's one thing you can't do natively in Excel. If you select the title of a chart and try and press the left arrow, it doesn't move it over to the left. So this add-in allows you to use the arrow keys to move the elements of the chart around and get them all lined up nicely. And another popular one is a formatting shortcuts add-in, which basically allows you to create keyboard shortcuts for font colors and fill colors. So if you want to change the color.
the font for a bunch of cells to red. That's something you do all the time or fill the cell yellow. You can create a keyboard shortcut for that, which is something you can't do in Excel either. And it's not easy to create a keyboard shortcut in Excel for that. So yeah, so those are a few free add-ins you can, you can check out and download. That'll save some time. And the pace buddy.
Oh, man, I got so many. Yeah, PasteBuddy was another one that allows you to create keyboard shortcuts for any of the paste special commands, like pasting values. So that's one thing you do a lot, like copy-paste values. There's, I think, eight or 10 different shortcut key combinations you can use in Excel to do that. None of them are extremely straightforward. You know, there's Alt-ESV. You could put something on the quick access toolbar. You can right-click. None of them are very, very straightforward. So PasteBuddy allows you to create keyboard shortcut that is fairly straightforward. You could use Control-Shift-V, Control-Shift-S, whatever you want to, whatever.
keyboard shortcut you want to create to paste values or paste formats or anything like that. Wow. An array of great add-ins that are going to save you time. And then when people buy it, they can actually all download it for free. They can, you know, you show them in the download PDF. You have instructions of how to install the add-in. So it's easy. People don't know how to do it. You get all these instructions. Yeah. All right. And yeah, all the great add-ins that you have there. And you have a special offer for our listeners, John. Yes, I do. If you enter the discount code MyExcelOnline at checkout, you could save 10%.
All right. Thank you very much, John. Very generous of you. And guys, definitely go there and start with the free ones. Have a look. See what you think. And John has explanations on every of his add-ins, and you can check it out. And if it works for you, go for it. If it doesn't, well, not an issue, but I'm sure they're going to save lots of time. And you're going to find links to all these add-ins in the show notes if you go to myxonline.com slash podcast. Okay, let's take a short break, and we'll be back with John's VBA Pro Course.
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 peers, 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. All right, we're back with John Acampora from excelcampus.com and we're going to talk about his newly released and very popular VBA course. It's called the VBA Pro course. John, first question, what did you decide to create this course?
Yeah, well, I get a lot of requests for learning VBA. I think that was really the main motivator was, you know, when you sign up for my newsletter, I try and send everyone out an email just asking what they're using Excel for. And I get a lot of feedback of people that want to learn VBA and learn macros, you know, just kind of simple stuff. There's definitely plenty of resources out there, but a lot of people don't know where to start. It can get confusing really quick and frustrating as well. So I decided to put together this course to help make that process a lot easier to learn it to kind of start with the basics or the foundation of coding and the VBA language.
and go from there into automating tasks all the way into creating a basic add-in as well. The VBA Pro course covers that too. So basically it was just, you know, the demand for this kind of training was what initially got me going. Yeah. And I'll get that as well from my listeners. I do surveys from time to time. And the number one thing there is VBA and macros. And, you know, number two, people want to learn formulas. And then you got this new Power BI. And now I send a survey.
I don't include macros because I know to be number one. Yeah, that's good to know. But I did the same. You know, I surveyed my list as well. And that was a huge, just a huge request. And I thought as well, when I wanted to become, you know, expert in Excel, I thought I need to learn macros and VBA. You know, that's what I need to do. Yeah, I never got around to doing it. And I can see from people that are using VBAs to automate and help them work. Yeah, they should definitely start straight away, straight away, don't you think?
I do. I think it's something that can help you sleep better at night. And because you tend to, once you kind of understand how to use it, you can create applications that allow you to have power and control over Excel. So where you might, like that budgeting process I was talking about earlier, where that might be this like just monumental task that you think, you know, I'm worried I'm never going to finish that on time. If you can write a macro to automate it and you have the ability to do that, then you can actually, you know, you have less stress because it doesn't take as long to do and you feel like you can recover from disaster or any kind of problem.
I think that helps a lot too and a good reason to learn VBA. And this course is directed at beginners, people who never use VBA or use it a little bit. And so taking a beginner and walking them through the course and coming out a more advanced user. That's right. Yeah, it's called the VBA Pro course and title might be, I don't know if it's the best title, but it is geared towards the beginner. The first two modules or two sections of the course are very entry level and go over all the basics of coding and what you'll need to know. And it progresses from there. The thing with VBA, it's like learning Excel. There's so many different areas that you could learn. Just like with Excel, you can learn formulas, pivot tables, all this stuff. VBA is the same way. So there's no way you can cover it all in a course, but I try and cover the foundation, the techniques and the basics you'll need to know. So you can go find.
other resources and actually understand those. I spent a lot of time searching Google, not really understanding what I was finding, or it took me time to understand what the properties and the methods are and these objects we're programming and all these coding language and that kind of verbiage and stuff. So I try and teach that right away because we can understand that. And it's not too complex once you understand some of the basic principles. It can really help you progress in your learning very quickly.
And I always get confused. You've got variables, you've got objects. And yeah, there's so many variables, aren't there? There's so many there. And you explained it well in a video that you did, right? Yeah, I do have a free training series, which is a video series on an introduction to VBA. And in the first video, I explained how to write your first macro, how to create the macro-enabled workbook. And then I also go over some of those very basic principles of the objects in the Excel object model.
and how that works and how properties and methods work. I kind of relate it to programming objects in your kitchen. So if you can kind of think about common everyday objects, maybe something like your coffee maker, your coffee maker is an object that sits in your kitchen, and it has properties, maybe it's hot, maybe the water levels full, these would be properties of that object, you might also have methods like making coffee, heating up, those kinds of things would be methods or actions that the coffee maker takes. And so in Excel, we're basically doing the same thing, we're programming objects, that object could be a worksheet, it could be a cell inside of a worksheet, it could be a pivot table or a chart. All those things in Excel are objects. And then we program those, the properties of those objects. So maybe the cell has a value in it. The cell has a certain text color. And then we can also program actions or methods, which may be copying, pasting, saving a workbook, those kinds of things. So I try and relate it to kind of the everyday things. You can start thinking about this and not get too overwhelmed, right? When you start out, because there's a lot to it and it can get overwhelming very quickly. Well, I've learned a lot just from the last mineral site. Great. Yeah.
I try. It's not, you know, that's the toughest part really is making it easy, taking something complex and making it easy to explain or so people can relate to it. You know, that's the challenging part. But it helps you understand it as well. You know, because as users of Excel, no matter what level you're at, you're going to have to explain your worksheet or your product to somebody else at some point, whether you're training them or you're training your manager how to use it or maybe the CEO, how to click a slicer, you know, whatever it is, you have to kind of be able to explain that your application that you've developed, even if you're not using BBA to a user base.
That kind of communication can really help you in your career as well, right? If you can communicate this stuff and train other people, then it can really help you advance in your career. Well, I can see that you're a great teacher and my awesome stuff. Now, you said the first couple chapters, you're holding the student's hand, just explaining the basic stuff. Can you tell us a little bit more about the following chapters and all the way to the end? What can people expect from this course? Yeah, sure. So yeah, the first few chapters, very introductory level. And then the next two modules, we go into some kind of real world examples of creating a macro that automates a process for multiple worksheets in the workbooks. You learn how to loop through those.
worksheets and do different tasks like copying and pasting data, consolidating data, listing out all the sheets in the workbook. Some fairly easy tasks, but it exposes you to a lot of different techniques and coding principles within BBA, like loops, message boxes, all that kind of stuff. And then we get into user forms. We actually create user forms. And there's two modules on user forms, how to create those and how to automate that and code user forms. And then we actually build an add-in. And that's basically...
There's another module on building and adding buttons up into the ribbon. That's part of having an add-in that opens your user forms and all that kind of stuff. And then in the last module, The last module of the course, I explained that process that I was talking about earlier about automating budget files. I call that a file manager application and basically show how to create that file manager application, which will allow you to run an automated process on any number of files. It doesn't have to be budgets, but basically if you're working with a lot of files, this is kind of a framework that allows you to run any kind of automated process on a number of files. So I explained how I created that. We go through it step by step, how to create it on your own, how to use tables, the Excel tables, and use those as kind of an...
spreadsheet-based application. So it covers a lot. The course is, it does cover a lot, but it gives at least a good foundation of some of the common things that I've worked with in my career to help automate processes and save time. A lot of valuable information there. Now, what's the feedback been from your students? Because I'm sure that they've become experts within a matter of days, if not weeks. Yeah, it's been great. It's been very nice to have a lot of great feedback from the students. A lot of students are very enthusiastic and also interactive. So in the course, I have a comments section.
on every lesson page where there's a video. And all the videos are real short, just five to 10 minutes in length. And then there's a comment section below that. So students can ask questions and I'm there answering them. And other students can see that and answer as well. So it becomes interactive. And there's a lot of great questions and a lot of great feedback, which is really nice. They've had users, like you said, learning at a very fast rate because they need to get something done for work. You know, they got a project that they got to get done. And so they're willing to put in the effort to learn this stuff, which is just awesome. And what else did they get from the course? How long is the course and the access to it?
Yeah. So, uh, right now it's lifetime access, uh, to the course when you sign up and it's 11 modules and each module has about 10 videos in it, short videos. So it's about 10 hours of total video, but that's broken up, you know, into short lessons. So you can spend time practicing after you watch a video with VBA. That's especially important to practice on your own work and, you know, to kind of get familiar with these principles and techniques and stuff. And they can download the workbooks and keep all the workbooks. Oh yeah. Yeah. I can download the workbooks. Every module, every video lesson has a workbook that I go through. You can practice on.
So that's another benefit too. That file manager application that I discussed, you could just download it and use it for whatever you want. You don't necessarily have to create it on your own. So yeah, there's a lot of benefits there. Yeah. And you get lifetime support. So as long as you're alive, you can- Yes. I do have to be alive. But yeah. So other than that, yeah. And not behind bars. Yeah. I just feel like with VBA, for me, it's been a lifetime learning process. So I want this to be a resource that people can come back to in the future as they get advanced and learn more and more. I want to be able to come back to this and use it as a resource. Yeah. Now, lifetime access is great. And how many students do you have now?
I believe there's about 600 now. Wow. Wow. All right. Can you tell us, our listeners that are waiting to know how can they get access to this course? Yeah, sure. So you can go to excelcampus.com slash VBA pro, all one word. And there you'll be able to get access to my free training series on VBA. Like I mentioned before, I have a free training series, which will get you started with VBA. Just sign up for that and check it out. All right. Thanks for that, John.
I can't wait. I'm going to sign up as well because I want to be the super Excel experts in the whole world. And mate, cannot wait for that. Great resource, guys. You should definitely stop what you're doing, even if you're driving and go on your phone and check it out. This is a great course and it's going to elevate your...
excel skills and your professional development and i'm sure that you're going to get noticed in your company and you know it's going to give you a lot of great reach and you can get those jobs that you want in the promotions and above all you have confidence in using excel so you know john's a great teacher and get on that course all right guys so you can find the links to this course and everything else we'll be talking about today by visiting the show notes on myexcelonline.com slash podcast let's go over to john's excel favorites this is my favorite part of the show and a lot of people's favorite part of the show where we get to ask our guests what their favorite Excel features, tips, and everything so related. First thing, what's your favorite keyboard shortcut and what does it do?
Okay, man, I have to choose one. Well, I'm going to go with alt semicolon. Alt semicolon selects the visible cells. So if you have a filtered range, or if you have some hidden rows, typically, if you try and copy those rows and then paste them somewhere else, you'll get all the hidden data, all the rows on the hidden data as well. So alt semicolon basically will only select the visible cells. So the first step is you select all the cells that you want to copy, then press alt semicolon, that'll select the visible cells, and then select or then copy control C or right click copy.
And that will only copy the visible cells and then you go paste them somewhere else. All right, John, thanks for that. Now, what's your favorite Excel tip? One I use a lot is if you hold down the control key, when you select a chart, it'll basically show a little bounding box around it and then you'll be able to move it with the arrow keys. So if you're building a dashboard and you're trying to line up all your charts nicely and you want to kind of nudge it with the arrow keys, hold down the control key before you left click the chart and that'll allow you to move it around with the arrow keys. All right. I didn't know about that. Cool stuff.
Cool stuff. See, you learned something new. Thank you, John, for that. All right. Now, your favorite function and why? Oh, favorite function. I'm going to have to go with good old VLOOKUP. I think it's my favorite. I mean, most people say that's lame, but I think it's, yeah. But I think it's also kind of the gateway to, for a lot of people to really programming Excel and kind of taking their data analysis skills to the next level. I know that was for me as well. So it's probably the most common one for that reason. One of my favorites. Another one, if you want a cooler one.
The cube functions are awesome. So I know you said your listeners and your readers are into learning PowerPivot. The cube functions are an amazing set of functions that not many people know about at all. The cube is C-U-B-E, and there's a set of seven or eight functions. But basically, those functions do something similar to getPivotData. So the getPivotData function, it's very similar to that, but it will basically pull in data from your data model or from your PowerPivot data set. So it's a really, really cool set of functions. You can do everything from, you can even bring in a slicer selection.
or reference a slicer in the cube function. So you can create some very dynamic, very customized reports that you wouldn't be able to create in a pivot table layout. It's kind of an alternative to building pivot table reports. All right. Great resource there. Thank you very much. And we're going to put a link on the show notes to explain that if you do a video for us. Sure, sure. That's a good one. Yeah. All right. Good stuff. All right. Now we're talking about add-ins and you have plenty. What's one Excel add-in that you're always using constantly? One of yours and one that someone else has created.
Sure. One that I use of my own is probably PivotPal. I use it all the time for pivot tables. I have a feature on there that allows you to create the pivot table from the source data sheet. And that's just something I come to rely on because the pivot table field list disappears when you go to the source data sheet. One that someone else has created, I got to say that one that Microsoft has created is Power Query. And that's one I use a lot. And it's no longer an add-in in Excel 2016. But up until that point, it was an add-in. And that's another one I rely on daily.
Power Query is awesome. And when people find out the things that you can do with Power Query, they're going to leave the formulas and they're going to leave text columns and everything else to clean up their data. They're going to rely on Power Query. It's going to be massive. It already is, but people just don't know about it. I'll have to create a course on that one day. All right. Now, apart from your blog, which is a great resource, what's another online resource for learning Excel?
Oh, that's a great question. There's so many. MyExcelOnline.com. No, there really is so many resources now, which is great. You can learn from all of them. And I've learned a lot from the forums too. I think that's probably my kind of original resource that I go back to is just MrExcelForum, BBA Express. There's a lot of forums out there as well.
Yeah, forums are a great place for people to learn Excel because there's a plethora of questions and answers. And if you have a question, the answer will be there. If you don't, people will get back to you. And it's a great tool to learn an Excel feature. If you recommend one Excel book to our listeners, what would it be? For learning VBA, I like any of the books that John Walkenbach has written. He publishes a new one every year, Power Programming, depending on what version of Excel you have. Those are great. They're huge. So don't expect to read it cover to cover, but it's a great resource. And he has a programming...
VBA for Dummies. I don't know the exact name, but it's a smaller book. If you want a little lighter reading, check that one out. And another book, non-VBA related for the non-VBA geeks. I just did a giveaway on my website for Mr. Excel, Bill Jelen's latest book is Excel 40 Greatest Tips. I love that book. It's just random tips, but there's, you're going to learn some from it guaranteed. It's got all kinds of fun stuff in there. I think it's a great book, including cocktails. You can learn how to make Excel cocktails and Excel inspired drinks. So I bought that book just for the cocktails. I haven't even got page one. It went right to the end to the cocktail section. Yeah, that's great. It is a great book. It is a great book. And I got it signed by Bill Jelen and I've got a couple of copies and it's a must for anyone.
Get it. Whether it's a hardback or the PDF version, go get it, download it, buy it. I'll put the links on the show notes. It's a must-have. It's a must-have. It's the best tips. Mr. Excel has been around for many years now, and he put all that into one book. It's a great resource. All right, John, thank you very much. Let's finish up with an Excel scenario that most people...
go through. Let's say that I am an Excel beginner or an intermediate user, and I need to learn the most important features of Excel within a week because I've got an important job interview lined up. Now, with all the Excel knowledge that Jonathan Porter knows today and the online and offline resources available, what will be the quickest way for me to start learning Excel? I would say definitely an online course is going to be the fastest resource or way to start learning because it's structured. So you have structured learning, you can follow the videos and just...
apply that information or learn it very quickly. Because a lot's going to depend on the job too and what your employer is requiring you to do with Excel. But I'd definitely say take an online course or get a book that has some structured learning to it to learn the basics. That's great. And online courses, you know, the range between one hour and 10 hours. That's a weekend. You can learn a lot of stuff in a weekend. Lock yourself, learn it, apply it, and then go to that interview and you'll be full of confidence. Because the people asking the questions, most people don't know the answers. They just want to hear you. They want to hear you talk the talk. And if you can talk the talk with confidence, you'll get that job. And, you know, if you really know how to use it, then I can guarantee that you're going to be in the short list for the next set of interviews. And, yeah, great stuff. Now, what's the best Excel feature that they should focus on for this interview?
I think the best Excel feature is going to be formulas. That's really probably where you typically start. If you're going to take on someone else's financial model or their work and kind of take over someone else's work, you're going to need to know how the formulas work within that spreadsheet. From there, you can, I would say, learn pivot tables. You can definitely, you can't go wrong learning pivot tables because that's another skill that surprisingly not many people know, but it can save you a tremendous amount of time when preparing reports and analyzing data. So formulas, pivot tables, learn those and you'll be definitely ahead of the game.
Great, great response. And do you know any great pivot table courses out there? Yeah, yeah. There's one called Extreme Pivot Tables. Yeah.
Weird guy. Yeah. Okay. Yeah. That weird Aussie guy. He has a great course. I definitely recommend checking that out. All right. The extreme people table course. It's a fabulous course. And I'm going to be doing some updates on that. I'm going to include Excel 2013, 2016 and some new features in that course shortly. If you're listening to this, it's going to be out the updated version of the online course. John, thank you very much, mate. You have given us lots of great information.
on VBA, on add-ins, on your background, your website. Mate, you're on the right track to become an Excel legend. And yeah, you're teaching a lot of people and you're giving them all that knowledge, which they can take to become, you know, better Excel users and professionals. And, you know, and ultimately they can get the pay rise or a job that they like. And I've got to thank you for that. You've got a great website. I'm envious of it. And you've set up standard. And mate, I try to follow. And thank you for being on the show. Now, what's the best way for people to connect with you?
Yeah, well, thank you for having me, John, in all the kind words. I really appreciate that. It's great to be here. And so the best way to connect with me is at excelcampus.com on the homepage there. I have an opt-in for my newsletter. You can sign up there and get the newsletter and connect with me that way. And with any email I send you, you can always hit the reply button and just chat with me directly. So that's the best way. All right, John. Thank you very much once again. And for the listeners out there, everything that we talked about today, you can go on the show notes on Microsoft Online.
dot com slash podcast. You can go to Jonathan Poros show, hit the show notes and you're going to have everything there. We talked about all the tips, all the add ins, his course links, everything. All you're going to do is just press on the links and it'll take you directly to exactly what we talked about. So if you're listening to this whilst driving, don't worry about it when you get in the office. The first thing you should do is go to the show notes and listen to a truly great Excel ambassador, Mr. Jonathan Poros. Thank you very much and I'll see you soon. Thanks again, John. Really appreciate it. All right. 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 MyXL9 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 MyXL9 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 myxcelonline.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. Now, 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 e-book 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 e-book 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.