Learn Power Pivot in Excel (Better Than Pivot Tables)

in this video we're going to learn power pivot Essentials in 12 minutes power pivot is a super powerful data analysis tool where you can combine multiple Excel files into one create relationships between different data tables and work with much larger data sets than what Excel can handle you can even make pivot tables from not just one table which is excel's limit by using multiple tables as well so if you need to analyze data power pivot could be a game changer for you before we get into Power pivot let's take a look at the data that we're working with which you can find over here in Excel we have three tabs one the salary brackets for all of these employee levels then the transactions so you can see here that it seems like this company sells cars and finally the employees and their names and you can see that we have some columns in common for example the level here is the same as the level in the salary brackets right over here and the same thing goes with the staff ID over here is the same one as this one that we have right here we'll keep this in mind as we'll use this later and you can download the same Excel file in the video description let's get started by installing power pivot and to do so we want to head over to the file Tab and all the way down towards options you'll find the addings under this popup that just came up and under inactive applications you'll find the power pivot over here so what we want to do is switch to the coom coom addin down here and hit on go now we'll be able to select on power pivot that's the one that we want to add and just hit on okay there you'll notice that we have it here as a new tab but in our case let's go ahead and close out of this one and create a new Excel file which is where we'll do the anal analysis in this new Excel file we'll go to Power pivot and click on manage this will open up the power pivot for Excel and right now we don't have any data so let's go ahead and import it over here under get external data from other sources we want to scroll all the way down to the Excel file which is what we want to import hit on next there and the file path is wherever you have it located in my case I just have it under my desktop so it's right here for me hit on open obviously in your case it might be somewhere else and we want to take on use first row as column headers as that's how we had it in Excel hit on next there and you'll see that it now shows the three different tabs that we have and we want to import all three so we'll take on this top part and hit on finish once this loads up we can just go out to close and we should be able to Now find it over here as you can see we have the three different tabs uploaded in this case we did it for three different tabs within one Excel file but you can also do this with multiple Excel files and putting them together now that we have the data let's establish some relationships and for this we'll go over to diagram view over to the right side here and you can see we have the three different tables and we want to essentially link them or connect them you could say using some kind of relationship so in this case you'll notice that we have stuff ID over here and over here as well so we can just link that by dragging and dropping once we do that you'll notice that it's established a connection which is a one to many connection as there's only one staff ID but that one staff ID might make more than one transaction so that makes sense same thing with the employee level over here we can go ahead and drag that to the level right here once it loads up again it's one too many so in level one there could be more than one employee now that we've established these relationships it's no longer just three Separate Tables but rather one larger data model with some relationships before we get started analyzing data it's important that we're all on the same page when it comes to data analysis and a great way to do that is with hotspots free introduction to data analytics report they're sponsoring this video and kindly providing this 50-page PDF completely for free using the link in the description below in the download you can find a comprehensive breakdown of what data analysis is what types of data analysis there are and some best practices as well it's not just a report full of text it also has some supporting visuals to make sure you understand this resource is great if you're a beginner or if you've taken a fair share of Statistics classes like myself I personally find it most useful to ref refresh my memory on some of the key statistics terms and techniques so if you want to check this out head over to the link in the description below to download this completely free guide and level up your data analytics skills all right back to the data set let's now turn back to the data view so you can see all of the different things that we can do so right now we have the sale price and the sale cost so we could add another column here just by doing equals the sale price minus the sale cost which would simply be our profit we can just hit enter there and it's going to calculate let's go ahead double click up there and rename it to profit now that's fairy simple you could have probably done that in Excel as well but one thing that's fairly unique here is that we can link between the different tables now that we have relationship so for example instead of having this over here which is simply the staff ID what if we want to know their name well we could use the equals related function you'll find it right here hit the Tab Key there and so we can link it to Let's suppose their name so the employee name which is what we want I'm going to double click on that close the parenthesis and hit enter now because we've established that staff ID as a relationship between the employee and the transactions tab you'll notice that it's able to find the names of all of the people at this point you you might wonder why don't we just add all of these three tables into one this way it makes it a lot simpler the reason we don't do that is that it's somewhat inefficient the reason is that our table would become so much larger if we had to have all of this data and all of the salary data in the transactions tab it would get repeated several times every time there's the same name we would have the same salary breakdown and so forth which would make it quite big and inefficient one more thing worth learning are measures and unlike calculations over here they're not going to make a whole separate column this case we can just use them down over here we're going to go over a fairly simple one but just know that they can get a lot more advanced so down over here with the selected just going to equals let's suppose that we want to just sum The Profit so we would use the sum formula you can see that up over here hit the Tab Key and we want to sum all our profits which we can find right here it's the new column that we've made double click on that close the parenthesis and hit enter so you can see down below that a measure is going to show up we need to stretch it out there and you can see what that looks like we can change the name from measure one to something like the total profit and hit enter let's make sure to remember this measure that we've made as we'll use it later for the kpis which are the key performance indicators for the time being let's suppose that we're ready to start analyzing this so we can just click on pivot table up over here we're happy for this to be let's say in an existing worksheet and hit on okay so here we have the pivot table and it might look like a normal pivot table but the key difference here is that we now have three different tables that we can work with while typically on Excel we would only have one let's suppose that we want to find out how much each employee is bringing in Revenue so for the employee let's go ahead and take their names you can find them right there and we now want to take their uh Revenue as well which we can get from the transactions tab so even though we have different tabs they're actually going to be linking it's just going to be the sum of the sale price that's the revenue that we've got you can see here that Sarah and Mike seem to be performing quite well we can take this a step further and try to see what level of employee they are so under salary brackets we could go ahead and take the employee level and put it under columns what's interesting here is that while Sara is still a level one employee so she's fairly Junior if we check by years of experience in instead of employee level you'll notice that she only has zero years of experience and yet she's able to bring so much in Revenue maybe we should consider giving her a promotion as she has good potential finally let's go over a key performance indicator you might remember up over here that we had the kpis right next to the measures actually which we looked at earlier so we'll click on kpis new kpi and over here you can see that we have the base field in our case we only had one measure so we're going to have profit there and we can either have a something relative or an absolute for example let's suppose that we want them to have a minimum of say 20,000 in profit hit enter there and you'll see how these scales also change we we can move them around if that's what we want let's suppose we go for this type of Icon style and hit on okay now let's clean this up a bit more so we can see it a bit better so I'm going to remove the years of experience and the values so I'm just going to have the name and the profit as well so let me put the profit under values here you'll notice though that we're not really seeing the kpi that's because we need to go inside of this drop down and you'll find the total profit over here that's the one we want and let's go ahead and tick on the relevant areas so I'm just ticking on all here but we really just want the status so we can get rid of these other ones now we can see that some is clearly failing so we might need to speak to him and Jones is not doing too well either much like regular pivot tables we can also add Charts or slicers but this time they're going to be linked between three different tables which makes them a lot more powerful we can do a slicer up over here as you can see we have all the different options same thing goes with a chart to learn how to do all of these in more detail check out this video over here to make an awesome dashboard or take our Excel course over here hit the like And subscribe and let me know in the comments if you want a part two

Channel: Kenji Explains

Views: 57,610

Keywords: power pivot, power pivot tutorial, power pivot in excel, power pivot essentials, learn power pivot, what is power pivot, master power pivot, take power pivot, power pivot lesson, power pivot essentials in 12 minutes, power pivot in 10 minutes, power pivot basics, power pivot mastery, power pivot for data analysis, power pivot pivot tables, power pivot relationships, power pivot measures, power pivot data kpi, power pivot slicer, power pivot chart, power pivot from scratch

Id: iLtc0-2PqyI

Length: 11min 18sec (678 seconds)

Published: Sun Jan 28 2024