How to use Power Pivot in Excel - Intermediate Microsoft Excel Tutorial
How to use Power Pivot in Excel - Intermediate Microsoft Excel Tutorial
In this Intermediate Microsoft Excel tutorial, I will show you how to use Power Pivot in Microsoft Excel. In this tutorial, we’ll cover the basics of Power Pivot, a powerful data analysis tool built into Microsoft Excel. We’ll start with an overview of Power Pivot and its benefits, then move on to practical steps for importing and organizing data. From there, we’ll cover how to create relationships between tables and use advanced calculations to manipulate and analyze your data. Finally, we’ll demonstrate how to create interactive PivotTables and PivotCharts that allow you to visualize your data in powerful new ways.
Whether you’re a data analyst, business owner, or just looking to improve your Excel skills, Power Pivot can help you streamline your data analysis and gain deeper insights into your information. With this beginner’s guide, you’ll have everything you need to get started with Power Pivot and take your data analysis to the next level.
Don’t forget to like and subscribe for more Excel tutorials, and let us know in the comments if there are any other topics you’d like us to cover!
Student and School practice: https://bit.ly/Student_School_Practic…
Tuition Fees practice: https://bit.ly/TuitionFees_Practice_File
0:00 Introduction to Power Pivot
1:43 How to install Power Pivot in Excel
2:51 Ways to bring data into Power Pivot
9:08 How to create relationships in Power Pivot (Diagram View)
14:46 How to create column calculation in Power Pivot
20:44 Different ways to add Measure in Power Pivot (more calculations)
28:02 Build a report with a Pivot Table in Power Pivot
32:20 Add a KPI (Key Performance Indicator) to your Pivot Table
34:49 Create a Pivot Chart with your data model
Intermediate Lesson 1 (Pivot Tables and Functions): • Intermediate Microsoft Excel Tutorial…
Intermediate Lesson 2 (Power Query): • How to use Power Query - Intermediate…
Content
0 -> Hii, I'm Jamie welcome to Teachers Tech
and it's great to have you here today
4.38 -> I want to show you how to use
powerpivot in Microsoft Excel
11.52 -> this is the third lesson in my intermediate
Microsoft Excel tutorials so if you want to learn
16.8 -> about power query in Microsoft Excel or maybe more
about pivot tables functions like vlookup take a
23.58 -> look at my lessons one and two and I'll link those
in the description and up above in the card and
29.22 -> I'll have more future intermediate tutorials so
if you want to stay in touch make sure you hit
33.84 -> that subscribe button to get notified when I put
those out but today is all about power pivot in
40.5 -> Microsoft Excel and powerpivot the main thing I
want to tell you about what I like about it first
45.54 -> before I get to it is that you can take multiple
tables for multiple sources and bring them into
51.9 -> a pivot table or pivot chart so if you've ever
wondered how you do that because usually if you
57.42 -> start go ahead and start a pivot table all you
can do is bring in one table but when you use
63.6 -> powerpivot you can create a data model by making
relationships between the different tables and
70.02 -> then they all show up in your pivot table or
pivot chart and then you can really dig deep
76.02 -> in analyzing the data and that's what makes power
pivots so great you have all these different ways
82.14 -> to bring in data from all these different sources
you can bring in millions of rows of data and then
88.68 -> you can perform calculations on those rows add
measures key performance indicators and today
94.74 -> on teachers Tech on powerpivot I'm going to go
through all these so you get started on how to
99.78 -> use powerpivot in Microsoft Excel so first of all
I want to show you how you can add power pivot to
107.46 -> your Microsoft Excel and this is free to do and
it's very quick because right now if you look
112.2 -> across my tabs there is no power pivot there it's
an add-in so what I'm going to do is go to file
119.4 -> and check Take a Look Down Below at options here
so I'm just going to click on options and then I
125.88 -> get the Excel options we have add-ins right here
and now powerpivot as you can see right here power
134.4 -> Microsoft powerpivot for Excel is a com add-in and
to go there all I have to do is drop down and go
141.84 -> to com add-ins and hit go at this point I just
need to check what I want and at the very bottom
148.98 -> here I have Microsoft powerpivot for Excel I'm
going to go ahead and just place the check mark
153.96 -> on that one and hit OK and as I do this take a
look at the tab my tabs up above when I hit OK and
161.64 -> you can see it was installed added and when I go
to powerpivot I have my new ribbon here that I'm
168.36 -> going to be walking through all these different
features for you today I do have some practice
173.58 -> files for you to follow along with me today and
if you take a look in the description you'll see a
178.92 -> couple different links and they are to these files
right here you're going to see student in school
183.9 -> and tuition fees so I just want to show you real
quickly what's in these I'm not going to dive into
189.42 -> them in depth but let's pretend we are a school
here we have student information all of this
195 -> is just fiction here just made up names we have
some courses all the different courses that our
201.24 -> school has here so there's a number of different
courses there's grades that students received
206.64 -> through different semesters in here so you can see
there's a GPA here on the side and we have all the
212.52 -> different semesters listed here that go back a few
different years and we also do have in our other
220.08 -> sheet this is a I'm just showing how many courses
they're enrolled in and how much it costs per
225.9 -> course so just one more Excel practice sheet there
so I just wanted to point those out I'm going to
231.6 -> go ahead and actually just minimize this one but
I am going to close this one right here so this is
239.1 -> the student in schools because when I bring the
data into powerpivot I can't have it open here
246.42 -> so I'm going to be bringing this in first so I'm
just going to close this down on this one I don't
251.7 -> think I made any changes but I'll go ahead and hit
save so now I'm just on a blank Microsoft Excel
259.08 -> worksheet here and I'm going to bring in in that
data so to do that I'm in powerpivot I've selected
267.72 -> the tab here and we need to build a data model
so what a data model is it allows me to bring in
274.8 -> all these tables and build relationships between
them so first of all I got to go get the data then
280.92 -> we'll build the relationships so I'm going to go
ahead and hit manage now at this point I get you
288.42 -> can see Power pivot for Excel open up and take
a look at all the different external data these
295.32 -> are the different ways I can bring data in so we
can bring it in from a database and you can see
300.36 -> the different servers available we can bring it in
from a service and we have other sources here too
307.56 -> so if you take a look connect to a data source we
have all these different ones you can connect to
312.84 -> in today's example we're just going to bring in
this Excel file right here we're going to bring
318.78 -> in from here and then we go find the PATH so I'm
going to go ahead hit hit next and now a friendly
324.9 -> connection name that's fine with me Excel I'm
going to leave it as that I need to browse to
330.18 -> where those files are so I'm going to hit browse
and I have them right here so I'm going to bring
335.52 -> in student and school remember it has to be
closed for you to bring it in and hit open and
342 -> does it have headers yes this does have headers
on it so first row as headers I'm gonna I'm not
347.76 -> going to bother testing this I know this will
work here and I'm not going to go to Advanced
351.78 -> but I'm going to hit next here now at this point
you see all the different tables that are in here
360.06 -> so what I'm going to do right now is just go
ahead and select the top one because I want to
364.86 -> bring them all in I could select individual ones
if I wanted to or what I could do is if you wanted
370.86 -> a preview so if I wanted to preview student
information I could select that one and go to
376.08 -> preview and filter and I could go do some filters
but I can preview to make sure I have the correct
382.2 -> data coming in I'm just going to hit cancel I'm
selecting all my source data and I'm going to
387.66 -> hit finish and you can see it goes through with
success we like the green check marks here and
393.18 -> it shows me all the information brought in
so I'm going to go ahead and hit close now
399.36 -> so if you take a look at the bottom or the tabs
are you can see all the tabs are there just the
405.6 -> same information that we brought in from Excel
now into Power pivot but let's say if that file
411.84 -> updated so you have a connection now to that Excel
file and if you were putting more information
417.42 -> maybe more students went into it what you would
need to do is go up to and refresh and then this
423.78 -> data would update in here as well okay now the
next thing what I want to do we're not quite
430.08 -> done we have to bring in one more spreadsheet so I
could go back and show you the way I did it before
435.36 -> with bringing in sources bringing that file but
what I want to do is first of all I want to close
440.4 -> out of here so I'm just going to go up and hit the
X and you notice it goes right back to the sheet
445.5 -> 1 that I was on with nothing on it but if I click
manage again here it just opens back up again and
451.14 -> then I'm back in with my power pivot so you can go
back and forth by just clicking that you do have
457.32 -> to save your file so if you're going to save you
could save you know this whole book here and then
463.5 -> that will everything will be remembered with your
power pivot as well okay so we have one more sheet
470.76 -> we want to bring in here so I'm just going to go
ahead and open this up so this one I want uh open
475.68 -> and I want to point out I could have went to um I
could have selected the table so I'm in this table
481.38 -> let's say and then I what I could do I could have
done to each one is just add data model from that
487.32 -> file and that would work what I'm going to do here
I'm going to show you that I can go ahead I'm just
492.18 -> going to go Ctrl C here so I'm copying with Ctrl
C and I'm going to go back to that other worksheet
499.02 -> and this is where I was working in powerpivot
I'm just going to paste that information control
503.88 -> V into here and I'll just go ahead and size it up
here so you can see and now if I go ahead and just
512.76 -> select this I'm going to go add to data model and
this also will add this to the data model so you
519.24 -> you can see now I have TBL tuition here and plus
all the other tabs so I could I know this is the
527.34 -> table here but I'm going to go ahead and just call
this tuition fees to label it like this and now
534.18 -> I have all the information the data that I want
and what we're practicing on today in here so I'm
541.32 -> going to go ahead and go to diagram View and start
setting up the relationships between these tables
548.82 -> so I just want to point out before I go and start
these relationships so if I'm on student info you
553.92 -> can see that there's like student ID here if I go
over to let's say grades I have student ID also
562.2 -> there well click on grade student ID here as well
so you can see there's connections between these
568.5 -> different tables if I go to courses we have a
class ID over here and if I go to let's say grades
575.94 -> we have a class ID over here so there's some
connections made you can see by just the labeling
581.28 -> but Excel doesn't know that powerpivot doesn't
know that by itself so we have to create those
586.56 -> connections between those tables I'm going to
go if you look we're just under the Home tab and
592.44 -> we're going to go all the way over and we're going
to go to diagram view here so I'm going to select
597.3 -> diagram view now there's five tables we only see
four at the very bottom I can change the display
603.96 -> here I'm going to drag this out so we could drag
this smaller so we can see things a little bit
607.86 -> better so now these are the five different tables
I have these right here you can see I can change
614.22 -> the size these this has four in here I think this
one should be fine you just click and drag so I
619.14 -> want to be able to show you all the different
columns in each of these tables here and I can
624 -> size them so I can see everything I just makes it
easier when you're making your relationships like
629.1 -> this and if I add more columns they'll show up in
here and I'll show you that in a moment when I do
633.66 -> some calculations after I have my relationships
so I'm going to move some things around just so
638.22 -> I can visualize what I want a little bit more
when I set up these relationships and I'm going
644.82 -> to be moving this grades right over to the middle
more and I'm going to move the students info over
651.36 -> here so I also like to kind of move mine around
to kind of help visualize this a little bit I'm
656.4 -> going to move this down as well so I'm going to
move my courses down here and I'm going to need
663.72 -> a little bit more room so I'm just I don't know
why I have to size that up I just like the way it
668.64 -> bucks so I'm going to give myself a little bit
more room in the display and I'm going to move
672.3 -> semesters down here as well okay so I'm going to
keep grades here and I'm going to keep tuition
678.96 -> fees over here so the reason I did this this is
the way I like to do it to help me visualize what
683.64 -> I'm setting up so I know each student here could
have many grades in each course many grades and
691.08 -> each semester has many grades and I can see that
there's a relationship if I look student ID here
697.08 -> I see student ID here class ID to class ID and
semester ID to semester ID names The Columns don't
703.62 -> have to be named the same I did them so I can
easily find them when I'm making the connections
708.78 -> so to make the connection so if I look at student
ID here and all I have to do is click and hold on
715.2 -> my mouse and I'm going to drag it to student ID
here and it's going to make a relationship between
721.8 -> these here so what I and if I take this wherever
I put this that relationship stays there I could
728.58 -> break the relationship with this line if I go
ahead and right click on it you can see I can
733.86 -> make it inactive I could edit it or I could
delete it as well so if I made the wrong you
739.08 -> know relationship between two different columns
I go ahead and delete it then create another one
743.1 -> or just edit from here okay so I want to point
out here too we have this star and we have the
749.82 -> number one over here and like I mentioned before
I said well one student could have many grades so
756.6 -> that's this stands for many and this is one and
that's what it means so one student could have
761.58 -> many grades if I go over to my class ID I'm going
to drag it over to class ID so each course could
770.22 -> have many great so you can see the one into here
and same thing I'll go to my semester ID and just
777.6 -> drag it over here to semester ID like I said I do
don't have to have them labeled the same but the
783.36 -> value has to be the same in between those two
different columns I couldn't have one integers
788.52 -> and one being letters or anything like that they'd
have to be the same on it now so I have those you
796.02 -> can see these are all the one and these are it's
many when it comes to grades here and when I do
801.24 -> the pivot table and pivot chart these are the
relationships that I'm going to be working on
806.34 -> when I display I do have this one more table here
and I'm just going to make one connection here and
812.34 -> you can see I have student ID to student ID and
this is just um this is I'm going to be showing
817.5 -> some calculations with the columns and I need this
relationship to connect and I'll show you when I
822.72 -> create the calculations having that relationship
here allows me to go over to this table
829.08 -> from here I can go to this table go grab something
and be working over here on this table here so uh
837 -> these are just some steps what I like to do I
like to kind of organize visually my my tables
842.22 -> and how I'm seeing things I don't have to do
that directly because those relationships when
847.44 -> I drag them around you see how they How They
still hang on I just like to visualize it this
853.14 -> way so we have our relationship set up now I
want to show you with some calculations first
859.62 -> that we can do with the columns to kind of really
show you how powerful this is with powerpivot to
865.68 -> add these calculations to this so I would need
to go back to data view here so I'm just going
872.52 -> to select data View and we're going to go over to
the tuition fees here and we're going to perform
879.78 -> our first calculation and this won't be through
a relation it'll just be on this table alone
887.52 -> so I'm going to go ahead and perform a calculation
on this one so I will need to use a formula what
894.24 -> I want to have happen is just very simple you can
see there's enrolled courses over here and there's
898.26 -> tuition per course I want to know how much this
is going to be in total so what I'm going to do
904.5 -> first of all let's go ahead and change our column
name I like to change that first before I do my
909.48 -> calculation so I'm just going to click up in here
and change this here so this is going to be called
914.82 -> total tuition uh here okay so I'm just going to go
ahead and then just click off of this and if you
921.6 -> look the whole column is selected now and where
I'm going to put my equation is right up here so
929.76 -> you can already see that the equal sign is already
to go here so I'm just going to click up here and
936.72 -> what is it that I want to perform so I need to go
5 times 3750 but I don't want to times those uh
945.24 -> from cell to cell it's this is going to be based
on the columns so all I actually have to start
949.92 -> doing is typing the column name so this is called
enrolled courses so if I start typing enrolled
955.74 -> courses you can see I get a couple different
options now the tuition fees is the name of the
961.2 -> table and the enrolled courses is the column so
if you see kind of these this double step here's
966.66 -> just the name of the column and this will work for
me here so I'm just going to go enrolled courses
970.44 -> and I'm going to multiply so I'm just going to
use the multiplication here multiply what well
975.72 -> it's tuition per course if I start typing tuition
per course it's this one I'm going to use you can
981.96 -> see there was the other one here but I'm going to
go tuition per course right here and I'm going to
988.32 -> hit enter and then watch what happens to the
column total tuition so I'm going to hit enter
994.02 -> and now I have total and I can format this
information too as well uh so you can see up
1001.4 -> in formatting we do have our dollar signs here
where we can add this uh to any of these so we
1006.98 -> have our formatting that you can do uh inside
this now what I want to show you now is I'm
1012.62 -> going to go back to my diagram view here just
select this and you can see everything's set up
1018.32 -> but take a look at tuition fees now I have a new
column called total tuition here so I just wanted
1026.48 -> to point that out as we add the columns uh you
these get updated here in the relationships and
1031.82 -> the views here so I'll go back to data view
now and what I want to do is perform another
1037.4 -> calculation though I don't want to perform it
on this one here I'm going to go to student info
1047.12 -> now you can look at the bottom I'm on student
info tab here and I'm just going to scroll over to
1053.24 -> the very end and you see that there is a payment
received column here so I know I just calculated
1060.38 -> total tuition uh but I have payment received
so what I want to know is the amount owing so
1066.92 -> I'd have to minus that total tuition minus the
payment received so I'm going to add a column
1072.68 -> at this point uh you notice you can't just go to
any column and start typing something in right
1079.1 -> so these are going to be made to perform those
calculations on it if we're adding more of the
1083.72 -> data that's where we can go from our original
Excel file and like I mentioned before we can
1088.52 -> refresh it and then everything we're creating in
here and the calculations will still work with the
1093.86 -> new data that comes in so again we're going to
create a new column it's going to be right here
1099.98 -> and it was going to be called amount owing so I'm
going to just type the amount owing right here and
1106.7 -> that that's good I'm just going to hit enter and
you're going to see the equal sign come up here
1112.04 -> so what is it that I want to calculate so I know
it's on the other table and so that's going to be
1120.2 -> the first number right the amount owing minus the
payment received so if I go ahead and start typing
1126.44 -> related so if I go Rel like this you get related
so this function allows me to find related tables
1135.08 -> and so I've made that relationship to the tuition
fees here so if I go to relate it it shows me here
1142.64 -> all the different columns from that one that
I have the relationship to so I what what do I
1150.02 -> want I want to know total tuition it's right here
so this is the column I just created so I'm going
1154.28 -> to go ahead and select this just like that I'm
going to add a bracket here to close this so I'm
1160.22 -> just adding the bracket to close this up so what's
the next one well this is a simple operation it's
1165.32 -> just subtraction here so what I'm might subtract
them well it's going to be payment received so
1171.2 -> if I start typing payment received it's right
here so I'm going to select it and go ahead
1177.56 -> and hit enter now and watch this get populated
here so now I was able to make this calculation
1185.18 -> on student info since I have a relationship
with tuition fees over here I was able to pull
1192.74 -> that column and use it in a calculation over
here so that goes I wanted to point out that's
1199.22 -> the importance of the relationship if I go to
diagram view between here so this allowed me to
1205.52 -> see here when I went from this one it allowed
me to see all of these different columns that
1212.06 -> I could perform the calculation in so that's
just a couple steps to the calculation I'm not
1217.16 -> diving deeper into any of the other calculations
but I want to show you what's possible once you
1221.9 -> had the operations but there's a different type
of calculations you can do it doesn't have to to
1227.48 -> be just with the columns you can use measures
as well so what's a measure well to get to the
1234.44 -> measures there's a couple different ways that we
can do it and we'll start from being inside power
1239.72 -> pivot but then we're going to move out and I'll
show you the other way that you can do it too
1244.82 -> so measures are more calculations and I'll give
you an example of one here let's do a really
1249.44 -> simple one I want to Total all of this tuition
here so if I go to let's say this spot if I put my
1257.06 -> equals so we're just like in Excel with a formula
and what do I want well I wanna well I want
1262.82 -> actually a total here so I know this is going to
be some if I start typing in sum I get my function
1268.94 -> I select function what is it that I want well that
was called total tuition on tuition fee so I can
1275.06 -> go to my tuition fees total tuition and I'm going
to select this one and I can go ahead and close
1282.86 -> this up hit return and I have a measure now you
can't see it very well I can stretch The Columns
1289.28 -> here so you can see it I can format it here also
but measure one I don't want this to be called
1296.78 -> measure one what is this this is tuition total
of all right so if I go up here into where the
1304.58 -> formula is and if I just highlight this and I'm
just going to say that this is tuition uh total
1312.14 -> and I'm going to put all just like that so just
so I know you can call it what you like but then
1317.6 -> if I go ahead and just click off of this it will
get updated here so that's how you would go and
1323.66 -> change the name you can see now it's I can add
a little bit more room that's a measure there
1328.88 -> so let's do another one here what I want to know
is the total amount of enrolled courses so I want
1335.06 -> it right here I'm going to just I'll put my equals
in and I'm going to do my sum here of all courses
1341.6 -> so some and this is enrolled courses here on the
total of here so here it is tuition our tuition
1349.04 -> fees enrolled courses I'm going to go ahead and
select this one and I'm going to just hit enter
1355.28 -> and you can see it just added the bracket at
the end too measure one well again I don't
1360.32 -> want this called measure one what do I want this
called let's just call this total courses total
1366.2 -> course amount just like that so we can change that
if we want but if I click off now I have a couple
1373.16 -> measures and I can stretch this out and so we have
two different measures there that we created okay
1379.34 -> I'm going to go back to my student info one here
and I'm going to show you how you can use these
1384.38 -> measures across different places because these are
created now and if I go to let's say well first of
1390.98 -> all I want to know how many students I have uh
if I would how would I do that I want to add up
1396.62 -> I could try to add up in different ways but I'll
show you a formula that you could use here so if
1400.94 -> I go in here and if I'm going to go to the equals
again and this time I'm going to start typing this
1408.8 -> distinct count right here so I know these numbers
are all unique here and so what it's going to do
1415.82 -> is Count through all the unique numbers because
each one has to be different for each student so
1420.56 -> I'm going to go to this one well this is on the
student info What's this called this is this is
1427.1 -> the student one right so I would go find that one
right there so let's go find student ID here and
1436.88 -> that should do the trick right here student ID hit
enter and I get a count of here well I can't see
1442.82 -> what's in there I'll just stretch this out again
so we can see 18 I don't want to call it measure
1449.18 -> one I want it to be called a student amount of
students so I'll just say amount of students just
1458.78 -> like that so amount of students now I can take
measures and use them with each other too so if
1465.68 -> I want to find the average cost let's say of what
the amount of each student is paying on average
1472.82 -> so I have a in tuition fees I did calculate uh the
what did I calculate the tuition total here total
1481.64 -> tuition all and I have the amount of students here
which is 18. so I need to divide those two to find
1488.24 -> the average amount of each student is paying so
if I go here and I'm going to do another formula
1493.64 -> here so if I put equals this time and it's total
tuition all so I start typing total and look here
1500.42 -> this is the one that I have I have tuition total
oh I have that other one but that's not the one
1505.64 -> I want but it has this new symbol for measure
I'm going to choose this one and it's just a
1510.32 -> division and what was the other one well it was
this one it was the amount so if I start typing
1515 -> amount it was the amount of students right here
so I'll select it and I'm just going to hit enter
1522.14 -> and I'll get a total here so you see another
measure I'm going to stretch this out so you can
1527.48 -> see uh we can change your name so I'll just put
average uh oops not that I'll just type average
1535.4 -> cost per student like so and I'll click off of it
and I can stretch this out so that measure if I
1545 -> ever needed it again and I can go and format it
also up here so that's some ways that you could
1552.08 -> use measures but another way that you could do
this is to go right from right from the ribbon in
1560.78 -> Excel so if I go and close out of this what I can
do is add a measure right from here so under the
1568.82 -> power pivot tab I'm going to go to new measure you
can see I can manage my measures too so all those
1574.34 -> different measures that I've created but I'm going
to go to new measure I can pick my table uh from
1580.22 -> here so if I was looking I can go all through
these ones so what is it that I want to find
1585.86 -> I could go to grades what do I want to call this
let's say I'm doing the average of the GPA so I'll
1591.68 -> just type average GPA I'm not going to bother
giving it a description here what is it that I
1600.14 -> want to to do here so I want to do an average
here so I'm going to start typing average of
1606.92 -> something so what is it and I know in that uh in
that one I have grades and I have uh it should be
1614.36 -> I should be the grade here so I have grades grade
I'm going to go ahead and select this one here
1621.62 -> and I'm just going to hit close the bracket
here so it's just going to be average this
1626.36 -> is just going to be a number and now it's going
to be two decimal places here that's fine I'm
1632.06 -> good with that decimal number all this is good I
could go and check the formula it seems everything
1638 -> is flying get the green check mark I'm going to
hit OK so where did this go it's not on this one
1643.52 -> well this wasn't even the table that I told it to
put on if I go back to manage now and I put that
1651.26 -> under grades so grades and I'll just stretch this
out here so we can see it average GPA 2.49 so it
1660.32 -> went through here and made the average of 2.49 so
now I have this measured called average GPA and I
1668.06 -> did that from just adding it that other way so
a couple different ways you can do calculations
1672.26 -> from The Columns to the measures try playing
with different ones getting used to those once
1677.48 -> you build them they can add a lot of power to your
power pivot so now we're at a point where I want
1684.14 -> to display this data and I want to use a pivot
table to do this and I'll also show you in a pivot
1689.72 -> chart because this is what it's all about it's all
about taking all that data so like I said at the
1694.58 -> beginning all those millions of rows going through
performing these calculations uh adding measures
1700.28 -> and then you're able to really display what you
want to dig through that data okay so we can add
1707.6 -> our pivot table in a couple different ways right
from power or pivot if we take a look up here we
1713.12 -> have pivot table here and I'm just going to do the
drop down you can see our different options right
1718.04 -> through here this tutorial I'm just clicking pivot
table and I'm just going to select it and where do
1724.28 -> I want it well I want this to a new worksheet so
I could go existing if I want but I want a new one
1729.02 -> and I hit OK and just like that if I take a look
over on the side now all of the different tables
1736.82 -> are in here so remember usually you can only
bring one table in but now that I built that
1742.82 -> data model built the relationships I'll be able
to pull information from different tables and it
1748.94 -> will show up in the in the in the report that I'm
building now I'm just going to go ahead and just
1755.06 -> delete this one really quickly here because I
do want to show you can insert it kind of the
1759.92 -> normal way if I go insert and then pivot table
well we don't want to go from table range we
1765.44 -> built a data model so we could go from this right
here and then we go new worksheet hit OK and it
1771.86 -> brings us to the same place so I just wanted to
show you a couple different ways that you could
1775.64 -> start your pivot table from and but you do need
to grab that that data model in order to do it
1781.52 -> so now on this step let's try picking some of the
fields to build a report so what I'm going to dig
1788.18 -> down and find out here is I want to find out how
the different courses are going throughout time
1794.84 -> to see if there's any fluctuations with different
semesters on this so what I'm going to do first is
1801.14 -> I want to see the courses is show up so my courses
I'm going to go to this table just open it up and
1807.26 -> I'm going to put a class name so I get class name
and it puts it into my pivot table here to begin
1813.2 -> with you can see those are all the classes we
offer now I also want let's say a grade next to
1820.82 -> this so if I go ahead now if I go grab a grade
and take a look notice that we created these
1826.82 -> measures and everything they're also in where I
could be grabbing them in but in this case I want
1832.22 -> to grade so I'm going to Select Grade now the
issue rate here is it just did a sum if I look
1838.46 -> over here it did a sum of these well I don't want
to sum I want an average of the grades for here
1844.34 -> so I can change this I'm going to just drop down
and I'm going to go to Value field settings and
1851.18 -> I want this to be an average but I also want to
change my number format to number and two decimal
1857.54 -> places like that so I hit okay hit okay and now
that's better so this makes more sense to me I
1862.94 -> have an average I get to kind of see of all the
courses but let's dig a little further now let's
1869.48 -> say well you know I want to know also by semester
so if I drop down I'm going to pick semester here
1877.82 -> so now it's showing me fall there's this was the
average I can see the different years of it uh
1884.66 -> audit and I could even go a step further and if I
was going to go and find let's say maybe a student
1891.98 -> information I want the last name here I could put
a student ID but I'm going to go last name now it
1897.68 -> even breaks it down so I can look at algebra by
default the different students in it what their
1904.28 -> GPA and so as I go down I could go through this
way and remember with pivot tables I can drop
1909.68 -> down and filter through it so if I wanted to see
a specific uh subject I could just go through
1915.5 -> here I'm also going to show you how to do some
slicers when we put the pivot chart in I'll show
1920 -> you how you can add some slicers to go through
this information as well so all these different
1925.52 -> tables that we have connected I'm able to build a
pivot table from selecting different ones so you
1933.08 -> can go ahead and try and see what you find through
here if you want to dig down and find different
1937.76 -> data and see what works for for you when you're
building a pivot table I want to add one more
1944.36 -> thing to this pivot table and that's going to
be a kpi a key performance indicator so how we
1950.66 -> do this is I'm going to go back to pivot table
here and you can see we have kpis right here and
1956.84 -> I haven't showed you how to use this yet but what
we're going to do is go to new kpi like so and we
1965.12 -> get this here so remember I made a measure and we
have to base this on a measure I made a measure
1971.36 -> average GPA so I'm going to use this I'm going
to select this one average GPA and it's going to
1978.02 -> be an absolute value here and it's going to be
out of four I'm going to put down so I have to
1983.24 -> change if I didn't change this then this would
all be skewed off so it's going to be four so
1988.46 -> just like that so what's going to be the green so
what's the indicator that would be best for for me
1994.58 -> for green so I'm going to say actually don't mind
that so when I clicked in there it said 3.2 1.6
2000.58 -> divided this up I could slide this if I wanted it
different on it I'm not too worried I just want to
2007.3 -> demo this how this could work and we'll just leave
it uh we'll leave it right there so if they're 3.2
2012.76 -> and above it'll be green the target you can see
how we could divide this up into if you needed
2018.34 -> more more areas to divide up you can do that and
you can change your colors so I'm going to go and
2025.54 -> just choose this one right here so and it will
look like these so I'm going to go ahead and hit
2031.6 -> ok now what I want to show is on the side notice
this uh so I have a new column that went in here
2039.34 -> right I have 0 1 minus one uh these don't show the
uh the the lights that I picked they're showing
2046.9 -> that one would be above average you can see it and
zero would be neutral and negative one would be uh
2052.3 -> the color or the be red it'd be below but if I go
and open this up and turn off status and then just
2058.96 -> turn on the goal you can see that comes up or this
you can see I have different ones so if I turn
2064.12 -> back on status just like that after I click it
on click it off and then I have these indicators
2070.12 -> right here so I just want to point out how you
can add these kpis to this so another quick visual
2076.42 -> because this is where you have so much data little
visuals like this can really help you kind of
2082.24 -> Target in and say oh what's up with this one why
is it that color so that's how you could use kpis
2089.68 -> so now if I want to add a pivot chart to this I
can be under the insert and you can see there's
2096.16 -> pivot chart right here I could also go back to
uh the other way when I showed you and I was in
2102.22 -> powerpivot when they had the insert pivot chart
from there as well but I'm going to go ahead and
2107.74 -> first of all before I do this I don't want to have
this in my Pivot chart at all so I'm going to just
2112.36 -> turn this off and the other thing is I'm not going
to have names in my so I just want to see a pivot
2118.6 -> chart this time and it's going to be just kind
of seeing the difference between each semester
2122.26 -> and I want to be able to see it on on each class
so right now I have a filter on and I can change
2128.56 -> the filter here as well so if I didn't want it to
be algebra and I wanted to select all you can also
2135.1 -> do this from uh beside here so if you go over
and find where it is and you drop down you can
2141.1 -> see the filters come up here so if I select all
hit okay everything's there now I'm just going
2146.62 -> to go back to what I was before on this because
I just just want to start with we'll say one of
2152.44 -> these just it'll make a little bit easier when we
visualize our graphing so now if I go and go up to
2160.18 -> Pivot chart here and I'm going to go click this
pivot chart and I can pick kind of what I want
2165.16 -> you can see how it has the column one if I hover
over it shows me each uh each semester how things
2170.5 -> are going I could maybe do a line I'll maybe I'll
do a line here like this and hit okay so I could
2177.28 -> hit okay and just like that now I have this one
here and I could filter through so just like I
2183.52 -> showed you before if I wanted to pick a different
one maybe we'll go with American society hit okay
2191.44 -> and you can see how it changes so we could go
through we get our pivot chart it allows us to
2196.96 -> view our data what we exactly want to see I could
go through and edit this chart to make it look
2204.16 -> nicer I'm not going to do this in this one I like
in my other tutorial I do get bit more into this
2209.56 -> one but this is more just like what I want you
to show you the data that is being chose but
2214.96 -> let's say I want to add a slicer to this I don't
want to drop down over here and picking each one
2221.44 -> slicers are a great way to make a interactive
quickly change what you're looking for so if I
2227.68 -> go up to slicer up here to see all the because
I have my relationship my data model I can pick
2233.86 -> what I want and what was it well it was the class
name so if I go class name hit ok now I have this
2241.72 -> slicer and I'm just going to go ahead and move it
down here just so it fits into the shot and so now
2248.08 -> I could go through if I go back to Algebra I have
this slicer that allows me to go through and check
2254.62 -> to see gives me that quick visual of what each
course is doing through the different semesters
2261.64 -> to see if there's any uh fault any ones that fell
off all of a sudden some of them don't have every
2267.7 -> semester that they're offered all the time but as
I go through it gives me that quick visual to see
2274 -> if anything is standing out so I hope you like
this walkthrough of how to use power pivot in
2283.06 -> Microsoft Excel we covered a lot of information
here today but I hopefully got through of how
2288.7 -> great of a tool this is to bring in when you do
have so much data millions of rows where you can
2295.42 -> perform those calculators calculations and then
you can dig down with the pivot tables or your
2302.86 -> pivot charts and to really where you can share
that information with other people and really see
2308.44 -> what's happening so thanks for watching this time
on teachers Tech let me know what you're looking
2313.9 -> for Down Below in the comments if you're looking
for more intermediate Excel or what type of Excel
2319.24 -> or other Tech thanks for watching this time see
you next week with more Tech tips and tutorials
Source: https://www.youtube.com/watch?v=kyGhgreDNUQ