what I'm gonna do here is I'm going to introduce you to power pivots power
pivots are an excellent tool if you have large amounts of data or you data that's
not coming from Excel but from other other software or if you have let me
show you how to install power pivots power pivots is an ad on Excel 2010
you'll have to download the the add-on from the internet 2013 or 2016 it's an
add-on that's built-in to Excel itself it's an add-on that's provided by sequel
server it's not an add-on as per Excel so let's go through the steps and how to
install it to start with so we'll go file and options and then we
will go up to add-ons and then what we'll do is we'll go down to the bottom
here and on the manage on the add-ins page on the manage we're gonna drop down
and we're going to change your select Tom add-ins and we'll select that and
we'll go click on go and then this pop-up appears and you see that I have
Microsoft Office PowerPoint for Excel 2013 checked and you will check this and
then you'll say ok when you have this checked on there you have this power
pivot tab on your on your excel sheet now so now let's go here if you go to
this and then you go to manage it will bring up a new window and so now it's
going to ask you where to get data from so so if we're gonna get external data
and now you can get it from databases SQL Server from access from analyst
analyst service in PowerPivot or you can get it from data services you can get
either either table the import wizard you can get
there's a whole list the whole list down so you can get data from anywhere you
basically to to work with powerpivot and so what I'm going to do is I'm going to
show you how to do it from the excel sheet that you have so
now I've opened up a new excel sheet and I have two tables of data on the sheet I
have on sheet 1 I have sales for this particular organization and on sheet 2 I
have the the people that account numbers and people that are the salespeople for
these for these for these companies for this organization so what I'm going to
do now they are in tables if they're not in tables create a table you need to
have them in tables before you can use them in the pivot view so I have my
pivot view table open and what I'm going to do is I'm going to click Add to model
add two data data model and the power view view comes up and they're my data
has been added to my to my data model okay and so I have I have approximately
9000 records in here and on my sheet - i have my salespeople and the customer
numbers that they deal with so we're gonna click in this this is a table as
well and we are going to add two data model and so now you have the second
sheet added in so now we have on our our pivot or power pivot view here we have
the two tables we have Table one which has nine thousand 64 records and
on the second one with salespeople and the accounts that they work for we only
have a three thousand twelve now advantage to pivot views if you work in
data analysis like I do 1,400,000 records which is a limit for
so quite often is not enough in using pivot views you can have 7 million power
records on your data so you have the ability to analyze far far more data
than you have then displaying Excel itself ok so now what we're going to do
is we are going to link these two tables originally when I ran this I just
I did a index match and I matched the account numbers to the salespeople but
this is far easier what we're going to do we're going to click up here into
design and we are going to create relationships and so now I have table 1
and I want my account account number and I want it linked to my table 2 is the
number I have salesperson code and I have the number and I would like to
create a relationship between there so
we're happy now and so now we would like to do a pivot table both of these tables
together so let's go back to the home tab and let's click on pivot table and
now it is going to put a new workbook and your existing Excel worksheet so the
data is going to be displayed in your excel book so you say ok now it looks
like the same thing as you have for a normal pivot table but the difference is
is you have the tables listed you have both tables listed and not just the one
and you can check you can change select any of the fields from either of the
tables so now let's go down and let's click on my attitude on my table and I
have the two fields that are available to me in for the other table so what I'm
going to do here is I am going to so from the second table I'm going to
select the salesperson and I'm going to put it in rows and the first thing and
the what I would like to do is figure out how much are these salespeople are
making so what I'm going to do is I'm going to go and I am going to total up
the amount so what I'm going to do is I'm going to put this in the total field
and that's as quick and as easy as you can figure out how much how many sales
or how much money these people have sold so now I have clicked on my pivot table
screen that's now added because I have a pivot table on here and I'd like to
create a pivot chart so I'm going to select this one and I'm going to say ok
and so here you have the data on your pivot table for the sales for these
salespeople and he have your your pivot chart done quickly and easily so this is
an introduction to power pivot I will be making a couple more - additionally to
this one as well please subscribe