In this special FP&A Today episode, Paul is joined by three Excel experts whom Microsoft has recognized as Most Valuable Professionals (MVPs). Learn why it is important to up your Excel game and how you can do it. Below check out offers (saving $$$) from our MVPs exclusively for listeners of FP&A Today.
Get all your questions about Excel answered by the expert panel:
George Mount, Founder Stringfest Analytics
Celia Alves, CEO, Solve and Excel Consulting
Mark Proctor, Excel Off The Grid
In this special episode:
- Our MVPs on when Excel first emerged as a difference-maker in their lives
- George Mount on the failure of finance to adapt to technology
- The challenge of finance teams descending to the lowest common Excel skill set
- The untapped power of PowerQuery and where to start
- The power of prepping your data properly
- Python vs Excel
- Automating Excel reports using Python
- How to deal with tricky Excel issues
- Will Excel go extinct with the rise of AI
- The thing that you absolutely could not live without in Excel
Special Offers Exclusively for FP&A Today listeners
George Mount
Subscribe to George’s newsletter to get access to his data analytics resource library and many other goodies https://stringfestanalytics.com/subscribe/
Mark Proctor
https://exceloffthegrid.com/fpa is an exclusive offer for our training academy to the listeners of FP&A Today – 30-day full access trial for $1, followed by a $100 annual discount. This is valid until 31 May.
YouTube: https://www.youtube.com/@ExcelOffTheGrid
LinkedIn: https://www.linkedin.com/in/markstephenproctor/
Celia Alves
Celia offers an online course about automation of Excel Reporting procedures. Check out the special FP&A Today discount here https://snapreportschamp.com/course
DISCOUNT CODE for 20% OFF: FPA20
(valid up to June 4, 2023).
Subscribe to Celia’s newsletter https://snapreportschamp.com/subscribenewsletter
Celia Alves – Solve & Excel – on YouTube https://www.youtube.com/CeliaAlvesSolveExcel
YouTube video of today’s episode
Follow Paul Barnhurst on LinkedIn
FP&A Today is brought to you by Datarails.
Datarails is the financial planning and analysis platform that automates data consolidation, reporting and planning, while enabling finance teams to continue using their own Excel spreadsheets and financial models.
Get in touch at www.datarails.com
Full transcript follows
Paul Barnhurst:
Today we’re going to talk about how to improve your Excel skills. Joining me here in a few minutes, they were on the other broadcast and they’ve had to switch over. Will be Celia Alvez, mark Proctor, and George Mount. All right, great. We have George here, and I’m going to throw George one question while we’re waiting for the other guests to get on. I appreciate your help here, George. Sure. So maybe we’ll start while we’re waiting better. Can you tell us a little bit about yourself and your background and when did you first realize that Excel could be a difference maker for you in your daily work?
George Mount:
Yeah, absolutely. Well, thanks Paul for putting this together. I think we’re seeing the testament of technology, its powers and also at the doubles cited sword of its nature. But for me, Excel started back when I was a new financial analyst. A lot of you started in that role and did not have the preparation to succeed in that coming from more of a liberal arts kind of background. I just remember, I think there was one particular task where my manager shaded different rows with a different color and wanted me to be able to sort by color. So I actually went through and manually sorted that whole worksheet by color. It took me a couple of hours. I didn’t know that you could sort by color in Excel, right? And there was just constant things like that, that I was just spending hours and hours and getting nowhere and making mistakes and giving the wrong numbers to people.
And I was just not really fulfilling my primary role, which was to make life easier for the people who I work with. And that really got me thinking about the power of Excel and from there, just the power of right, automation and data analytics in general. So really I’ll started out with just that primary motivation to give people what they need in a timely and accurate manner. And I think the Excel is so powerful in that front. So it’s really cool to see so many people, including our two fellow MVPs here tuned in around the campfire talking about this stuff today.
Paul Barnhurst:
Yes, thank you, mark and Celia for being able to join us again. As I told everybody else, I’m not sure what happened, but when I tried to log in, it told me the broadcast had ended and it won’t let you re-log into a LinkedIn live broadcast. So we had to just go live on LinkedIn. So thank you for getting back here with us and looks like we got most of our audience, or at least quite a few people back in. So George did a little bit of an introduction there about himself. We’ll go to Mark next. Mark, can you maybe give us a little bit of your background, and then when did you first realize that Excel could be a difference maker in your daily work?
Mark Proctor:
So my background is I’m a qualified accountant. I qualified back in the early 2000s, and to be honest, in most of the 200s, I thought I was pretty awesome as at Excel because I knew V look up, so therefore I was golden, right? I knew V look up, therefore I didn’t need anything else. It wasn’t until I started a new role in 2015 where I’d already planned a vacation before I started this role. And it turned out that I needed to save a day and a half on our five day reporting cycle, which was going to be a bit of a struggle. So I thought, you know what, I’m going to give macros a go. And within five hours I wrote a macro that saved me at that day and a half, and then was then used in that business every month. So that five hours has probably saved a thousand hours worth of effort.
So that was the real first taste of what could actually be achieved. I then went into a, it’s called a global reporting analysis role for finance, which is a fancy term for fp and a. And through that we had our month end packs and everything else, and we had month ends that we’d work till 11 o’clock, one o’clock in the morning just trying to get everything done and it was a pain. And then through that I was like, we’ve got to find a better way. And eventually it was like bit by bit, we chipped away, it created tools and then we could automate everything from all the way from here’s our inputs, click refresh goes through, it creates updates, your Excel documents. It then goes through, creates PDFs, it creates emails, it sends them out, you know, can do all of that from inside Excel. So it’s not just a tool for calculation, but actually your complete end-to-end can handle it. So that’s kind of my background. And for the last eight years since I started that previous role, that’s what that’s been doing, trying to save as much time as I possibly can to spend more time doing something else more enjoyable.
Paul Barnhurst:
Thank you, mark. And Celia, will you want to give us a little bit about your background and when you first realized that Excel could be a difference maker in your daily work?
Celia Alves:
Oh, thank you, Paul. My case is a little bit different because the work that I was doing did not require much of Excel. I almost accidentally got acquainted with Excel in around 1994. My first year as a high school teacher, I went to a teacher’s, my first teacher’s conference, and I happened to choose one of the workshops to participate in. It was about Excel, maybe two or three hours workshop, I can’t remember. So I was born and raised. I was raised in Portugal and I studied mathematics. That’s my background, academic background, I got a associate degree and a master’s both in education and mathematics. And I started a teacher’s teaching career. I taught mathematics up to grade 11, grade 12, seven to 12 for about 19 years. And so I got acquainted with Excel my first year as a teacher. I figured it all out of times.
So I thought, okay, this is Excel, I understand the formulas, I understand that I can make a chart, couple of things. And I used what I learned that day during years and years, and I felt like the queen of Excel when I would enter those teachers meetings with all my students marks and averages and charts representing them, nice spreadsheets printed on paper because we were not carrying laptops at that time to our meetings. And that’s what I was doing with Excel. And then 2000 2013, I moved to Canada with my husband and kids. And in 2015 I decided to try freelancing on online. And I decided that I was very good at Excel. And my first gig, I got acquainted with V lookup. So I beat you, mark. I was able to work for 20 years without even knowing, using Excel, without even knowing about V lookup.
And then I discovered that the task I was given, I thought I could give, do it with the lookup like Mark. He thought the lookup solves everything, not in that case. So I started with D B A, I discovered forums in Excel about excelling online YouTube videos, and that’s when my journey started. And later I got hired by a company to help them with their Excel systems that were already in place to improve them. And I learned a lot from there. So that’s when my journey about learning how to automate tasks in Excel started those two events there.
Paul Barnhurst:
Next question I’m going to throw over to you. To you, George. So you started your career in finance, if I remember right. And you’ve commented previously that when you worked in finance you were disappointed with technology within finance. Can you talk a little bit about that? What was it that you were disappointed? Where do you think finance was falling short?
George Mount:
Yeah, it’s kind of wild. So I started in finance maybe 10 years ago, and at that time, all this about data science and machine learning, data analytics, it wasn’t really a thing quite yet to the U common everyday office worker. They hadn’t really heard those terms before. So it was finance had such an opportunity that they just kind of let go in a lot of ways, particularly just having that mindset of if something can be automated, then maybe we should think about it. Not everything should be automated, but there are a lot of things that should be thinking about really looking at data as an asset that can be used and reused and given life to help guide the business. And I just didn’t really see that mentality in the departments that I was in mean to be fair, I think especially back then, a lot of organizations, a lot of departments weren’t really thinking like that, but that really got me into data analytics more so than finance, just because I found it a place to really dig into the tech technical aspect, learn more about programming, learn more about statistics, and make a bigger impact in the organization.
So it’s kind of cool to see finally now finance is starting to see what it can do in this arena. It took it a few years, but I think that having the finance background personally has been helpful for me because we do have a lot of people, especially in the Excel world, there’s a pretty strong overlap between right, finance and Excel. So we’re still pretty well poised or finances to make a big impact when it comes to data analytics and automation.
Paul Barnhurst:
I agree. I think one of my favorite stories, I remember early in my career, I took a V B A course and it was taught, it was one of his working at American Express, and the guy who was teaching it shared this story. When he started his career, the guy still had a 10 key on his computer and next to his computer, and the guy entered all these numbers in the Excel spreadsheet and then he added ’em up all on his 10 key and wrote them up number down at the bottom. And the new guy said to him, I’m pretty sure you can use this something. I’m pretty sure it’ll add it together. He’s like, I don’t know about that, but this is how we do it. And sometimes I feel like that’s what we do with Excel or finances. We just stick with the way we know and it’s often time consuming and very inefficient. And so one of the things I’ve seen with Microsoft, and I think many people have seen this is over the last decade, Microsoft has made massive updates to Excel. We’ve seen Power Query dynamic arrays, Lambda Power Pivot LA and more. But it feels like despite all these improvements, many people are either not using ’em or not even aware they exist. Mark, any thoughts why that is?
Mark Proctor:
There’s a phenomenon that I think exists in workplaces where everything returns back to the lowest common skillset. So what happens is that somebody starts a new job, and let’s say they are, they’re all up to date with everything that Microsoft are doing. They’re fantastic at Power Query and everything else. They’ll implement whatever solutions, they’ll come along, this is the way we should do it, they’ll improve things, eventually they’ll get bored or maybe they get promoted out of that role, they go somewhere else. And then from there what happens is that they then leave that job, someone else comes in who has less knowledge or less experience, they don’t understand it, and therefore they start to change things back to how it was, which means that things always end up reverting back to that lowest common skillsets that people have. And so really it’s about a training piece.
It’s about making sure that people have consistent training to stay up to date. The problem is that also within workplaces, people say people have an issue an Excel issue. So they ask their colleagues, how do I do this? And their colleague only knows one way to do it, and that’s the old ways. So those ways tend to get replicated over and over again. And with Microsoft, even though they create these new tools, they like, here’s a tool, go and use it. It takes a long time for that impact to really happen. And we see that with Power Query. I mean, it’s been around for over a decade now or about a decade, and yet it’s now starting to pick up more and more pace. So it needs that adoption and knowledge before people even accept it. And I think it is a case of why when something works would you try and look for a different solution. It’s only once you’ve really got a problem that you then look for something else. Unfortunately, I think it’s inherent in how businesses operate, how people operate, that it just doesn’t get adopted as quickly as it should.
Paul Barnhurst:
Yeah, no, I agree. And I like the point AJ said, I put it here on the screen, the issue is tribal knowledge and living in silos, right? Yeah. As you said, people know how to do what they know. Making a change means taking a risk. Sometimes things can go sideways. So I definitely think there’s some truth to that. I think a lot of things you said as well. Celia, what’s your thoughts on that? Why do you think the adoption takes as long as it does?
Celia Alves:
Yeah, I agree on with Mark and I made it me recall a situation where I was trying to implement something in an organization with a client and they say, oh, people are not Excel savvy. We need to make it simple. So there’s always these pushing back, trying to make things easy because otherwise they will get problems that something will stop in the middle of the process. So they tend to, like Mark said, lower things down. And then I also think there’s a lot of lack of inquisitive inquisitive mindset, curiosity. I mean, I just told you my story. I went off to use Excel the way I learned it that day for 20 years without questioning, can you do something else? Is there something else? I didn’t even bother to explore all the buttons that were in the ribbon that I was not using.
I was just using what I knew the best I could. And of course we are always busy, so we just want to, I’ll leave it to the next time and then the next time never comes. I also think that Microsoft does not do a great effort on marketing the product. So they do put things outside MVPs like us, we do our best to tell the world about the new features available, but if you are not already aware that there is something out there that maybe you can hear from this person or that person that you follow online, you will never get to know about it. So it’s kind of a vicious cycle there.
Paul Barnhurst:
I agree. And I think, I know you guys do a great job of socializing new things, the MVPs, and we see LinkedIn I think has helped a lot for people that get on LinkedIn. They can see all the different things from Excel and people announced the new Excel things, but Microsoft itself hasn’t done a great job announcing it. I mean, I remember the first time I heard about Power Query and Power Pivot, I briefly heard about something, I don’t know, it was 2015, 2016, but I had no idea what they were. I didn’t even touch Power Query till 2017 while I was trying to figure out a project. And all of a sudden I’m like, oh wait, this has been here for a couple years. How did I miss this? And it then became something that I used all the time. And so let’s jump into Power Query.
BecauseI think in my opinion, that’s one of the biggest opportunities for finance and fp and a professionals to automate streamline work. I see George nodding his head there in agreement, so that’s good. So studies have shown FP&A professionals spend up to half their day wrangling data, and if that’s one of you out there just comment saying yes or how many hours, I think we talked about how many hours you spend in Excel. So what are some of the things, I know Power Query is one, but Celia, what are things you’d recommend people learn if they really want to get better at dealing with data, whether that be pulling files together, kind of that transformation, extracting data. What’s your recommendation for ’em?
Celia Alves:
The first thing, even before starting dealing with power learning Power Query, I think is understanding the difference between the two layouts, the layout to store your data and the layout to represent your data. Finance people and other professionals. But in this case, we are talking mostly for financial. People tend to want to see timelines with the months and the years along the columns, and that’s not the way you should store your data. But what happens is that a lot of times people tend to input the information the way they want to see it at the end. So for example, they are recording expenses and they go, oh, I have January, February, March, I have three expenses in March. So they go to the cell for March in that category of expense and they put in a formula equal 20 plus 100 plus 200, and then it’s a nightmare having to scroll back and forth to find the correct cell and to put in the information.
That’s not the way people should do it. People need to make their minds to prepare their minds to work with the tabular layout. Tables, rows and columns is the way to store your data. So if you have three expenses for March, you need to have March 1 this amount, March 10, this amount, March 15, this amount, and then you use other tools to aggregate that information to summarize it and represent it the way you want. Then you can use Pivot Tables, then you can use Power Query to modify the layout and give you it the final layout that you want. So that’s my first advice is start with start by understanding why you need to learn and use tables in Excel and then go to pivot tables and Pivot Power Query.
Paul Barnhurst:
Thank you so much, Shirley. I love that you brought up tabular data and tables. Learning how to set your data up properly is critical for pivot tables for Power Query for Power Pivot. When I teach my courses, I often say, if you want to unlock modern Excel, you need to understand first how to structure your data in a tabular format. Because if you don’t learn that, you’re missing out. The habit we often do is we want to summarize it, we want to put it in the format, we want to see it, but what we want is we want it in kind of almost like a database format where each record is its own row so that we can summarize it so we can work on it and transform it.
[Datarails ad] You know what it is like 13 different spreadsheets emailed out to 23 different budget holders, multiple iterations, version control errors, back and forth updates you never really feel in control of the consolidation and collection process. Yep, I’ve been there. Stop. Breathe.
Data Rails is the financial planning and analysis platform for Excel users. Data Rails takes data from all your company’s disparate sources. No organization is too complex, consolidating everything into one place, secured in the cloud now all your data finally talking to each other, everything is automated back into your report in Excel, cash flow, FX conversion, intercompany transactions now automated and up to date, drill down and variance analysis in seconds. Don’t replace Excel, embrace Excel, turn your Excel into a Lean mean FPNA machine. Find out more@www.data rails.com. -Anyone who follows me knows I’m a huge fan of Power Query. And so George, we’ll ask you first and then we’ll go to Mark. Can you just give a summary maybe of what Power Query is, a little bit of what the tool does and why people need to learn it?
George Mount:
Yeah, I can definitely do that. And before I even start with that, with this topic of the primacy of having your data in a machine readable format, I know AI is all the rage right now and I have a little demo I’m working on that. I’ve showed in a couple workshops already that even with the analyzed data feature in Excel that lets you query your data using natural language and brings these machine learning insights into your data, that doesn’t even work if your data is not in a proper tabular format. So that skill is not going to go away just with AI and everything happening. You’d need to have your data in a proper machine readable format. So that really is before we talk about any of this, so important as far as Power Query goes, it really is the repeatable data cleaning tool inside of Excel.
So if we think about this ETL process that some of you may have heard of, it’s a lot fancier sounding than it really is. You extract your data, and I think we’ve all done this, right? Think of all the places and if you want to leave in the comments places where you eat your data from, whether it’s Data Warehouse, maybe you’re going to Salesforce, maybe it’s just a series of CSV files or maybe you have a whole folder of a bunch of CSV files, wherever that data’s coming from, you can bring it in via Power Query and then do all those things that we all know and love week after week. If you think about all the filtering and the right sorting and maybe you need to merge some tables together or whatever, those kind of manual tedious tasks that you do, and sometimes you get in trouble because you forget a step one week and then the whole thing breaks or maybe somebody’s out of town and you can’t talk to them about what they did,. PowerQuery is going to lose a lot of those frictions for you because everything’s going to be auditable.
You can literally see step by step what’s happening. I sorted my data here, I renamed the column here, so there’s not going to be any more gotcha surprises about what happened. And then from there you can load your data into a variety of formats, including if any of you all are using Power Pivot or the data model, you may have heard of that. To build a little bit more robust of a reporting structure in Excel. The really good news with that, I mean if that doesn’t sound cool enough, this is all very applicable to Power BI as well. So if that’s on your learning roadmap as well, you really get a two for one deal here if you’re going to learn Power Query because a lot of this stuff is equally applicable. It’s really the same engine that’s being used there in Power Bi. So I’m sure everybody has their own way of explaining Power Query, but for new people, I just like to use that kind of ETL structure. It’s been around for a while. I think everybody knows the pain of that manual weekly report and when they hear, oh my God, I can just build a menu that people click and it runs week after week. I think that that’s a pretty good sell without even seeing how easy the product is to use.
Paul Barnhurst:
Thanks George. So Mark, what would you add about Power Query and using it?
Mark Proctor:
So I would say mean, I’d step back and let’s step back a stage and go, right, what’s the context in terms of FP&A? What’s the blueprint that we are trying to follow in terms of all of our work and ultimately everything that happens in that finance realm in terms of reporting and analysis, there’s an underlying blueprint or method that we follow. So we start with our inputs, we then reshape our inputs into data. We take that data, we calculate on it, we take those calculations, we visualize them, we collect those visualizations together in some form of presentation and then we distribute it. So everything we do falls into that pattern and we might go from input up to a visualization stage, there might be a journal or an adjustment or something else. We might then push that back into that input phase and ultimately we go through this loop over and over again.
So PowerQuery fits in that piece that goes from your inputs to then creating your data. So it’s right at the start from taking your input and changing it into something useful. So if you want to automate anything or change anything, that the bit at the start is where that happens. The analogy that I often try and use is if you want to decorate your house, if you want to paint the room in your house, if you’ve got an enormous hole in your wall, the first thing you shouldn’t do is start painting. The first thing you need to do is to fix the holes and then plaster it. And then once you’ve got this smooth surface, you can then start decorating. What often happens is that people take their data and their data is like that wall with an enormous hole in it, and then they take it and they start using formulas, which is just going from a hole in a wall to painting.
What they need to do is to get that smooth surface first, they need to get that data into an optimal layout so that the painting, which is the formulas, are then exceptionally easy. And that’s the thing, if you set up your data correctly at the start, the formulas you need and the complexity of you need suddenly reduces down to a much lower level. Actually the Excel, if you use Power Query, the Excel skills you need later on are significantly lower than if you don’t use Power Query. So you’re doing yourself an enormous favor, and actually you need less Excel skills later on because you’ve prepared everything in the right way. So that analogy of it’s really just trying to get that smooth surface and everything in the right place before you even start decorating is the way I try and describe Power Query. And the next week, the next month when you start on your painting, another room in Power Query, you click refresh and it automatically fills those holes and you’re ready to start painting all over again. So that’s how I would describe Power Query in as that form of analogy.
Paul Barnhurst:
I thank you. I really appreciate that analogy you gave there. I had never heard it that way, but right, filling the hole first, getting everything smooth before you paint and it makes it much easier, which is really true if you clean up all your data, it’s much easier to work with than you’re trying to do it all with these complex formulas. Anyone who’s ever written a long formula to try to clean up data mid left, right, fine, you get the substitute and you get this big huges long formula where you’re trying to do something that might be quite easy in Power Query, for example, learning it. So I can definitely relate to what you said there, MarkAnd so I’m going to ask each of you here, if someone’s never heard of Power Query, where would you recommend they start? Kind of that learning journey. And George, we’ll go ahead and start with you.
George Mount:
Yeah, that’s a good question. I mean, obviously going to one of the MVPs here is a good start. I have a white paper on if you’re an Excel user here, five things to Know about PowerQuery. I’m sure I could distribute that in some way, maybe in the show notes for the podcast or something like that. But yeah, think about, I mean also to Mark’s point, right? Think about where are those lumps in your wall right now? What are those steps that you maybe forget or that could caused your whole project to go crashing down when you try to update things. I think having that kind of wishlist, right, because you do want to begin with the end in mind is important. And then from there, starting to see getting that mental model behind what Power Query really is and where it fits in that workflow is pretty important. A lot of good resources for that in terms of the tactics, I’ll definitely throw my little white paper in the mix. And I know our fellow MVPs have other resources they’ve liked to share as well.
Celia Alves:
One thing that I want people to be aware of is Power Query Lives inside Excel. And when you learn how to open the Power Query editor, it looks a little bit like the Excel grid, but it’s not the same. Then there’s a different mindset, a little bit of mind shift that we need to do because in Excel we can treat our data cell by cell. We create a formula that looks into that cell and does something to that cell to produce and maybe with other cells to produce the final result. In Power Query the logic goes mostly in column. So when we set an action to happen to something, to our data, most of the times we do it to happen to the entire column. So the reasoning is a little bit different. We also, power Query is a tool that allows you to import data from outside, from inside your Excel file or from outside.
You can connect to other Excel files, you can connect to CSV files, you can connect to databases and say, give me the data, import the data, bring it here. But in the way, I want you to clean that for me. I want you to remove certain rows that I don’t need, remove certain columns that I don’t need, transform certain columns because there’s information in one column only that needs to be split out by different columns. And all that happens in the way coming into the Excel file and all that is automated like George was explaining before and Mark. And the next time you prepare that automation once, next time you click Refresh All or you click a button to refresh a query or a couple of queries and everything is repeated. So you only do that work most of the times once unless you need to make improvements to it.
So what I mean with all this is that Power Query is easy to learn, but there’s a couple of things to figure out. When you are importing the data from your source, you are doing all these transformations and sometimes people ask me but I don’t want the rows to be removed from the data source. Yo no, don’t worry, because the data source is not touched. So that’s the beauty of Power Query. It goes to the data source and transforms things in the way when they come to you, data is cleaned, but the data source was not touched. So all these different things require a little bit of mindshift from when compared with what we are used to when working in Excel. And to save you time, I recommend that you find a course, a structured course about Power Query. There’s plenty of them out there.
I have a course, Mark has a course, we have great YouTube channels with a lot of free content there. I run an Excel Meetup group, the Toronto Ms. Xl, Toronto Vita group that you can join for free every month we have free sessions. Some of them are about Power Query, they’re, the recordings are available for you. People who are looking to learn to sit in action, to have the materials, you can get those. So really start by practicing understanding what it is. And to save time, I do recommend that you find a structured course because it’ll allow you to connect the dots faster and start making good use of what you learn.
Paul Barnhurst:
Thank you, Celia. And I’m a big believer in courses, books. There’s a lot more than just reading people’s blogs or watching YouTube videos. There’s some value in the structure that you get from that. So I appreciate that. Celia, Mark, what would you recommend if someone wants to start today on Learning Power Query?
Mark Proctor:
So Power Query is easy until it isn’t. Okay. When you first start, you’re like, I can get this data, I can do this, I can do that. I’m almost jealous of the people that can get their data from a structured database because you know what? You can pull that stuff in and it’s already in a pretty good format. The problem is that often we are connecting to Excel files. We have multiple header rows, we have a lot of transformation and a lot of cleaning. We have, you know, get exports from accounting systems. Columns might be in different places or with different names. So there’s actually, initially Power Query seems easy and it is easy, but once you’ve got past the point of I can get stuff in, there are a lot of more complex transformations. And that’s where if you just stick with YouTube or blog posts, that’s generally where most of them tend to finish.
How do you combine all the files in a folder? Celia’s got on her YouTube channel, she’s got loads of stuff about dealing with tricky problems. I’ve got loads of stuff on my YouTube channel, my blog about dealing with tricky problems. George, you’ve got loads of stuff about doing the tricky problems. Yeah, he’s nodding, see he has as well, it’s those tricky problems that then frustrate people, right? Because they start learning PowerPoint, it’s easy, and then suddenly they come across a format that it just doesn’t work in that simple context. And that’s where if you have a structured course that actually you can, then it helps to join those dots. And it’s not just about clicking a few buttons, actually, you might have to go a bit deeper and actually change some of that M code. Nothing difficult, nothing hard, but you have to understand the basic concepts.
And if you don’t, you’ll get to a point of power Query where you’ll get stuck and you’ll go, it doesn’t work for me. And then you’ll go back to the old methods and all your chances of automation will then disappear completely. So I would say, yeah, you can start searching Google, you can search for blog posts and YouTube channels. There are, there’s lots available, but books and courses are the only way that you’ll ever draw all those bits together. As somebody who writes blog posts and puts things on YouTube, it’s only ever a thousand words. It’s only ever 10 minutes. I’m only ever trying to show you how to solve one problem. In a course, you get that context of, well, how do you solve multiple problems that all come along together at the same time? And how do they fit together and all those positive learnings. So that’s I think, the best way to learn. And ultimately on the course, you can learn something in seven hours, 10 hours that on YouTube you’ll just be hunting for days, you’ll just be watching YouTube video. No, that’s not what I want. How about the next one? No, that’s not what I want. So actually it saves you a huge amount of time and it’s much more efficient. So that’s what I would suggest
Paul Barnhurst:
Thank you, Mark. I appreciate that. This next question is for George. I know we talk quite a bit about Power Query and I know people when they get into Power Query, Power Pivot, they often want to go beyond that. They’re like, okay, what else can I do? And you wrote a book about advancing into analytics and from going from Excel to R & Python. So if someone wants to go beyond Excel, what’s the advice you would offer? I mean obviously you’re going to say buy your book. What’s the advice you would offer people?
George Mount:
They could go to the website and read a trial though anyway? Yeah, I guess so the whole thing with Python particularly, I get asked about it a lot. I wrote the book about it. If everything you’re doing is working with Power Query and you’re using the M language and everything is clicking in Excel, you don’t see a need for it, that’s cool. I’m in the US, it’s a free country, you could do whatever, but if you find a reason that maybe something’s working, not working well or you have that little itch your conscience is telling you there, that’ll be a better way to do this, try it out, right? That’s all I’m saying. Python is a great place to start. There are some ways to automate Python, I’m sorry, automate Excel with Python. Obviously if you’re looking to get into more advanced analysis, machine learning and stuff like that, it’s an option.
I can kind of hear the crowd, the throngs of, well, why would I learn Python when there’s Power Query? And it’s an absolutely good question, but the only thing I’ll say to that is it’s not in Excel right now, unfortunately, but if you go into Power bi, the Power Query editor in there has an option for inserting a Python script. So it’s there for a reason. I think that the product folks at Microsoft probably know the product better than we do. So that’s all I’ll say about the matter. And you can make your own choice because I think that trying to sell Python, I don’t know people are going to either do it or you don’t sell to somebody who’s not ready to be sold to. So that goes for the book as well. But yes, it’s an option. We’ll leave it at that.
Paul Barnhurst:
All right. So we had a question here and I’m going to throw this one to you, George. I’m going to put it up here. So someone asks, how can the automate Excel reports using Python? Looks like I use Query, but I only know the fundamentals of Python. So I think you talked a little bit about that. So where would you recommend they start if they’re wanting to bring a little bit more of Python into what they’re doing?
George Mount:
So I’m the big fan of books. That’s my preferred way to learn. There’s a great one by, I don’t think I have it with me by Felix Zoom stain called Python for Excel. It’s another O’Reilly book. It’ll look like this, but it’s got a snake on it. It’s not actually a Python. So people get confused about that.
Paul Barnhurst:
That. I know which one you’re talking about. I’ve seen that book The Snake on it. Yeah,
George Mount:
It’s a really helpful one. There are a lot of possibilities. Maybe there are things that, I mean the nice thing with using Python is it’s one language. So whether we talked about, mark kind of talked about this whole workflow of you’re bringing your data, you transform it, you’re going to build your visualizations. It does feel a little bit siloed at the moment in Excel, right? Because the way that you build your charts is different than the way that you build your formulas. It’s different than the way you build your data model and everything’s in a little different of a bucket. It’s hard to integrate that. Sometimes Python’s really good, it’s even called many developers call it that glue language. So whatever you’re trying to do in that workflow, Python’s got the option for that. So I definitely check out that book. I would look at maybe starting with one slice of that process because there are ways to maybe do one thing in Excel and another in Python.
So take the tricky parts of your process, see if you can automate it and it is going to take some time. So I guess the other word of advice if you’re looking to Python is like don’t uninstall Excel. That’s not what anybody’s saying. And you’re not going to hear obviously from MVPs that this whole Python is better than Excel and you don’t need to use it Excel anymore. Don’t listen to that either. So yeah, don’t listen to that. Also, don’t think that you have to learn Python or that it doesn’t have a use either that it’s worthless, it’s somewhere in the middle for everybody. There’s like a spectrum. So you’ll just find where you fall on that spectrum.
Paul Barnhurst:
Great point. I think that a good point is you find out where you fall and what you want to use it for and how much you want to dig into it. Because there’s places it can help. There’s places where Excel is very important and I like how you point out don’t uninstall Excel because, well, I’m learning Python now, so I don’t need Excel. They can be used together, they can be complimentary tools. Just like because you’ve learned Power Query doesn’t mean you have to forget how to use a lookup because you can do a join or forget. Yeah, formulas, you still use it all in conjunction. And then just a few people had pointed out here and I just want to mention a few of those as we’ve had a lot of resources mentioned in the comments. So one person mentioned some great Ken Pulses, Matt Ellington and Miguel Escobar have some great stuff on their Skill Wave website.
I’m also a big fan of Ken Pulse’s book Mastering Your Data with M think is what it is called now. The version I have is still the original Data Monkey version, but that’s a great one and there’s a lot of others. I have a few books back on my shelf here around that. I know some other people mentioned Leila Gharani, Chandoo is another one that was mentioned. So there’s someone else said the Excel MVPs, the three here are great ones to learn from and I’d encourage it all to go to their websites. You can see them mentioning their businesses here, but there’s also many others because we all have different people that we gravitate toward for whatever reason, they may, we feel comfortable with their learning and the way they teach. So there’s plenty of resources out there. So we’re going to do just a couple more questions and then we’re going to wrap up here. But one I want to ask is, so Excel is coming up on 40 years. We’ve all seen AI, I’m sure all of you have probably played with Chat GPT or Bard or seen generative AI and all these developments kind of transforming technology. How do you think that will change Excel? And do you think there’s any risk that Excel may go away one day and we’ll start with Mark and I by one day, I mean in the near future? Anytime.
Mark Proctor:
So the answer to that short question is I don’t believe Excel will be going away anytime soon. The biggest thing with artificial intelligence AI is that if you mix artificial intelligence with actually intelligence, then that fantastically augments our ability to do work and to be efficient. Unfortunately, if you combine artificial intelligence with actual ignorance, what you do is that you create this enormous snowball of a problem. And what I can see as a future is that people who have relied heavily on AI, they’ve created their solution, here we go, they’ve delivered it. Their line manager next week then says, ah, can you update that to change this one bit? And at that point they don’t know how they really solved that first problem, how they then change it and iterate over it. They’re then relying on something that they didn’t understand to start with. And I think that’s the biggest issue in terms of AI.
If you already understand the topic, have a base understanding of how things work, then AI can help you be more efficient. But if you don’t really have that understanding, you need to use it more of a learning tool. The problem is that it’s a learning tool that openly says this may give you the wrong answers. So as a learning tool, it’s there but has some limitations. I think in terms of Excel, it’s all about augmenting our actual intelligence rather than us taking entirely from artificial intelligence. Because if we just rely on that, then we’re in real trouble. I mean, even when you’re building a data model, what field should be connected? What fields shouldn’t be connected? All of those things are, if they get it wrong, it causes a huge load of pain. And I don’t think any business in their right mind would want to devolve all of that decision making to something that doesn’t understand the context of what it’s responding to and the consequences of that response. So Excel isn’t going away anytime soon, but AI can definitely help us. If we’re applying actual intelligence, we’re applying actual ignorance, then we’ve only got ourselves to blame.
Paul Barnhurst:
Well said Mark. I think you summed it up very well. There is combining human intelligence with artificial intelligence can make us more effective. But when you combine it with ignorance, you’re just going to end up with problems because you have to be able to validate that it’s right. You have to know how to ask it the right questions, there’s a level of knowledge you need to take advantage of it. And so I really like that answer. So the one question you want to ask, and we’ll start this one with Celia, what is the one thing in Excel that you cannot live without? If you had to pick one, what would it be?
Celia Alves:
Tables.
Cell tables.
Cause I wanted to say Power Query, but with Power Query I can do build tables. So maybe Power Query. It’s so hard, I can’t pick one. It’s like asking me to choose one of my children.
Paul Barnhurst:
I figured you might say that. I get it. So tables in Power Query, we’ll go with those. We’ll give you two. We’ll go with those two. George, how about you?
George Mount:
I had a very similar process. I think tables kind of are the Ohio’s, the heart of it all. Tables are the heart of it all at Excel. So I think I’ll go with that for now.
Speaker 1:
All right, mark,
George Mount:
I’m just going to refer to my Learned, you know MVP colleagues here. Yep, I would agree with them. Hundred percent tables, master that jobs a good one.
Paul Barnhurst:
I’d probably say the same myself. Tables and power queries. So anyone else, feel free to jump in and mention what yours is, but just as a final kind of parting words, if people want to get in contact with you, anything you want to share with the audience. I know some of you had mentioned some offers, ways to get ahold of you, we’ll give you each a chance to just go through and share that with the audience here and we’ll make sure we put anything you want in the show notes. George, your article, I know Mark, you sent a special offer as did you, Cecilia, on some of your courses. So those are out on my website and we’ll also put those next week in the show notes. But maybe we’ll start here on this one. We’ll give George first opportunity, any kind of final words or how can people reach you if they want to learn more about you?
George Mount:
Yeah, not Chat GPT, because it’ll tell you that I wrote a book on accounting in 1967 or something. The best place is either on LinkedIn where we all are now you can just click through. I’ve left a few comments, so go ahead, click through there, follow me, connect if you wish. I also blog pretty regularly stringfestanalytics.com, so it’s just my business name without the space. If you go there, subscribe, I’ve got a pretty extensive resource library with all sorts of outlines, checklists, anything that you could imagine for building your analytics arsenal in terms of what to learn, how to learn it and so forth. So I think those are the best sources for me.
Paul Barnhurst:
Right, thanks. Thank you George. Mark.
Mark Proctor:
Yeah, in terms of me, so pretty much everything runs from Excel Off the gGrid.com. So there’s six years worth of blog posts, seven years worth of blog posts there. Now starting in the early days with VBA A and then since about the last six years I’ve been heavily on Power Query and how we automate our workflows. So there’s also links there to my YouTube channel and you can follow me on LinkedIn. Happy to connect there. If people want to contact me directly, there is a contact form on Excel off the grid.com in terms of a special offer. If people want to find out more, then if they go to excel off the grid.com/fpa there, they can download. I’ve got two free of my eBooks. You can download one on macros, one on dynamic arrays that people can have for free. And then there’s a special offer for if people want to join my training course, which is how we automate Excel from that first point of input going through those seven stages, including query all to the way to how we distribute at the end. So that’s that’s there if people want to take advantage of it, excel off the grid.com/fpa.
Paul Barnhurst:
Great, thanks Mark. And Celia, we’re going to go ahead and give you last word.
Celia Alves:
Oh, thank you. So I’m on LinkedIn. I share my content, other people’s content news about Excel on LinkedIn. I have my newsletter that those of you interested in knowing when Excel releases something new can subscribe. And I also tell about any Excel events that might be going on that people might be interested in participating in. So that’s the newsletter. There’s the Ms Excel, Toronto meet up group where you can learn every month from a new presenter about a different topic. And you can have the recordings on my website that is the displayed there under my name solving excel.ca. You can connect with me there. I’m also on Instagram if you like, more the kind of a short tips. I also publish those on Instagram and there’s 20% off for my course about reporting automation, of reporting procedures in Excel that you can grab and take advantage of for a couple of days. I believe Paul has the link for that. If you cannot find information, feel free to connect on LinkedIn and I’ll give you the information.
Paul Barnhurst:
Thank you Celia. And I just want to give a special thanks first to our guests here. Thank you George, Celia, and Mark for joining me. And to the audience, thanks for staying patient. As I know we had a few technical issues and it took us a little while to get started here. But thank you everybody for joining today and please feel free to reach out to any of these with questions you have or myself and we’ll look forward to seeing you next time. Thanks. Thank you. Thanks Paul. Thanks everybody.
