Learn Power Pivot in Excel (Better Than Pivot Tables)

0:00in this video we're going to learn power

0:02pivot Essentials in 12 minutes power

0:05pivot is a super powerful data analysis

0:07tool where you can combine multiple

0:10Excel files into one create

0:12relationships between different data

0:14tables and work with much larger data

0:17sets than what Excel can handle you can

0:20even make pivot tables from not just one

0:23table which is excel's limit by using

0:26multiple tables as well so if you need

0:28to analyze data power pivot could be a

0:31game changer for

0:36you before we get into Power pivot let's

0:39take a look at the data that we're

0:40working with which you can find over

0:42here in Excel we have three tabs one the

0:45salary brackets for all of these

0:47employee levels then the transactions so

0:50you can see here that it seems like this

0:52company sells cars and finally the

0:54employees and their names and you can

0:57see that we have some columns in common

1:00for example the level here is the same

1:02as the level in the salary brackets

1:04right over here and the same thing goes

1:06with the staff ID over here is the same

1:09one as this one that we have right here

1:12we'll keep this in mind as we'll use

1:13this later and you can download the same

1:16Excel file in the video description

1:18let's get started by installing power

1:20pivot and to do so we want to head over

1:22to the file Tab and all the way down

1:25towards options you'll find the addings

1:29under this popup that just came up and

1:31under inactive applications you'll find

1:34the power pivot over here so what we

1:36want to do is switch to the

1:38coom coom addin down here and hit on go

1:43now we'll be able to select on power

1:45pivot that's the one that we want to add

1:48and just hit on okay there you'll notice

1:50that we have it here as a new tab but in

1:53our case let's go ahead and close out of

1:55this one and create a new Excel file

1:58which is where we'll do the anal

1:59analysis in this new Excel file we'll go

2:02to Power pivot and click on manage this

2:05will open up the power pivot for Excel

2:08and right now we don't have any data so

2:10let's go ahead and import it over here

2:13under get external data from other

2:16sources we want to scroll all the way

2:18down to the Excel file which is what we

2:21want to import hit on next there and the

2:24file path is wherever you have it

2:26located in my case I just have it under

2:29my desktop so it's right here for me hit

2:32on open obviously in your case it might

2:34be somewhere else and we want to take on

2:37use first row as column headers as

2:40that's how we had it in Excel hit on

2:42next there and you'll see that it now

2:44shows the three different tabs that we

2:46have and we want to import all three so

2:48we'll take on this top part and hit on

2:51finish once this loads up we can just go

2:54out to close and we should be able to

2:56Now find it over here as you can see we

2:59have the three different tabs uploaded

3:01in this case we did it for three

3:03different tabs within one Excel file but

3:05you can also do this with multiple Excel

3:08files and putting them together now that

3:10we have the data let's establish some

3:12relationships and for this we'll go over

3:15to diagram view over to the right side

3:18here and you can see we have the three

3:20different tables and we want to

3:21essentially link them or connect them

3:23you could say using some kind of

3:26relationship so in this case you'll

3:28notice that we have stuff ID over here

3:30and over here as well so we can just

3:33link that by dragging and dropping once

3:36we do that you'll notice that it's

3:37established a connection which is a one

3:40to many connection as there's only one

3:43staff ID but that one staff ID might

3:46make more than one transaction so that

3:48makes sense same thing with the employee

3:51level over here we can go ahead and drag

3:53that to the level right here once it

3:56loads up again it's one too many so in

3:59level one there could be more than one

4:01employee now that we've established

4:03these relationships it's no longer just

4:05three Separate Tables but rather one

4:08larger data model with some

4:10relationships before we get started

4:12analyzing data it's important that we're

4:14all on the same page when it comes to

4:16data analysis and a great way to do that

4:19is with hotspots free introduction to

4:22data analytics report they're sponsoring

4:25this video and kindly providing this

4:2750-page PDF completely for free using

4:30the link in the description below in the

4:33download you can find a comprehensive

4:35breakdown of what data analysis is what

4:38types of data analysis there are and

4:41some best practices as well it's not

4:43just a report full of text it also has

4:46some supporting visuals to make sure you

4:49understand this resource is great if

4:51you're a beginner or if you've taken a

4:53fair share of Statistics classes like

4:56myself I personally find it most useful

4:59to ref refresh my memory on some of the

5:01key statistics terms and techniques so

5:04if you want to check this out head over

5:06to the link in the description below to

5:08download this completely free guide and

5:10level up your data analytics skills all

5:13right back to the data set let's now

5:16turn back to the data view so you can

5:18see all of the different things that we

5:19can do so right now we have the sale

5:22price and the sale cost so we could add

5:24another column here just by doing equals

5:28the sale price

5:30minus the sale cost which would simply

5:33be our profit we can just hit enter

5:35there and it's going to calculate let's

5:37go ahead double click up there and

5:39rename it to profit now that's fairy

5:42simple you could have probably done that

5:43in Excel as well but one thing that's

5:46fairly unique here is that we can link

