Become a Creator today!Start creating today - Share your story with the world!
Start for free
00:00:00
00:00:01
Episode #30: Jon Acampora from Excel Campus
144 Plays9 years ago
We turn back to tools this week on the PolicyViz Podcast. I’m pleased to have with me Jon Acampora who runs the very popular Excel Campus website. Jon provides tutorials on VBA, formulas, and many other tools. As an Excel...
The post Episode #30: Jon Acampora from Excel Campus appeared first on PolicyViz.
Recommended
Transcript
Introduction and MICA Advertisement
00:00:00
Speaker
This episode of the Policy Viz podcast is brought to you by the Maryland Institute College of Art. MICA's online graduate program and information visualization trains designers and analysts to translate data into compelling visual narratives. Join expert faculty such as Andy Kirk, Marissa Peacock, and John Schwabisch to mine the data and design the story. For more information, go to mica.edu backslash MPS in Viz.
Introduction to Policy Viz Podcast and Guest
00:00:38
Speaker
Welcome back to the Policy Viz Podcast. I'm your host, John Schwabisch. We're going to have a little change of pace this week. We're going to go directly into some tools. I'm here with John Encampora from Excel Campus, and we are going to talk about
00:00:53
Speaker
how to improve the way you work with Excel, a lot of the stuff that John's been doing with his blog posts and video tutorials, and also I want to talk a little bit about where he sees the future of Excel and some of the data visualization tools that are currently available and now sort of coming online and possibly changing the field. John, welcome to the show.
00:01:12
Speaker
Thanks for having me, John. I'm excited to be here. Yeah, this is great. I'm glad I was able to get you on the show. We talked at length last year about the things that you're doing at Excel Campus, some really great videos and tutorials. So I was hoping we might start by just having you tell folks a little bit about yourself and about the work that you're doing at Excel Campus.
Excel Campus Overview and Tutorials
00:01:30
Speaker
Yeah, well, thank you. So my website is excelcampus.com. And I basically have a blog there where I write articles and share tips and techniques about how to save time with your everyday Excel tasks. My background is in accounting and corporate finance. So a lot of my content is geared towards that, but also working with data to create reports, analysis, and financial modeling, and that kind of stuff.
00:01:57
Speaker
My content is kind of all over the place, I guess. I'm going to cover everything from
00:02:03
Speaker
keyboard shortcuts all the way up to writing macros and VBA to automate tasks. So it's kind of a bit all over the place. But when you work with Excel, that's kind of where you land is in a world of you need to know everything to create a visualization or a dashboard. You can't just know how to use charts. You got to know how to write formulas and how to cleanse your data and all this kind of stuff. So I kind of try and cover it all there.
00:02:30
Speaker
And I also have a YouTube channel, the Excel campus YouTube channel where I post a lot of videos and tutorials about Excel as well. So let's talk a little bit about VBA because the videos that I've gone through on the site tend to be the VBA ones because that's the thing I'm most interested in at the moment. I sort of get the feeling that for a lot of people VBA is the under the hood background part of Excel.
00:02:55
Speaker
And for a lot of Excel users, coding is maybe for a lot of people, I think it's sort of coding sort of turns people off. So how do you make VBA something that's accessible for everyone and encourage people to use VBA either to just be more efficient or to do things that the defaults in the tool don't allow them to do? Yeah, that's a really good question. It's not easy to do in terms of
00:03:19
Speaker
training people on how to use VBA. My initial exposure with VBA was I was doing accounting and financial analyst roles where you tend to start to do the same task over and over again. Just writing the same report every week.
00:03:36
Speaker
copying and pasting data and all this stuff. So my first exposure to it was just to kind of learn how to automate some of these tasks that I was getting really bored with doing day in and day out. It took me a long time to really understand what I was doing. You know, everyone kind of starts with the macro recorder.
00:03:52
Speaker
or typically does. You know, that's a great way to start the macro recorder. It just basically records everything you do in Excel. It spits out code and you can go look at that code and try and make sense of it. And it's not too difficult to make sense of, but you still then have to understand what it's all doing. Like if you then want to go write your own,
00:04:11
Speaker
It's really challenging to take that code and then say, okay, but I want it to do this instead. I want it to copy and paste this sheet or maybe all these sheets or whatever you want to do. It's hard to generalize it by just using the recorder. Exactly. For people that have used the recorder and haven't really started coding yet, the recorder also gives you a lot of unnecessary code. It's tracking all of your movements.
00:04:36
Speaker
which you don't necessarily need when you're running a macro. You don't need to select a cell before you paste data there, you know, that kind of thing. Back to your question, to make it a little more accessible, I've tried to relate it to kind of everyday common tasks. I have a video
00:04:53
Speaker
that introduces you how to write your first macro. And I explain the object model there. And even the word object model sounds kind of scary, right? It's like, what is an object model? But basically, if you just think about all the objects in Excel, or everything you work with in Excel is a spreadsheet, or a chart, or a pivot table. Those are all objects. And so I've tried to relate how you program those to how maybe tools in your kitchen. So just something very simple.
00:05:22
Speaker
to relate something scary to something that you're familiar with, like the tools in your kitchen, your coffee maker, it has these properties, you know, it might be hot, it might have a certain level of water in it, it might perform certain functions, right, like make your coffee for you. So that, I've tried to kind of relate how you might think about common everyday tasks,
00:05:43
Speaker
and then turn that into how you can code and program. And explain it in that way. Because it is very challenging for the average person to just start pick up coding and try and make sense of all this. Especially with Excel. Excel is such a broad beast, really. I mean, there's so many things going on, and everyone's need is different. Everyone's problem is different, right? We're trying to solve those in a simple manner, but it's not always easy.
00:06:13
Speaker
Right. Yeah, I think that's certainly true for a lot of people who are using Excel sort of day to day. They're not coders, right? They're using Excel. And I think that's another reason why some of the pivot table stuff is so valuable for people and some of the other things. Yeah, and it's also like, in some way, the average Excel user is a bit of a coder in the sense that even if you're writing a formula, that's really
00:06:35
Speaker
you're starting to create an application in some sense. If it could be an interactive financial model where you input numbers and it spits out information, it could be as simple as clicking a slicer and your whole chart changes. But in some sense, you are programming a spreadsheet. And then to take it to the next step, you can automate a lot of that, the heavy lifting by writing macros in BPA.
00:07:02
Speaker
Yeah, so I think that's true. So that raises sort of an interesting question for me because a lot of the formulas that you can do in Excel, they have some sort of additional arrays and options that you can or don't have to include. And yet, so VLOOKUP as an example allows you, you know, if you don't change the last argument, it gives you a default. Do you think that's a good practice for Excel to have as a default instead of forcing the user to like,
00:07:28
Speaker
set those or is that just like the way it's built is like let's just make it as easy as possible and people just have to learn to be aware of that.
00:07:37
Speaker
Yeah, I mean, the VLOOKUP one is such a good question. I actually just published a whole video tutorial series on VLOOKUP and the lookup formulas. And that one is so confusing, right? That last argument defaults to true, which means that it's basically going to do a closest match or an approximate match instead of an exact match, which is 98% of the time you use a VLOOKUP, you want an exact match.
00:08:02
Speaker
I think it's almost something that's just left over from originally, maybe not, I don't want to say thought wasn't put into it, but they didn't know how important it would be 10, 15 years down the line, you know? Like that, VLOOKUP itself is just one of those key elements, like key skills that every Excel user is going to need at some point, whether they know it or not, they're going to use it or they probably going to need it.
00:08:27
Speaker
Yeah, but yeah, so something is that important like and I could see how Microsoft can't necessarily go and change The formula now because it breaks older versions old there's compatibility. Yeah, right there's compatible issues. Yeah, so I mean
00:08:43
Speaker
I love if they created a whole new function that was similar to like a look up function that didn't require the complexity that the look up does because it's a complex formula and it takes some time to really understand it the look up the two right. Yeah so when it comes to the day you're an excel MVP.
00:09:01
Speaker
So you get to talk to the they. Can you talk a little bit about what it means to be an MVP and what the conversations are like when you talk to the Microsoft folks or when you talk to other MVPs with that dialogue and what those conversations are like?
00:09:15
Speaker
Yeah, sure.
Microsoft MVP Experience
00:09:16
Speaker
For those that don't know that Microsoft MVP is an award that's given out to people in the community that basically contribute a lot of their time to teaching other people about the Microsoft technology. So in my case, I'm an Excel MVP, which is an award. It's not like a certification or anything. So the benefit to that is I get to be part of a group that communicates with the Excel team at Microsoft, the product managers that actually
00:09:40
Speaker
work on the product and give us all these great features and stuff. The dialogue is great. There's an open email loop or distribution channel that we can just email the team directly if we have problems or suggestions.
00:09:56
Speaker
And they're always asking for our feedback, which is really cool. And they're also, at least lately, I've only been an MVP for two years, but they're very focused on getting all user feedback, not just these MVPs, but feedback from the entire world, all the Excel users out there.
00:10:13
Speaker
To try and to improve the product so they're very focused on that which is really cool And it's cool to really see the people behind the product and meet them like we get to go to a conference once a year in Redmond in Washington and Actually meet the team and spend a week with them and we get to see what they're working on next You know I could like sworn to secrecy on a lot of that stuff, but the excel secret sauce right
00:10:35
Speaker
Yeah. But it's, it's great to see that they're passionate about this and they are listening. So like, I know if you use Excel on a day to day basis, you get frustrated with it. There's no doubt about it and you wish it could be better. Um, but they are listening to those and working on them, you know, so it's, there's a lot of positive there.
00:10:53
Speaker
Yeah, and there's been a lot of changes with 2016 coming out in the subscription model that allows them to do these pushes and make changes pretty quickly. There's been a lot of change over the last, publicly, the last few months once it launched in the fall. Right. What are you seeing, either working with the other MVPs or Microsoft or through the people who are taking your courses, what are you seeing in how the program is going to change, has changed, and how that's going to affect people's use of the tool?
00:11:21
Speaker
Well, yeah, I think it's great because they are able to push out changes without us having to wait three years or six years or however long it takes to push out a brand new version. We haven't seen much of that yet with Excel, but I know they're working on pushing out new updates pretty soon. So the next few months, we're going to see more and more updates. And to me, I think it's great because
00:11:45
Speaker
Well, I should say there's not a lot of competition, but maybe you don't look at other spreadsheet tools versus Excel and choose which one you're going to use. It's pretty much your default to Excel. But this allows the program managers and the people behind Excel to get excited about the things they're working on because they're going to see the light of day in a matter of months versus a matter of years. And a lot of times those features that we see these great new features just get canned before they even make it to the product.
00:12:12
Speaker
And then no one gets to actually see those. So in that sense, I think it's really going to help change Excel the future. And they're also really focused on pushing Excel onto all platforms. So we're not, you know, we could use Excel on the Mac, we can use it on iPad and all the Android devices, you know, so.
00:12:30
Speaker
That's another huge push, which kind of aligns with even more of the Power BI stuff and those changes as well. We're kind of just inundated with data now, and we want exposure to it everywhere. CEOs and CFOs of organizations want to be able to look at their
00:12:48
Speaker
visualizations, their reports, their dashboards on a plane, on their iPad, not have to be on their desktop on their Excel to see these things. You mentioned Power
Exploring Power BI's Capabilities
00:12:58
Speaker
BI. Can we talk about that a little bit? I don't know if it signals a C change, but I think it's a big step for Microsoft, and I'm particularly curious
00:13:06
Speaker
to hear what you think about it, and also what you think it holds for the future of the whole Microsoft ecosystem, and also how Microsoft is going to compete against some of the other, in particular, data visualization tools. It allows you to do interactive visualizations much more easily.
00:13:22
Speaker
much easier. Yeah, I'm really excited about it. I think it's a game changer or it has the potential to be. I'm an Excel geek guy, whatever. So I'm obviously going to be in that camp no matter what. I don't work for Microsoft or anything like that, but I love the product and I'm going to be a fan of it.
00:13:42
Speaker
Having said that, I've used other tools, other computing tools, even like Tableau and stuff. And so I can see where this could really be a game changer, not just because people that know Excel can actually get involved with Power BI, but they've made it basically available to everyone. So you don't even have to have Excel to use Power BI and start creating visualizations. And so they're very focused on
00:14:05
Speaker
developing this rapidly. I did a video, gosh, it's probably like, I don't know if it was even six months ago now, I did a video on Power BI and just an overview of it. And I created this dashboard pulling in data from my local ski resorts website for their snowfall history, right? And then I connected to their Facebook page and pulled in all their Facebook posts and all that data.
00:14:25
Speaker
and started to compare those. I mean, you make really basic analysis. When it's snowing, they post to Facebook more often than when it's dry. You don't even have to have Excel to start doing that kind of stuff. You can download Power BI desktop version for free and do all that kind of stuff and create dashboards that you can share online. So as I look back at that video that's maybe six months old, Power BI has completely changed from what it was six months ago, like in a good way. They've just
00:14:54
Speaker
added so many features to it and they're continuing to roll them out almost weekly, which is unheard of. So one of my goals for 2016 is really dig into Power BI and try to understand it. So my thought on Excel up to this point when it comes to interactive visualizations is you're pretty much stuck using the form controls or the ActiveX and you'd make a dashboard, you'd make some interactivity in your spreadsheet, but you had to send the file to someone else for them to be able to use it.
00:15:19
Speaker
So my sense is that Power BI allows you to make the interactive visualizations much more easily, but also it allows you, if I'm not mistaken, to post things to the internet. Is that right? That's right. Really, the interface is powerbi.com, which is just a website where you post your dashboards. And you can even create your dashboards on powerbi.com. You don't necessarily need the desktop app. It just has more features and makes it easier.
00:15:48
Speaker
But then once you have created that dashboard, you can share it with anyone in your organization and they can go view it. You're just basically sitting on a link to a web page and they can go view it and then interact with it as well, all on the website. So to drill down, to apply filters, those kinds of things they could do on powerbi.com.
00:16:10
Speaker
When it comes to interact with the entire ecosystem of Excel and Power BI, are they moving away from VBA to more of a JavaScript base? Is VBA going to die off, not in terms of it won't exist, but are they not going to support it or advance it as much? How is all this going to play out over the next six months, but let's say the next few years?
00:16:31
Speaker
Yeah, it's a really good question. I don't think VBA will ever die. And that might just be my own opinion. But it's just so heavily relied on by organizations around the world.
Future of VBA in Excel
00:16:45
Speaker
That said,
00:16:46
Speaker
And they are continuing to support VBA. VBA is supported in Excel 2016 and they're continuing to support it. So it's not dying by any means. There is JavaScript apps and add-ins that you can create as well. And they're definitely working on changing towards that model because they want Excel to be available on every platform.
00:17:09
Speaker
So you can't really run VBA on an iPad. And there's going to be people that argue that you could, and that it would be fine to do that. However, it makes sense for them to use JavaScript for this kind of stuff. So that will be a major player in this going forward. And especially with powerbi.com, that's mostly JavaScript powered there. It's very open source, too. You could even design your own visuals. If you know JavaScript, you can write your own visuals.
00:17:39
Speaker
Wow. Yeah, which is really cool. Also, they will open up the content packs as well, which means if you're an organization that produces data or you just have data that you want to connect to Power BI, you can create your own connector to Power BI.
00:17:54
Speaker
Yeah, it's different. I think it's really changing. The whole Microsoft structure is really changing. There'll be a lot more open, a lot more flexible, and people are excited about it. Yeah. And so how do you think this then compares now, or is probably a better question, is how it's going to compare to other tools that do sort of similar things? And I guess the obvious one is Tableau. But there's also other ones like Plotly and Highcharts and some of these other tools. I mean, some of them have varying levels of complexity.
00:18:21
Speaker
Do you see Excel getting more into Tableau's space with these new tools?
00:18:29
Speaker
Yeah, I would think so. I think there's, you know, to really break it down, I actually work for a company that uses Tableau. We use Excel a lot as well. And the models are definitely different. I mean, Tableau is obviously very expensive, high end product, does some things really well. And Power BI is definitely catching up to that. You know, they might not have all those features yet.
00:18:52
Speaker
But they have a different way of going about getting your data into Power BI and also how it's served. You can keep the data on wherever it's actually stored now and just refresh it.
00:19:05
Speaker
by pulling it into your visualization. So there's some different technologies there that will change how it works. But I think what you're going to see is that since Power BI is free, you can expose a lot more people in the organization to it.
Comparing Power BI with Other Tools
00:19:21
Speaker
Right now, in my organization, we can't necessarily
00:19:24
Speaker
go just give everyone access to be a Tableau designer, or even everyone on the finance team, or the marketing team, because of the licensing costs. Very prohibitive. But everyone has Excel, and everyone could get free Power BI, obviously. Exactly. I think even if you just start to mess around with Power BI and create a little dashboard here or something that might save you a little time,
00:19:53
Speaker
You're not invested in it and you're not expected to have to produce something to create a return on that investment as quickly as you would be with a paid product. So I think you're going to see a lot of people adopting it and using it if it's accessible and marketed properly.
00:20:14
Speaker
That's one thing the tableaus and those other tools have going for them is they have a team of people that are going to take you out to lunch and tell you how great their software is and expose you to it and give you demos and stuff. That's probably not going to happen with a free tool like Power BI.
00:20:32
Speaker
You're going to have people that within the organizations, you're going to have these people that are going to be the Excel geeks and those guys like me that are going to really have to go promote it and market it within the organization to get people to adopt it. Before we wrap up, we've talked about the future of Excel and Power BI. I'm wondering what does the next year or two hold in store for Excel campus and for the things that you're working on?
Future Plans for Excel Campus
00:21:03
Speaker
Yeah, great question. So I have a course on VBA that I launched last year, and I have another course on the lookup formulas as well, like VLOOKUP, like the one we talked about. And then I have ideas for a few more courses as well. They'll be coming out in 2016. And of course, I have the YouTube channel I work on, and then I publish articles and blog posts just for free, tips and tricks like that, so that can all be found on my newsletter as well.
00:21:32
Speaker
Great. And Power BI as well. I think, like you said, I'm going to start diving into it a lot more. Seems like it would be a natural thing to show up on your site. Yeah, it's so hard to write about it because the moment you publish something, it's changed.
00:21:47
Speaker
Yeah, that is a challenge for people. And it's the same thing with sort of the core functions of the 2016 packages. As soon as you write something, they are going to change it, or they're going to fix it if there's a problem, or they're going to put something else out. So for good and for bad, I guess. Yeah. It's fine. It's just like any other software tool. You just got to keep up with it. Keep up with it. Great. Cool. Well, John, thanks for coming on the show. This was really interesting. And good luck this year. I'm eager to see what's going to come out of your shop.
00:22:16
Speaker
Same to you. Thank you, John. I really appreciate being here. Great. And thanks to everyone for tuning in and listening. Again, if you have questions or comments, please hit me up on Twitter or on the website and please rate the show on iTunes. Thanks again for listening. This has been the Policy Viz Podcast.
00:22:44
Speaker
This episode of the Policy Viz podcast is brought to you by the Maryland Institute College of Heart. MICA's online graduate program and information visualization trains designers and analysts to translate data into compelling visual narratives. Join expert faculty such as Andy Kirk, Marissa Peacock, and John Schwabisch to mine the data and design the story. For more information, go to mica.edu backslash MPS in Viz.