This is how I ACTUALLY analyze data using Excel

it's been 6 years now since the first time I analyzed a bunch of data at work using Microsoft Excel and today I want to show you my Excel data analysis workflow that covers 99% of the types of analysis I do using the tool this is not going to be a video about building beautiful but nevertheless useless and random dashboards this is going to be a video about how I solve business problems using Excel how I analyze data and get insights and next steps using Excel for those of you who are new here my name is moan and I work as a data and analytics analyst in the financial services industry Excel is my go-to tool for any quick ad hoc analysis up to say a million rows anytime when I want quick answers to some business critical problems I use Excel let me show you how by walking you through a Hands-On practical example where I'll identify the top suppliers and Brands across very ious categories in my fictitious gym equipment sales data set look at year-on-year growth to see how sales have changed over time analyze market shares and even calculate year-to-date and moving annual total profit figures I think that's enough of an overview for now let's dive into the analysis all right cool so this is going to be a pure data analysis exercise this is like I said not going to be one of those YouTube videos where I'm going to create a bunch of the shiny stuff that is completely useless and random I'm going to show you what I use Excel for which is quick ad hoc data analysis to get the answers to whatever business critical problems that I have and then I just move on so this is our data set it's a fictitious uh sales oh no it's a fictitious profit data set sorry it's a fictitious gym equipment profit data set and a super quick tip for you if you get a data set and the columns are all over the place you can click in the top left and then double click and and then you can see that all of the columns are beautifully adjusted obviously this works for rows as well so let me just mess up my data set just a little bit you can see that I'll mess this column up as well and this one and this one click on the top left double click and then you can double click again and you have a neat and tidy data set so first things first let's understand the columns we have only six again it's a simple fictitious data set so I try to filter out all of the noise and we'll just focus on these col col s category suppliers Brands year month and the monthly profit so that's the data that we have before we even jump in the one thing I like to do is you can see that this is what's called a range and I like using tables because it's just so much easier to use tables when you have a bunch of pivot tables and pivot charts and then everything automatically updates I won't go into too much detail about you know this in this video but uh I guess yeah if you want to learn more about Excel or SQL actually or Tableau or python then course careers have a super data analytics course they've been a well yeah they've been a partner of mine for quite a while now so yeah they're good check out their course if you want to but anyway um yeah so let's turn this into a table and the easiest way to turn this into a table is to press control and then T I'm a man of shortcuts you hit okay and there you go what's the big difference now if you go to the top here you can see table design which you didn't have before let me just undo this you see at the top now you don't have table design so contrl T hit okay and then this is a bit too much for me this design so I'm going to tone it down just a little bit I think this looks about right so the first thing I really want to know is how many suppliers do I have how many brands do I have for each supplier and how many categories of gym equipment uh well profit data do I have here so a really simple way to do it is to insert a pivot table and start doing some analysis so I'm going to do that I'll show you two ways to do it I'll show you the shortcut way which is just going to be alt and VT hit enter and then that's a pivot table it took me probably 2 seconds to do it but I'll uh delete this sheet for now and I'll show you the point and click way as well so you go to the top insert pivot table from table hit okay there you go you have your pivot table and then let's see how many suppliers do we have three three suppliers okay fantastic easy and if you're not sure about the count or the sum or any kind of summary figures that Excel would show you you can always go to the bottom right right here and then you'll see the numbers all right so we have three suppliers how many brands do we have we have nine Brands and what I like to do is go to Design Within design change the report layout to show in tabular form remove the totals and then it's just an easier way to look at all of the brands and then I'll drag in the categories as well so how many categories do I have I have three categories airbikes rowing machines and treadmills super simple all right so now I answered some super basic questions so we have three suppliers nine Brands and then we have three categories just click into the filter and you'll know next thing I want to know is the year- on-year growth for each of these brands in terms of profits so what I will do is I'll go to my worksheet on the bottom here hold down control and then duplicate the the worksheet yeah so this is good because you're duplicating the the uh pivot table as well which is going to come in really handy when you're starting to use uh slicers and timeline filters across all of your pivot tables and pivot charts in a dynamic dashboard so yeah it's going to be great for that I'm not going to go into much detail in this video on that kind of stuff if you you know really want to learn more you can get get some certifications and you can do some courses online uh yeah they'll teach you a lot about it yeah and I guess on on that note you could you know say for example go to data Camp they have a bunch of good courses not just on Excel but you know a bunch of other data stuff as well I learned a bunch of Tableau skills on data Camp because they have a like a skills track a certifications track they have case studies they have various tutorials so yeah they're pretty good and I recently started partnering with them as well because yeah they're genuinely good but anyway um yeah second question year on-year growth how am I going to solve that problem so not that difficult let me remove the category for now drop in the monthly profit and then let me grab the year and move it onto the columns so now these numbers here these are the total profit numbers but what I'm looking for is the year-on-year difference so basically I want to see what's the difference between 2019 and 201 18 2020 and 2019 so on and so forth so I go to values value field settings show values as and then all I have to do is Select percent difference from and then the base field is obviously year and then the base item is previous yep and then I hit okay and there you go it looks yeah pretty good I think there you go some quick calculations 2024 is straight away I can see off the chart charts now why is that we can go into the data and we can have a look quickly so let me go 2024 select a couple here there you go 2024 is off the charts because we only have 5 months worth of data up to and including May that's why the numbers are off the charts because you're comparing five months of profits to 12 months of profits in 2023 so let me just remove 2024 because it's not a full year and then the numbers look much better I think all right so these numbers obviously it's it's you know quite hard to spot which one's good which one's bad what I like to do is this is honestly so simple go to conditional formatting and then choose some color scales so whatever you want you can choose your own rules I don't care too much cuz I'm really just after some numbers so I'll go with the let me go with this one I think blue is good red is bad yeah there we go so the darker the blue the larger the increase and the darker the red the larger the decrease so that's some quick insights for you right there biggest growth 6.91% right here biggest decrease what was it 7.7 right here yeah that's it quick insights all right so I answered year- on-year growth now the next thing I probably want to look into is market share so let me again duplicate my sheet hold down control drag and drop the sheet and then I have a new sheet now so all I'm going to do differently this time is I'm going to move the year onto the rows I'll move probably suppliers onto the columns let me move that away and then oh actually yeah I want to move the brand probably onto the columns yeah that makes a lot more sense and this time let's change the calculation so go to Value field settings show values as and it's not a difference from all I want to see really is the row total the percent of row total hit okay and now I want to add back 2024 because we're comparing like like and what I want to do now is remove the conditional formatting so you go to conditional formatting manage rules and then you just delete the rule hit apply hit okay done so now What's Happening Here is that each of these rows should add up to 100 and if you go to the bottom right here you can see that the sum is indeed 100 you can check this one as well is indeed 100 or you can just do a sum function so it's going to be sum B5 to what is it J five there we go and then you can drag it down and there you go everything's 100% fantastic okay so the market share cannot be more than 100% right because that's that's all of the profits in the market so that's the entire market so all I'm going to do here is insert a line chart I'll pick this one and then it looks obviously not great right now because we didn't actually select in which category do we want to look at the market share so so I go to pivot table analyze and then insert slicer and I want the category hit okay and now we have a pretty decent slicer all right so airbikes cool let me look at that there we go make it a bit bigger and yeah there you go I have three different categories you can click through and you can see how the market shares change over time if you don't like the layout of this chart you can quickly change you know the layout into something else let me pick something that looks well not ugly so yeah maybe this one I think this one looks all right and then you can change the colors as well so people like dark stuff oh that is very glowy um maybe this one yeah all right okay this is not too bad so I'll go with this one and let me just interpret the charts for you really quickly so let's pick airbikes for example what can I see straight away so the thing I can see straight away is that Apex Athletics they started increasing their market share and their profits uh you know they yeah it's it's at 17. 97% fantastic and then what's that Spartan Sports they're the worst as of 2024 rowing machines I can see that is this Elevate Fitness yes Elevate Fitness improved over time which is fantastic and then the purple one Titan training they yeah they definitely didn't improve over time they're actually at the bottom of the list now and if we're looking at treadmills it's going to be Spartan Sports who have uh I wouldn't say dominated the market share but obviously you know they increased their market share over time and then Hercules gear did the exact opposite simple analysis that's it let's move on so the next thing we're going to do is I did tell you that we were going to do some calculations so we're going to do some year-to-date calculations and we're going to do some moving annual total profit calculations all right super quick break and don't worry this is not a sponsored message this is a message from me Mo if you gained any value out of this video or any one of my videos so far then can I ask you three simple favors please one subscribe to the channel it helps everything I do more than you know two check out the ultimate data portfolio why because it gets you closer to your next data job if you don't believe me just check out what others have said and three if you're truly committed to getting your next data job and making more money then check out my onet toone mentorship program so year to date essentially is let me pick an example say if we're in um 2018 may I want all of the profit to be summed up from January to May that's it and then if we're in August then I want all of these profits so the 8 months worth of profits to be summed up but if we're in 2019 July then I want obviously the first s months of profits to be summed up so let me call this uh new column profit year to date because well it is the profit year to date and it's going to be super simple so nothing to worry about a bunch of sum ifs so first of all it's asking for the sum range so I'm just going to use my keyboard to navigate around if you're on a Windows it's just the arrow keys and the control key and then if you're on a Mac it's going to be the arrow keys and the command key super simple so okay so the sum range for me and I'm going to hold down control right now and press uh space and then I select the entire column fantastic okay the first criteria range is going to be the category and then the category obviously has to be equal to the to the category of the current row so yeah treadmill in this case and then criteria range two supplier again it has to be the supplier that occupies that row of data so in this case Peak Performance gear criteria range three it's going to have to be the brand that's in the current row and then year it has to be the current year so again in this case 2018 and then month and this is where it's going to get a little bit tricky not too tricky but because we want the year-to-date um profits to be summed up we want all of the months up to and including the current month so it's just a simple math calculation less than or equal to m%c sign and then you pass in the current month and then if this doesn't work now then I'm going to look like a fool but it did work fantastic okay so let me just do some formatting because we're talking about uh profit figures here currency and you can see that it's uh British pound here because I live in the United Kingdom so yeah it's not US Dollars this time I won't change it to dollars because yeah whatever it can be pounds this time for a change so let's do some sense check so for the first year uh these profits right let's just do some sense checks so let's pick 2018 May and then the 65 476 this number should be equal to the sum of these 5 months and you can go to the bottom and check 65 476 yes it is fantastic all right we can move on and obviously we can do the first 12 months there you go profit year to date it's going to be 152 585 and on the bottom here you can see that it's 152 585 indeed so the profit year-to date numbers are legit fantastic let's just pick another one just in case this one right here let's add up the profits for the first three months right here so this 41 036 41036 okay I did a sense check everything works cool next thing I'm going to do is we're going to calculate the profit um what should I call it total moving annual total yeah I think that sounds good because essentially what we're going to do now is let me explain really simply we're always going to look at a 12-month period so I want the profit figures summed up for the last 12 months no matter where I am so if I pick uh 2019 May for example I want the profits for this year so this is the year-to-date profit and on top of that I would also like seven months from the year before so all of these months so seven months from the year before 5 months from the current year so that's 7 plus 5 12 months and that would be the profit for the last 12 months but if I pick another month for example 2020 uh October so I have 10 months in this year I want all of these profits and then I would like two more months from the last year so to calculate the profit moving annual total we need to first of all um well yeah pick the profit year to date and on top of that we just add on whatever months we want to make it a full 12-month period from the previous year so this is going to be profit year to date plus again it's going to be Su ifs I bet it's boring by now but the sum range is going to be the profit month and then the category again it has to be equal to the category of the current row and then second criteria supplier has to be equal to the supplier in the current row and then we have brand has to be obviously equal to the brand of the current row and then we have year and it gets a little bit tricky here because I want obviously the year but I want the previous year so take away one because you already included the profit year to date so you already have the profits from the current year what you're trying to do is onto the current Year's profit you want to add on last year's profit however many months you need to make it a full 12 month period so that's why the minus one to get to the previous year and then in terms of months again simple math calculation so the month has to be greater than whatever current month you're in in this year so greater than and then m per sign and then the current month and then hit enter and again if it's not going to work I'm going to look like a fool but it did work so good oh by the way the only thing I would like to add as well if you want to learn this type of thinking how to actually solve problems using data and how to like you know actually approach job applications and and how to do stuff in the real world then I would honestly I would I would urge you to go onto my websites and look around there and check out the resources because yeah you know like all the stuff I created really the ultimate data portfolio the ultimate data road map and like the six other bonuses that come with it the job Seeker Academy the ultimate project Builder they they focus on implementation they focus on Hands-On stuff they focus on practical stuff they focus on relevant stuff so I've I've done so much learning and sometimes you know you're in this forever cycle of not implementing things so make sure you actually Implement things like this for example okay let's compare the numbers for the first 12 months they should be the same because there was no previous year makes sense all of these numbers are the same now if we look at row 14 my profit moving annual total is 152 423 so this should be the profit in 20 19 uh January plus the 11 months before so if I select these 12 months I can see if I go to the bottom right that I have 152 423 okay great let's pick something randomly I'll pick row 86 so I'm in August which means this profit right here this 9555 2 this should be the year-to date profit um plus the the four months from before so 1 2 3 4 5 6 7 8 and then oh sorry yeah actually I picked the wrong one uh because obviously I'm in 2018 here there's no prior year apologies so let me pick another one 2019 August for example it just goes to show you know with data analysis trial and error and I'm pretty certain I'm going to leave this piece in as well I'm not going to cut it out just to show you that you know not everything has to be perfect first time clearly I didn't pay attention here and I should have lesson learned so 2019 August um the moving annual total profit is 152 337 so this number should be these 8 months in the current year so this is the year to date and then the four months from the previous year so 1 2 3 4 and what's the number 152 337 on the bottom right here fantastic The Profit moving annual total numbers are correct as well and just to do some I guess you know pivot table data analysis manipulation with it let me go back to this sheet right here and let me duplicate it so holding down control dragging and dropping the sheet I'll remove this chart because I don't need it I'll remove the slicer as well click into the table and this time let's do something different so first of all go to pivot table analyze and then you go refresh refresh all and then now all of a sudden you see that our two new calculations the two new columns they appeared right here okay so I'm going to remove the monthly profit figures because I don't need them and then the next thing I'm going to do put year on the filters I'll put month on the filters I'll put brand on the rows and then I'll put suppliers on the rows as well and now the only thing I really want to see is the moving annual total profit for a specific month obviously these figures now are large very very large because my year and my month I'm selecting all which I shouldn't I just want the moving annual total profit for the last month of data that I have so I'll pick 2024 for 2024 I think May was the last month probably yeah let me just go back and see 2024 and yes may is the last month okay so I'm back here let me just uh format this oh yeah this is another thing you can do you don't always have to go to values right here on the right and then go to Value field settings you can just double click and it gets you to the same place so I'll do some quick formatting number format number yeah thousand separator okay it's just easier to look at the number and there there you go this is the this is the moving annual total profit for these brands for these suppliers which means that over the last 12 months up to an including 2024 May iron strength equipment Co for the forge Fitness brand sold well made this much profit and you can obviously add in the category as well if you want to and then you'll see a different kind of breakdown and that if you select everything then you would see the total across all of the brands all of the categories and all of the suppliers so yeah I think pretty simple stuff that's about it all right that's all from me for now if you enjoyed this video then please subscribe to the channel as there's definitely a lot more value ad content like this one coming and you should probably also check out these videos right here thank you so much for taking just a little time out of your day to watch this and I shall see you in the next one a [Music]

Channel: Mo Chen

Views: 29,842

Keywords:

Id: 7W95pylDH8M

Length: 24min 5sec (1445 seconds)

Published: Wed Jun 26 2024