5:48between the different tables now that we

5:50have relationship so for example instead

5:54of having this over here which is simply

5:56the staff ID what if we want to know

5:58their name well we could use the equals

6:01related function you'll find it right

6:04here hit the Tab Key there and so we can

6:07link it to Let's suppose their name so

6:09the employee name which is what we want

6:12I'm going to double click on that close

6:14the parenthesis and hit enter now

6:17because we've established that staff ID

6:20as a relationship between the employee

6:23and the transactions tab you'll notice

6:25that it's able to find the names of all

6:27of the people at this point you you

6:29might wonder why don't we just add all

6:31of these three tables into one this way

6:33it makes it a lot simpler the reason we

6:36don't do that is that it's somewhat

6:37inefficient the reason is that our table

6:40would become so much larger if we had to

6:42have all of this data and all of the

6:45salary data in the transactions tab it

6:48would get repeated several times every

6:50time there's the same name we would have

6:52the same salary breakdown and so forth

6:54which would make it quite big and

6:56inefficient one more thing worth

6:58learning are measures and unlike

7:00calculations over here they're not going

7:02to make a whole separate column this

7:04case we can just use them down over here

7:08we're going to go over a fairly simple

7:09one but just know that they can get a

7:12lot more advanced so down over here with

7:14the selected just going to equals let's

7:17suppose that we want to just sum The

7:19Profit so we would use the sum formula

7:22you can see that up over here hit the

7:24Tab Key and we want to sum all our

7:26profits which we can find right here

7:29it's the new column that we've made

7:31double click on that close the

7:33parenthesis and hit enter so you can see

7:36down below that a measure is going to

7:38show up we need to stretch it out there

7:40and you can see what that looks like we

7:42can change the name from measure one to

7:44something like the total

7:47profit and hit enter let's make sure to

7:51remember this measure that we've made as

7:53we'll use it later for the kpis which

7:55are the key performance indicators for

7:58the time being let's suppose that we're

8:00ready to start analyzing this so we can

8:02just click on pivot table up over here

8:05we're happy for this to be let's say in

8:07an existing worksheet and hit on okay so

8:10here we have the pivot table and it

8:12might look like a normal pivot table but

8:14the key difference here is that we now

8:16have three different tables that we can

8:18work with while typically on Excel we

8:20would only have one let's suppose that

8:22we want to find out how much each

8:24employee is bringing in Revenue so for

8:27the employee let's go ahead and take

8:29their names you can find them right

8:31there and we now want to take their uh

8:35Revenue as well which we can get from

8:37the transactions tab so even though we

8:39have different tabs they're actually

8:41going to be linking it's just going to

8:43be the sum of the sale price that's the

8:45revenue that we've got you can see here

8:48that Sarah and Mike seem to be

8:49performing quite well we can take this a

8:52step further and try to see what level

8:54of employee they are so under salary

8:57brackets we could go ahead and take the

8:59employee level and put it under columns

9:02what's interesting here is that while

9:04Sara is still a level one employee so

9:07she's fairly Junior if we check by years

9:09of experience in instead of employee

9:12level you'll notice that she only has

9:14zero years of experience and yet she's

9:17able to bring so much in Revenue maybe

9:19we should consider giving her a

9:21promotion as she has good potential

9:24finally let's go over a key performance

9:26indicator you might remember up over

9:28here that we had the kpis right next to

9:31the measures actually which we looked at

9:33earlier so we'll click on kpis new kpi

9:38and over here you can see that we have

9:40the base field in our case we only had

9:42one measure so we're going to have

9:44profit there and we can either have a

9:46something relative or an absolute for

9:49example let's suppose that we want them

9:51to have a minimum of say 20,000 in

9:54profit hit enter there and you'll see

9:57how these scales also change we we can

9:59move them around if that's what we want

10:01let's suppose we go for this type of

10:03Icon style and hit on okay now let's

10:06clean this up a bit more so we can see

10:08it a bit better so I'm going to remove

10:09the years of experience and the values

10:12so I'm just going to have the name and

10:14the profit as well so let me put the

10:16profit under values here you'll notice

10:19though that we're not really seeing the

10:21kpi that's because we need to go inside

10:24of this drop down and you'll find the

10:26total profit over here that's the one we

10:28want and let's go ahead and tick on the

10:30relevant areas so I'm just ticking on

10:32all here but we really just want the

10:35status so we can get rid of these other

10:37ones now we can see that some is clearly

10:40failing so we might need to speak to him

10:42and Jones is not doing too well either

10:46much like regular pivot tables we can

10:48also add Charts or slicers but this time

10:51they're going to be linked between three

10:53different tables which makes them a lot

10:55more powerful we can do a slicer up over

10:58here as you can see we have all the

11:00different options same thing goes with a

11:02chart to learn how to do all of these in

11:05more detail check out this video over

11:07here to make an awesome dashboard or

11:10take our Excel course over here hit the

11:13like And subscribe and let me know in

11:15the comments if you want a part two