At Capital One, we don't just use technology, we architect it. Our teams have true end-to-end product ownership, shipping distributed systems that handle high concurrency at scale. No single server bottlenecks, engineered beyond legacy downtime, and operating right here in downtown Toronto. That's technology at Capital One. Search technology at Capital One Canada.
This is the MyExcel Online Podcast, episode number seven. Lens and data that is like Hercules being sent out to capture that three-headed dog that guards the interest of hell. Welcome to the MyExcel Online Podcast, the need to know Excel insights, knowledge, and tips brought to you by the experts that know them best. Are you ready to explore your full potential and get better at Excel? It's time to stand out from the crowd with your host, John Mikaludis.
G'day guys, and welcome to the Microsoft Online Podcast with your host, John Mikalutis. In today's episode, we have an Excel guru who is an expert in data analysis and cleansing. He's worked with data and Excel for more than 10 years, including time spent as a financial analyst. He's been a Microsoft MVP since the start of this year, and he's a co-host of Excel TV. He's a leading author on the revised version of Guerrilla Data Analysis, a book originally written by Mr. Excel, Bill Jalen, in 2002. He teaches Excel workshops and consults clients who have small businesses with no IT team.
or sophisticated data management systems. Let's welcome from Portland, Oregon, Mr. Oz du Soleil. Hey there, John. All right, this is good. This is good. Glad to be here. Cheers, buddy. Welcome. That's a big intro, yeah? Yes. Good stuff. All right. I've told our listeners a little bit about your background. Now, tell us more about yourself personally and how you got to become an Excel expert. I just moved to Portland, Oregon back last August. I was in Chicago off and on from 91 to, yeah, summer 2014. Got my bachelor's in philosophy. And that's interesting because when I look back,
I took more decision theory and logic courses than I took learning about Descartes and Plato and all. I hated that part. I loved the logic. And that served me well in working with Excel. So let's see. So yeah, I got my BA in philosophy, studied two years of Russian language, no kids, never been married, no interest. Lucky man. You're a lucky man. Wow.
Yeah, I'm free. I saw in Wyoming when I was driving here. Yeah. But where did Excel come from? Excel started, I was working in a call center and I hated it. I hated that this data would show up on my chair. It seemed like they would wait for me to go to the bathroom or go to lunch, put this paper on my chair. You're not answering enough calls. Your calls are too long. You're taking too many naps. Yeah. I said, man, I can complain.
I can go plead and show them how I'm working so hard. I had to get data. That was my instinct. So for a week, I tracked everything I did. And then I saw at the end of the week, I saw certain categories where I could break down the calls that I took and calls that I had to make and people I had to go and see, reasons why. Then I made some half-assed charts that were all of the no-nos about making visuals. But what came out of that was I saw that the higher-ups listened. They paid attention. And had I been some complainer?
You know, those sheets on my chair would have just kept coming. So that's when I saw the power of working with data. And I saw the eventual joy in working with Excel because my supervisor saw that I had an analytical mind. I had the initiative because I was tracking all this data on my own. So I wound up getting moved into different roles. I wound up being a commissions analyst. And that is where I started getting my passion about data quality because I didn't know about a VLOOKUP.
I just started to know this data is messed up and I can see duplicates. How can I write some formula to help me find the duplicates? And it was all in service to helping these customers whose careers were derailed because of bad data. So I got a lot of skill working with that, learning about working with data dumps, learning where you can and can't trust canned reports. Then after a layoff in 08, I started freelancing. And that's when I really got broad view of how people use Excel. Man, a financial planner.
So he says, OK, so you come into his office and you've got a portfolio of 20 stocks. He's got to look those up one at a time to find their value. And then you say, OK, you bought Nike back in 2008, like January 2008. He's got to look all that stuff up one at a time. Then you say you got Ford and whatever. So he had me build him a tool in Excel.
where you could put your stock ticker symbols and approximate date when you bought them. And the tool I built will go retrieve all of those at the push of a button. Interesting. Yeah. So that was one example of somebody's use for Excel that went beyond straight business, taxes, inventory levels. And you saw the power within Excel that this tool can actually bring that data out and analyze it and make some sense. Yes, yes. But the big thing for him was...
How long would it have taken him to look up 20 stocks and their approximate date of purchase and the price that they closed on that date? One at a time. Five minutes or three minutes a stock? Something, but it certainly wasn't a push of one button once you load all of the stock ticker symbols and dates in. So he's looking at hours during a day that he could save. If he's going through that with more than one client a day, geez. But yeah, that's been some of the really exciting stuff that I've learned is doable with Excel and has helped me to get an idea of the wide range of need.
around data. Cool, cool. And it drives you, doesn't it? Because you see what you can do and you think, wow, if I can do it for this guy, I can do it with other information. And your mind just ticks. It gets on thinking and then you don't get to sleep and you're thinking, wow, I can do this or I can do that. And that's when the real power of Excel, that's when Excel eats you, doesn't it? It consumes you. Yes, yes. It's exciting. It's exciting. Yes, it is. And if you let your mind go, man, it is a beautiful thing.
You can do whatever you want. You can do so many different things. It's not just use VLOOKUP and conditional formatting. No, you can do a lot of different scenarios or permutations for that one result. And that's the coolest thing about Excel is, and if you know the different scenarios and different tools within Excel, man, you're never going to go to sleep, are you? If you love it. If you love it, yeah. And do you find that the first 90% are easy in getting the product out, and the last 10% you're always trying to make it a little bit better? You're always trying to improve, and you're like, that 10% may take you.
Four hours. The first 90% might take you one hour, yeah? And, of course, it depends on the project because I'm dealing with something now that has got a lot of different pieces to it. And the pieces work. Now it's got to be a seamless process. Yeah. And now all the details and testing the scenarios and you fix something and break something else. And this is where it gets nuts. It's like, you know, these five pieces are working individually.
Sometimes you've got to stand back and say, hey, let me come back tomorrow and finish it. Because you're just consumed. You get so consumed into it that you're just missing that little thing. And you're not seeing it. You're not seeing it. You've got to step back. You've got to step back. And that's an exciting thing. And I've blogged about this a couple of times. But sometimes you just have to go get a cigar and go in the backyard, be away from the computer and just be. That's right. Have a glass of wine or whatever. And yeah, recharge the brain. Good stuff. Now, you said about your blog. It's called datascopic.net. And I've been on it. And you've got some weird and wonderful blog posts. Tell us a little bit about the blog and what's on there.
Well, I'm curious what you would say is weird and wonderful. Well, the pictures for what? The headings. The headings, they've got double meanings and they're fantastic. I like them. And they're just, yeah, it's just deep and meaningful. It's just a different way of writing a blog post, which is awesome because these days everyone just writes blog posts explaining things. But yeah, you go into it in a different mindset. It started out as I just need a web presence. So I put up a web page and then I started doing typical Excel tutorials. Yeah. But then I realized that for you to stay on Google, you have to.
consistently update your website. Okay. I could not consistently write, here is VLOOKUP again. Here is some product again. And then I started thinking about what does, you know, working with clients and teaching. Then I start to hear their concerns and I see what they're doing. And I see this person doesn't need some product. This person needs to lay out their spreadsheet better. Constantly, I see that your problem is layout. Now sweep all this stuff together and think about input, storage, output.
Don't think October, November, December, our salesperson, Joe, salesperson, Sally, salesperson, Raymond. No, that is called storage. Now you input. A field that says salesperson, Raymond, October, put that stuff all in one place. That's what they needed. And then learning what it took to be a good analyst, not being scared when you get called in and somebody's questioning your numbers. That's where I got passionate was, okay, so you have all the skill, but then you get called into the controller's office and started, well, I thought, I thought, well, I don't know. I was like that when I first started out. I was like that. And they used to ask me, how can this number be right? I go, it's on the paper. It has to be right.
Without checking, I was that busy getting the information out that you don't have time to check whether there's an error or not or other issues that might arise. Yeah. And this is where I got excited about my blog. And what I wanted to focus on was Excel is a tool. And what are you trying to do? You're trying to pay commissions right. You're trying to apply for a grant for your nonprofit. That's what you're trying to do. And if you have to just add up sales and add them up this way and add them up that way because you don't know pivot table, then do that. But at least when you've got all of your data together.
Check that data quality. That's what's missing. And that's where I've gotten excited about blogging and saying Excel is a tool in service of what? Get clear on what what is and then check all of the data quality. Do you have everything you need? Do you need to go ask somebody questions? Do you need to do all of that? I decided to have more fun because I get excited about Excel and data. I'm remembering when I used to put that mask on for fencing.
you know, and grab my foil and then, you know, waiting for the judge to say, and then it's time. So that's when I think about like data cleansing and my blog post about capturing Cerberus because cleansing data, that is like Hercules being sent out to capture that three-headed dog that guards the entrance to hell. That's a great analogy. And on that, and I see you're passionate, man. I love it. Now, data analysis and data cleansing. Okay. What is it for those people who haven't heard that term before? Data cleansing.
OK, data cleansing. This is the dirty work. See, when data analysis, analysis can be a lot of things and data cleansing is a part of analysis. But typically analysis would be something like this product line isn't doing so well. You know, you've got all of your data and now you've got to decide whether you want to continue the product line, try something else, end it. What are the forecasts around it? But somebody had to clean the data in order to get you there.
See, and this is a concern I've got about all of this. This is like this juicy, sexy stuff around being able to predict the future. But who cleans the data? And how do you clean data? Right. And how do you? And so this is where I want to have new people start to think about, OK, I've got all of this data. Where did it come from? Did it come from a source where I can trust what's here? Or can I anticipate that there are duplicates that need to be cleaned out? Now we're talking about the data cleansing part. So now you've got data from three sources. You need to divide it up by countries.
And one source has country codes that need to be converted. Another source has country names spelled out. And then the other one has the French version of the country names. So Etatsunis needs to be turned into the United States or vice versa. That is the data cleansing that you got to bring all of this stuff together so that you can then see the quality of it once it's all together so that then you can get to the juicy part. So the cleansing is done within Excel or is it done before it gets to Excel in the ERP systems? It depends on the individual process. And you might be able to get data from source A that is clean and trustworthy. It's been deduped wherever it came from. But then there's this other just data dump that somebody's you and then.
It had to be cleaned up in Excel if that's the tool you're working in. All right. And a scenario with data cleansing, what would you use? What feature within Excel would you use to cleanse data? Okay. One thing would be the duplicate finder in conditional formatting. Sure. That would be one thing because you can get Excel to highlight duplicates for you. You see your data is all wrong and you see there's duplicates. Yeah. You can see that only the first 20 rows, but then you've got a thousand rows. So by using the conditional formatting to find duplicates, you can actually highlight those duplicates in a color.
Correct. And then after you can sort it by color and delete those duplicates. Correct. Yep. Yep. You can do that. And then I'll tell you one other thing in Power Query. There is something called Unpivot. Yeah. This is beautiful. This is where we talk about, say, data shaping, because say you have all of your clean data, but and I've got a video about about using Unpivot and it's around area codes and Canadian provinces. And supposing you need a province column.
But you have a column for each individual province. Now, you can move that around, write some formulas or whatever to get yourself nice, clean columns and rows. But Power Queries unpivot will do it instantly. And then when it unpivots it, it puts it into a tabular format, correct? So it puts it all in a unique column name. Yes, yes, yes. So instead of having whatever, Saskatchewan, Ontario, whatever. As a column name, yeah. Right, individual column names. You'll have one column that just says province.
And then the rules. That's right. And I see that every day, you know, working with Excel when I used to work there, that you get the data that comes in and it could be that regions. And also I see a lot of months and people try to do a pivot table with months. A column name, January is the first column name. The second one is February and March and so on. And that is not the way that you should have your data to analyze it in a pivot table. So by unpivoting, it gets those column names and it puts it into the rows.
And the column name is not called January. It's called month. Right. Or whatever you need. And just thinking, you know, getting people to think that way, getting new analysts to think about this is intuitive to set up a report like that. But what happens is you wind up with just one view. You wind up with the month view or the province view. But now what if you want the view that's instead of month, it's by shirt color?
Well, if you've got to dig it out and you've got all these month columns, you've got to write a lot of fragile formulas. Yeah, and Power Query will help you with that. But the issue that I see is with all these external data sources, ERP systems, when it comes in, it comes in that shitty view, doesn't it? And these are sophisticated systems, and companies pay a lot of money every year, and they should at least have the option to download the data in a tabular format. And they don't do that. And they don't do that. And that's why Excel comes in with Power Query, where he cleans all that data with a couple of clicks or a button. And, you know, you can go and spend time analyzing rather than manipulating the data to the way that you want it to look like. Yeah. And I feel like that that needs to be more of a conversation about this data cleansing and the tools that are available for data cleansing, because analysis gets so much attention. And here's one thing I'll share with you is I talk with a reporter because Chicago Public School Systems.
They're always talking about needing to close schools and scores are bad and just all kind of mayhem. And this journalist was interested in, well, let's see the data. And they're bound by law to provide it. but they provide it in the crappiest way. And so the true analysis can't start until you have somebody with the skill to merge, purge, cleanse, normalize. Audit. Yep, audit, all of that. And so now you have this journalist who she got what she asked for and it could have been deliberately turned over as a mess. And so this is so important that people learn about thinking about data quality and understanding how to cleanse this stuff. And especially when you got somebody that...
So, yeah, you can have the data, but I'm going to send it to you in a PDF. Well, there's tools out there that can convert PDF to Excel. There's different tools that you can do that now. But, you know, I don't trust the data that comes to me. When I used to work in different companies, there's a lot of data and I don't trust the quality of the data because you're talking about big companies, multi-billion dollar companies. Imagine how much of accuracy is lost.
in each company. So they're reporting profits of 150 million, but I'm sure that there's another 20 million or whatever that's just missing or wrong because of that data quality issue. Would you agree with that? Yeah, I agree with you. I wonder if you would say more about your perspective and what you've seen and what leads you to say that. Well, because companies are massive, big mammoths. They have 300,000 employees. They have hundreds of different businesses, different IT systems. People come and go. Some people don't really want to be there, but they're there because they're getting paid. And the IT people, are they doing the checks? I'm not.
confident that these guys know what they're doing. They're putting all the processes in there. Okay, the data's coming down, but who's checking that data against the source? Who's checking that? Who's doing some checks to see whether or not you're within the range of having good data? And I see it, man. It's just one company. Imagine there's millions of companies out there. I see it in one company. There's just billions, billions of dollars lost or gained through bad data dumping or cleansing or whatever you want to call it. And this, see, we're getting into the book that I'm writing now for contact data management.
Because that's where I talk more about that kind of stuff. Guerrilla data analysis is the more technical. Get in, get out. You want to know what a pivot table is. You don't want to know about tables or some product. OK, here it is. But full contact data management gets more into this. Things like data is never clean. Deciding when clean enough has been reached because you can fill with this stuff until your life goes away. But what do you gain after a certain point? I've had experiences where we sit down on a data cleansing.
project and realize it's getting dirtier, it's getting dirtied faster than we can clean it. Yeah. And also the users, the people that are trying to clean the data, these guys are not Excel experts. You know, they're thrown in the deep end in most situations. And man, I feel sorry for companies that don't spend money on teaching employees and making them better at Excel. Yeah. Wow. But you know, one thing that I'm getting is that a lot of people who use Excel,
Their title is something else. If you know SQL really well, that's your profession. You go to SQL conferences and everything else. But if you're a grant writer or a customer service rep or a project manager, you know, project manager is probably not thinking about I need to master Excel. They're thinking about being a better project manager. You know, how do you get the most out of people? How do you keep a project moving forward? How do you set benchmarks that be more like what they're doing? But then, yeah, who's thinking about data quality? Nobody until a customer calls in complaining about something.
But I'll tell you, going back to my early days, customers consistently complained about certain things. And I finally got mad. And I started going to say, what happened? This didn't get reported. This didn't get sent out. What's going on? And the people would look at me and say, I did what I was supposed to do. But I would go deeper and found out, yes, everybody did what they were supposed to do. The data's messed up. Wow. This person has four profiles in the system. And the address is tied to this one. And the stuff that was reported was sprinkled between the other three. And that's what happened. So now how can I dig in there?
And clean this up. And then you run into the issues of, OK, the report that was sent to this person that didn't see the four profiles. What is it going to take to clean this up? Well, we got an expensive consultant that you can't get the department head to pay for. So my next move was with processes that I owned. I said, don't send me that report anymore. Send me a data dump. I will clean the data dump and get this stuff right. But my title was customer service rep. No data dump analyst.
Right, exactly. So that was not my job. And I stayed on the edge of being in trouble because my real job was answering the phones or whatever. It was not solving problems that is seen as IT, but this is too small for IT. So that's where I feel like cowboy, guerrilla data analyst, because you got to fight.
You know, you got to fight for the right to have some clean data. Yeah. Yeah. All right. I can talk with your man for the next hour. So this is interesting stuff. I like it. And we'll talk about your book shortly, but tell us what's the weirdest thing you've seen done or you've done yourself using Excel. I can go off onto some kind of tangent about what is, what is defined by weird, but let me offer Mindy Tracy, her template for designing her son's birthday cake. Aha. Yes. You can download that. You can see her blog post on that. And then there's Sylvia Juhasz.
adding sound effects in excel if numbers are low you get i heard about it but i haven't seen it and i'm going to get on to that and i'm going to get on to that i'm going to put that on the show notes as well i heard a lot about it then i want to add what i did because you know i'm a musician i play bass and i need visual okay and i analyzed the song by primus and one of the things i did was les claypool in press man his bass line sits kind of odd in the song because His main note starts on the and of four instead of one. And I was able to use Excel to show the 16th note grid.
of the music and highlight where his baseline sits in that 16th note grid. Wow. Wow. And do you have that? Because I, well, I'm not a technical musician. I love music and I kind of get an idea, but I need to see this. Where do you have, where do you have this? That is on my YouTube channel. We'll put the link on the show notes, man. Awesome stuff. You can do, look, when you're bored, you can do lots of stuff with Excel, correct? Oh man, you know something. I'll tell you, I just did a cussing calculator. I don't know if you saw that video. No, which one I, cussing? Cussing. We're saying bad words. Okay, cursing.
Yeah. Cursing, man. Cursing. Speak English. Yes. It's a calculator where I said my kids have been cussing. That's the title of the video where I say, OK, I heard my kid. She said F, S, S, B, S. And there's points. So F has more points against it than saying D or G. OK. And so the kids are adding up points. Right. But then there is a multiplier for context.
So if it was necessary or understandable, it doesn't get as high of a multiplier as just being foul-mouthed. Wow. That's weird stuff, man. That's cool. And do you have that yet? You're going to share it with us? Oh, yeah. Fantastic. Cool stuff. All right, Oz, you've got a book. It's called Guerrilla Data Analysis. And it was first written in 2002 by Bill Jelen. And you've added some more information there. You've added your spin into data analysis and data cleansing. And I've read it. And it's a quick read. It's a good read. And tell us.
Why did you decide to create this book? Well, I was communicating with Bill and he asked me to do it. He said that it had been written in 2002. It was the first book he'd ever written. And he said it sorely needed to be updated. And we were talking back and forth. And I said, yeah, I can update that. And I loved it when I got it because it was that get in, get out. A lot of the people that I'm surrounded with as clients and as students, they aren't trying to master Excel. They want to get in and get out.
There are a lot of things that hadn't changed since 2002, but I updated some of the examples because in 2002, it was still important to say something like about the Lotus 1-2-3 version or mainframe data. So I took it and also expanded beyond just business and did talk about nonprofits and retail and regular home uses of Excel in the examples. So that's a lot of what went into it. And then I felt it was important to add things like. Dynamic development, you know, because, again, that is where things fall down is you have all of your formulas and all of the data everywhere. But then still new data means being put in three different places. And if you forget those three are put in the wrong place, then your data is a mess again. So I felt it important to add that in and add a part for spreadsheet layout. So I went a little beyond just straight from functions and features.
Awesome. Now, I read the book this week and for me, it's for a beginner to intermediate person, isn't it? Someone that's thrown in the deep end, that's used Excel for maybe a year or two, maybe five years, but doesn't know the full potential of Excel. And now they've got this job and they've got to analyze data and they're like, wow, how do I learn the best features within Excel? And this book here talks about those features within Excel that you need to learn to become a good data analyst. And I read a quote here, which you got here. There are a lot of people who are in data-driven roles, but don't have a data background. They aren't sure what Excel can really do, but spreadsheets keep showing up in the ring boxes. That's just...
sums up this book, doesn't it? In that phrase. Yeah, yeah. And there's a lot of scenarios, isn't it? You get a promotion, as you said before, you haven't used Excel before, you use it a little bit and then you get this promotion and you've got to become a data analyst, don't you? Even though you're not called data analyst, you're called a marketing guru. Right, yes, yes, yes, exactly. Exactly, wow. Yeah, you sum it up very well. I appreciate that. And that is the person I kept having in mind as I went forward with the writing. You know, I have a friend who got his master's degree in marketing and the data is already clean by the time they get it to the classroom.
He can pick it up once it's clean. But then there's also the problem of the university has all these fancy, expensive analysis tools available to them. And then you go out and get a job at your 15-person company. They don't have these things. So now my friend is doubly hurt. He doesn't know Excel. He knows the fancy stuff that his 15-person firm can't afford. And he doesn't know about data cleansing. So it's for people like him. Another quote here, which I like, good analysts own the mistakes and fight to get better. Bad analysts end up as examples of what to do to get better. Because you got to dig.
And this is where it's beyond knowing the functions and features, getting up off your ass and asking questions. Why didn't this happen? Why did this happen? These numbers don't match up. What's going on? You know, and there was one time, you know, my numbers aren't working. So then I contact somebody who generated the report. He says, OK, that version of the report takes into account revenue shares. This version of it does not. And so depending on what you're trying to do.
You got to get the right report. And it was an instance where I had the wrong one. It had a revenue share taken out. And so when I use that and then, of course, my numbers don't add up. Telling people to slow down. OK, they want something now. And I have to say, look, I give you something now is going to be full of all these duplicates and weird stuff and questions that I can't answer. And I feel like a good analyst when they have questions that they can't answer. They can say, I don't know, but I am aware that there is a question here and I'm looking into it. They gave me crap data. What am I supposed to do with that? No.
Oh, no, no, no, no. That's somebody collecting a paycheck. That's not a good analyst. We've been there. And, you know, I started off like that. And then I thought, okay, I've got to really, really become good. Because if not, you're just going to get fired. So, you know, I want to be better than the next person. And I thought, how can I become better than the next person? I knew that Excel will help me out. And I learned it inside out. And all the reports that I was doing, I made sure that I checked them and they were perfect. And that's it. You've got to have a bit of a conscientious mind, don't you, when you're doing your work? If not, you're going to get eaten alive. You know, because we're still human beings.
You know, and we do make mistakes. And a friend explained to me, he said, if I try to hide it, if I try to shift the blame, that's when an analyst gets in trouble. Making a mistake, you don't get in trouble. It's embarrassing. If you can own up to it, if you can have the courage and say, hey, I made a mistake, you learn from it. And I think that's what every analyst should aspire to. Okay, you're going to make mistakes. I have, you have. If you don't get fired for that mistake, well, learn from it. If you do get fired, well, you're going to learn from it, aren't you? Now, consistent mistakes. That's an idiot. Yeah, that's a separate thing. That's George Bush.
Yeah, I agree with you there. I agree. All right, let's go on to a couple of topics about this book, which I liked. And I'll just talk about them, yeah? Just, I'm not gonna go into detail because we'll be here for, you know, into the early hours of the morning. All right, now, using conditional formatting to make deadline alerts and finding duplicates. I like that. Linking between workbooks and worksheets and what to watch out for. And that's important for a data analyst. Using the advanced filter. That's a cool trick. Using consolidate.
That's awesome. And also using subtotal, which I never used before. And because it was on the corner of my eye, I could see it there, but no one's taught me. I never had to use it. I always use pivot tables to get subtotals. We can actually use the subtotal feature to get some cool subtotals in your data set. Also matching lists of data with the match function. So you're matching two lists and using the match function. I use it all the time. And that's an awesome function to use. I also like the frequency distribution function. I've never used that. And that's awesome. And finally, the last part of the book, you got some down and dirty tips and insights into Excel. Useful Excel functions.
troubleshooting in Excel, and also spreadsheet layout and development, which is what you added there, and some keyboard shortcuts, which every analyst should have. So that, you know, the last part of the book is awesome because it just analyzes all these different things that a data analyst should have. It's just, you know, from five, six different Excel features that you need to know. Yeah, those last parts really were, I thought, were important to add because it's, again, seeing people know the functions and sometimes having an idea about a function and not a context for it. You say, this is obviously a VLOOKUP. Well, not for them. They've heard of VLOOKUP. Somebody might have done a VLOOKUP for them once, but now how can we paint a context around it so that they can begin to smell a VLOOKUP scenario, a pivot table scenario, thinking forward enough to where they are thinking about how they're going to lay out this spreadsheet.
you know, and not just try to go straight to an answer that they need now, but then next week they want a different answer and they don't know how to get it out. Okay. Awesome stuff, man. All right. Now, how can our listeners get a hold of this awesome book? That is available. You can click on my website on my homepage, datascopic.net. You can click there and it's also available at amazon.com. Awesome. All right, guys, you can find the links to this book by visiting the show notes on myxcelonline.com slash podcast. All right, let's take a short break and we'll be back with Oz's favorite Excel tips.
Can you analyze this spreadsheet using Excel pivot tables? Sure, if you can show me how to. Introducing the Xtreme 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.
Okay, we're back with Oz's Excel favorites. Okay, Oz, what's your favorite keyboard shortcuts and what does it do? I'm going to tell you, I've written about this. I don't use many shortcuts. I use Control-C, Control-V, just a few. And, you know, I'm a mouse guy, you know? And the thing for me is when I would use a lot of other softwares, like my music editing software, that's got keyboard shortcuts. And I don't want to mess around and, you know, this one keyboard shortcut for...
Changing your time signature is the same thing that is the shortcut for bolding in Excel. And also, I am slow and deliberate. I use a lot of helper columns. Most of my projects, they involve things I've never done before. So I have to think about how in the hell am I going to get this done? I've not done much with keyboard shortcuts.
I've stopped apologizing for that. No, you don't have to apologize, man, because I use about five and, you know, or every day. And then, as you said, to the shortcut, to timestamp the date, you know, how many times are you going to use that? And to bold, I use the mouse. So you don't really have to know everything off by heart, but if you can use between two to five, it's better than using nothing. And Control-C, Control-V, Control-Z.
They're up there. They're up there. They're 80% of what you're doing. Yeah, I see some people who are blazing fast. The financial modelers, the model of, yeah, those guys. Yeah, yeah. They're fast. That competition is timed. So, yeah, so they've got to be fast. My friend George Gilmer in Chicago, fast. Al Chen of Key Cuts, he teaches a keyboard course at universities in New York City. But, you know, for somebody like me that is real.
guerrilla use of Excel. I got to sit down in the corner of a storefront where the glass is broken and there's dust everywhere because the apocalypse happened two weeks ago. I got to sit and think, you know, and then I'm going to go out very methodically. If you're analyzing data, it's not about pace, is it? It's not about pace. It's not a race. You've got to sit back, think about it, and then bang, and act upon it, yeah?
Yeah, that's my approach. And we all have our own styles. And you have the people who go straight to VBA when there's some function that exists and they have their reasons for it, whatever. We're about trying to get some stuff done with the tools we have. Awesome. Good stuff. All right. Next question. What's your favorite Excel tip? For somebody who is learning, I say play. Play with it. You learn about a new function or a new feature. Play with it. See if it belongs in your arsenal or how it can fit in your arsenal. Build little dumb things. You know, this custom calculator.
It was just some little dumb thing that got me more familiar with array formulas, you know, thinking about some use that probably nobody will ever use. But this again comes from, you know, studying music where when you learn, when you find out about a new scale or a new melody, you want to try out a new technique, make a dumb little song. It's not going to make you millions, but it will be a reason if you're trying to learn how to slap or tap. Are you trying to learn something, you know, in a Locrian mode? OK, just make a dumb song, have some fun. And that's what I say about.
get in your mind around Excel Play with stuff. Play with it. Don't be scared of it. That's a great answer. I love that. You're the man. You're the man. I love it. Awesome. Now, what's one function that you always use when you're doing all these data analysis? One function. Your favorite function. My favorite function, I'm trying not to say VLOOKUP. Don't say VLOOKUP. That's it? Yeah. That's it? That's it? Yeah, VLOOKUP. I understand all the concerns around it being memory intensive. I get that. But when you've got to compare some lists, when you've got to pull data from somewhere else and bring it in and get things matched up.
I had a client contact me. They had sent two people over two days off of their regular job to compare and compile a master list from some other lists. They weren't even a quarter through the project, two people over two days. I turned it around in 45 minutes because I had VLOOKUP available to me. You could analyze the situation, couldn't you? You could say, okay, in this scenario, VLOOKUP works and I can make it work. Yes, yes. And to the point about me being memory intensive, it's more of an issue of we got to compile this list and then you copy, paste its values, VLOOKUP's gone. The client has their master list that they wanted. Cool stuff, cool stuff. All right, Excel add-ins. They're useful. They're everywhere. I'll use them. What's one that you use constantly, one that helps you out on your day-to-day activities? Right now?
depending on what version of Excel, is Power Query. It's officially an add-in. In the 2016 preview of Excel, Power Query is in the data part of the ribbon now. But that's got to be the add-in that I use the most. When I saw Power Query and what could be done, man, I just used a Power Query solution.
an hour of Ken helping me get that set up, saved a month of VBA coding. That's what it's useful, isn't it? It's getting rid of all that VBA code and using Power Query to clean up your data at the personal burden over and over again and over again. Yes, yes. And being able to have those queries saved and pushing a refresh button, you know, and having everything update. Man, Power Query is just the greatest thing. And it hurts my heart when I find out I'm dealing with somebody that's got Excel 2007. Yeah.
Okay, now we got to write all this VBA code and manage scenarios and what if that and what if this and Lord. I think that's going to be the end of the VBA gurus, yeah? No, because there are still things that need to be done via VBA. You know, I build a lot of forms. And one way to have input, storage, output is if you can have a form to input data and VBA that can unprotect everything, move the data from the form to the next empty place in the storage sheet, protect everything back, clear the form, have the user ready to input the next item. So that's done via VBA, but a lot of cleanup.
Yeah, the Power Query, huge help. Cool, cool. All right, now you've got a cool blog. I like it. Now, what's another blog out there that you always refer to whenever you're in trouble or one that's helped you along the way over the years? Definitely Chandu's blog. Yeah, right on, buddy. I'll learn from him. I'll learn from Chandu. Yep, and Debra's blog, Contextures. Yeah, Contextures, Debra's great. Yes, very helpful. Those are just a couple out of many. Yep, and then Mike Girvin's YouTube channel.
Oh, man. He doesn't stop. He's a machine, isn't he? Yes, he is. Yes, he's a machine. He's a good guy. They're nice voice. Yeah, yeah, yeah. And I like that he does give us stories around Excel and not just, you know, tips. You know, one thing is I've started.
channel where i'm calling it excel underground because a friend asked me about is excel for the common man and that really struck me in a lot of ways is like all these people feeling left out they're not talked to when somebody is showing you how to make a vehicle look up look left okay there are ways to do that but then A common person isn't thinking that way and they don't have a context. And so I started thinking more about non-business, fun ways of using Excel. And I had that friend be part of a video I did about my kid's birthday party. And it was a fun one where the story was I had a birthday party for my son and his friends came and wrecked the place. Now I want to build the parents for it.
But it's the thought process of the bill comes out to $77 per child for all the damage. But some people had two or three children there. So now how can I get Excel to help me generate bills so that this person had one kid, they get billed $77. This person had three, they get billed 77 times three. Pivot table stuff, easy, but it's just got to be set up right. And that's what's happening in Excel underground is more story and more fun and more common man stuff. And that's going to be a YouTube channel, correct?
Yeah, there's already a few videos there. The Birthday Party is one. The Custom Calculator is another one. Awesome. I can't wait for it, buddy. I want to subscribe. Get my hands dirty. Good stuff. All right. Now, final question. What's one Excel book that you love? Apart from your book, which is awesome and next book you're writing. What's one book that you can recommend to our listeners? Okay.
There's many books there. Yes, there are many. There are many. Man, okay, I am going to say Tables by Zach Barisi and Kevin Jones. Tables. Cool answer. I love that book. As he said, only 1% of the Excel users use tables. The rest don't know it, and it's easy, and I use it all the time. Even if I have three rows of data, I use it just to make it look pretty because of the design, the style. Then you've got structured referencing, and then you've got dynamic formulas. You've got everything. You've got the rows that sum up the whole column. Man. Yes. When I found out about tables.
My whole life changed the way that I developed. And then I felt ashamed of some dashboards that I had made because they got so many of these formulas in it because I didn't know what this table icon was. Yeah, tables help you keep everything tied together. And a good data analyst has to use Excel tables. Has to, has to. You know, just from, even if you aren't using the automatic formula fill, even if you aren't using that, just if you have a data set that's driving a pivot table, you can add new data and just refresh the pivot table instead of going in and changing the reference or remaking the pivot table.
Exactly. There's an infinite number of users, man. More people should use it. And the book is out. The name of this book is Excel Tables, A Complete Guide, Lists and Tables by Zach Barisi and Kevin Jones. Yes, yes, yes, yes. A must-have for any data analyst. Oh, man. My sister, she said she doesn't do much hardcore analysis, but she might have to make drop-down lists. And as soon as she's made the list, somebody comes and says, we got to add these two or three more. Well, if the drop-down is being fed by something in a table, just add them.
Correct, correct, man. And I use that all the time. And it's the first trick I learned when I started learning Excel was data validation. Yes. And now with tables, that can become dynamic because you put a name formula as your table. So all you've got to do is just extend the table, add in another name, and it gets automatically updated onto your table. And I'll do a lot of work for my wife for her data analysis. Now, she's a quality assurance auditor. All day, she's using tables.
She's using tables every day, and I'm always helping her updating the list and data cleansing. But she's not a data analyst, but she's using it every day. She is a quality assurance auditor. She goes in and orders pharmaceutical companies, but is using Excel tables every day. Yep, and that, you know, and if we were to rewind and go back to my favorite tip is throw your shit in the table. You get some data, throw that shit in the table. That's it. Even if it's three rows long, man, or one column wide, man.
Definitely use it. All right, good stuff. Fantastic. All right, so you can find the links to this book and everything we've been chatting about today, including tutorials on Oz's favorite shortcuts and tips by visiting myxonline.com slash podcast. All right, good stuff, Oz. We are right at the end of the show, buddy. This has been fun. This has been cool. All right, I've got a weird Excel scenario for you. All right, let's say this is my first time ever seeing Excel. So I'm a beginner and I need to learn the most important features of Excel within a week because I've got an important job interview. Now, with all your knowledge that you know,
And all the resources online or offline will be the quickest way for me to start learning Excel. This is a tricky question and it comes to me a lot. My first and easier answer would be guerrilla data analysis because it's like the buffet table for Excel. But here's what's hard about that question. You don't know what's going to be there when you show up at that job. That's the problem. That's when somebody said, I'm trying to change careers. I need to learn Excel. Okay, well.
Say my first five years of Excel was data cleansing. I had no use for a pie chart, bar graph. I was cleaning data. And then you got other people who don't know a doggone thing about cleaning data, but they're using the standard deviation and all of that stuff. So that's what makes it hard. And that's where when I do a workshop, particularly a private workshop, what are your people dealing with? Then I'll find out like at one nonprofit.
We need to get ourselves together with layout. They thought they wanted to learn about all the newest and greatest, and I was prepared to do that. But then when I get their data, they need layout. So that's the hard part about that question because Excel is so big. And another thing.
When I showed up as a commissions analyst, I inherited this spreadsheet that had all these crazy formulas in it. Now I'm doing forensics on somebody else's formulas. So that's what makes it hard with a vague question like I need to learn Excel. Yeah, but to start, I think you have to read your book, Guerrilla Data Analysis, because that's a buffet, as you said, and they need a buffet and they need a conference. And one thing is, it's having that confidence. When someone asks you in that interview, do you know how to do a drop down menu?
And if you can say, yes, this is how I do it. And this is how I do it. Do you know how to insert a pivot table and refresh it? If you can have an answer for that, if you're confident enough, if you know a little bit about every different feature within Excel, you will get that job. And your book explains those features that each new or intermediate Excel user that's going for that job interview needs to learn and needs to know. I'd agree with you there. And that was part of the joy of writing it was, you know, providing that buffet table.
Yeah. Within that longer answer, that is still lurking in the background. All right. And that's the scary part. Good stuff. All right. Now, the second part of that question is what's the best Excel feature that they should focus on? But I think we touched upon that and we said Excel tables. Do you have anything else there? I would say pivot tables and understanding how.
Your data needs to be set up to use a pivot table. That's a big thing. So putting your data into a tabular format, as we said before, don't have columns that are called January, February, March. Have one column called month and then all the months in the subsequent rows. Yep, exactly. Just so it would be as two part. Yep. Learning pivot tables and what it takes to get your data into a pivot table. Good stuff. All right, buddy. Now, final question.
I heard that you've got a new book coming out or you're writing a new book. Tell the listeners, take us into your studio and your notes. All right. What are you writing about? It's called Full Contact Data Management. And this is really all odds because guerrilla data analysis, that was a revision of Bill's book. But full contact data analysis, I am bringing new analysts into the world of what it takes to be really good.
And it also provides a perspective for Excel as a tool. So I get into things like. You've got to have some backbone. People are going to question the hell out of your data. They have something at stake. Their branch might close because of your data. And you can't take it personally. You can't hide from it. You can't try to do some trickery. But as a good analyst, you cannot only just take data, run it through some stuff, show them some pretty bar graphs, and then get upset and say, are you sure? Are you sure that's right? I don't know. Well, did you consider what happened in this last week? Yeah, I considered all that. But you can't take that personally. You have to dig and probe and pick and cajole as part of being a good analyst with Excel as a tool.
So the book does focus more on data cleansing and data quality. I'm trying to get people to think when you get data, what's the quality? Throw it in a pivot table. See if there's weird stuff. If it's supposed to have countries, but then you see the word bananas in a country field. But you're not going to see it by scrolling your eye up the raw data. You get this raw data, you put it in a pivot table, you can see bananas. Where the hell did bananas come from? OK, now you've got a data problem, data quality problem. This is the kind of thing that I want new analysts to start thinking about. And I think full contact because I wanted to bring.
That passion of when I used to wrestle, when I used to fence, you know, how I think when I deal with a new client's challenge is I feel like when I put that fencing mask on or when I hear my weight class called over to PA and it's time for me to go step to the edge of the mat. There's that confidence that I am prepared. I haven't seen this opponent before and somebody's going to win and somebody's going to lose. So that's where I'm coming from with the full contact data management, because this isn't a world of standalone tips like learning. Here is how you parry. Well, you know, your opponent isn't going to say, show me how you parry. They're going to be doing some shit to you that you got to react to.
And that's full contact data management. OK, I got the data. I've done a VLOOKUP. Now people are questioning it. I'm getting embarrassed. Then they find out that my VLOOKUP was wrong. OK, you got to move and you got to stick and you got to plan and adjust. And that's full contact data management is that full world of being an analyst and what it takes to be good at it. And there are technical aspects of how to get Excel to cooperate with you as being your six shooter in the Wild West or it's that horse.
or it's the hat that blocks the sun off of you. That's what Excel is in full contact data management. You have some fantastic analogies, man. I love it. This is going to be an awesome book, man. And what kind of chapters, what kind of a few topics or features that you're going to touch upon? One thing is characteristics of being a good analyst. And number one is give a damn.
Give a damn. Yes, because when the data is wrong, you have to give a damn to get up off your ass and go find out where did this come from? You don't do that if you're somebody just collecting a check. I will talk about the power of working with data dumps because there is a section where I talk about there's reality out there somewhere and then you want data. Well, it's got to be collected and you have definitions and you have calculations. And by the time you have this report, there are summaries.
that have their strengths and weaknesses. A new analyst has to understand that. And when you find out that there is problems in the report, if you can work with a data dump, you got power, true power, because you are no longer beholden to somebody. fixing the report and then sending you back the report. You can say, God damn it, can I just have a data dump? Just give it to me, all of it. I'll clean it out. Yeah, because it's a constant thing. You know, when we talk about the tension between the business and IT, it takes time to rewrite reports. And then a lot of times people don't know in a report. It's just a thing that they've ran and distributed for the past three years. And then something happens that says, this does not look right. Who knows? Now you got to find a person that wrote the report three years ago, maybe.
But that's the world of being a good analyst that gives a damn, that gets up off the ass, that asks questions, gets to the bottom of things. It's about getting stuff done. And there's so much that can get done with native Excel. And, you know, one other reason for me adding a very simple VBA example in full contact data analysis was when I was working and learning Excel and I kept hearing about this VBA, but nobody around me knew VBA. I knew some people who could record a macro.
But that's all they could do. They couldn't go in and figure out how to edit that. You know, when it says scroll 14, you know, they didn't know about taking that stuff out. But then when I look at VBA books, there'd be these big, thick things with examples that were like, here's how you put a button in. Well, no. In my book, I say we're going to build a form and we're going to put a button in. And behind that button is a submit button. But we also want to guard against submitting incomplete data. So let's add some checks to see if all of our fields are complete.
And if so, then the submit will submit. And I'm hoping that it would help somebody who had the questions I've had of what is VBA? Can it help me? Where the hell is it? Because now you're down the path of the developer tab is not open when you first open Excel. So that's all I wanted to do in full contact data management, that simple introduction. But really, it's all of that stuff that can cause you to fail no matter how good you are with Excel. You know, people start questioning your numbers and then you say, oh, forget them. Forget them. Yeah, my numbers are right. But who are they to be asking me questions about my numbers? Well, no, that don't work.
Mate, you learn something new every day with Excel. You learn something new every day with Excel. And I think your book is going to have some pretty cool tips in there for people to learn. And I know that I'm going to learn something new because like I have from this book here, you always pick up something new every day. And that's the wonderful thing. You don't get bored with Excel. The minute you get bored, you know what happens? You get divorced, yeah?
That's right. That's right. Not a good place to be. No, that's why Microsoft is bringing all these new power query and power maps to make people stay in love with Excel. And we are going to be in love. All right. Oz, what's the best way for our listeners to connect with you, my friend? Yes, at my website, datascopic.net. That's the best way. Also, you can contact me through comments in my YouTube channel that you can find at my website as well. Yeah, and Gmail.
oz1.depot at gmail.com. So those are the best ways. And also, where can they go to see you play the bass? I haven't played in a while. I'm getting back to that. Oh, man. Getting back to it. You're going to get back to the music. Yes, I do. I got to Portland last August.
And I'm getting to a place to where I can start to carve out some time away from work and see about getting in another band. Awesome. Awesome. Oz, thank you very much, mate. It's been a pleasure speaking with you. And man, I know that a lot of data analysts are going to take lots of value out of this interview. I know I have, man. And I appreciate it. And good luck with your book. I know you're going to have lots of success with it. And I want to have you back on the show in the future.
That's for sure. Thanks. This has been great. Yeah. And I would love to come back on with this. This has really been great. Now I hope that it does make a difference for some people. Hopefully too, mate. Cheers. Appreciate it. Right. 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.