How to use Power Pivot in Excel

hey everyone kevin here today i want to show you how you can use powerpivot in microsoft excel so what is powerpivot well it allows you to analyze data and also get insights from your data and in this video today i'm going to make this as simple as possible even if you've never heard of powerpivot before maybe you've never used it before i'll walk you through step by step how you can start taking advantage of it today in fact if you want to follow along i've also included sample files in the description i think the best way to learn is actually by doing so feel free to follow along so you might be wondering well hey i've heard of pivot tables before but what's the difference between power pivot well just like the name implies power pivot's even more powerful you can analyze data across multiple tables with a standard pivot table you're locked into just one table of data also with powerpivot you can analyze massive quantities of data we're going to start off by creating a data model then once we get our data in we'll create some calculations we'll create some measures and then we'll visualize our data with pivot tables pivot charts and we'll even add in some kpis all right well why don't we jump on the computer and let's start analyzing here i am now in microsoft excel and first off i want to show you how you can even get powerpivot powerpivot comes with microsoft excel we just have to make sure that we turn it on to turn on powerpivot let's go to the top left hand corner and click on the file menu within file let's go all the way to the bottom to options once you click on options this opens up a prompt and powerpivot is an add-in for microsoft excel over here on the left-hand side you'll see an option for add-ins let's click on that right here there's a section called inactive application add-ins and if we look down this list we'll see powerpivot right here it's currently inactive and we want to activate this over on the right hand side we see that it's a com add-in so right down at the bottom there's this drop-down list next to manage and when i click on this we can select com add-ins because powerpivot is a com add-in let's click on this and then click on go this opens up yet another prompt and within here we see a few different add-ins that we can enable right now they're all turned off and i can simply turn it on by clicking on this checkbox so here i'll click on microsoft powerpivot for excel and once i'm all done let's click on ok now that we've turned on the powerpivot add-in let's look at the top ribbon here and over on the far right hand side you'll see a new tab for power pivot and when i click on this this exposes all of the different power pivot options and in a moment we'll run through this but before we start doing that i want to open up the sample data just to orient you to the data that we're going to be working with today we're going to be working with three different sample files today one of them is called customers we also have orders associated with those customers and then we also have cookie types the customer sheet includes all of the customer information for customers at the kevin cookie company so you see the customer name there's a whole bunch of contact information related to these customers don't worry this is all made up information so don't try calling any of these customers and then over on the right hand side there are also some notes related to these customers the orders sheet contains the bulk of the information here we can see the customer id who place an order we also see the order id what product they ordered how many they ordered and also the date of the order and lastly there's also a cookie types sheet and in here you'll find all the different cookie types that we make here at the kevin cookie company along with the revenue per cookie and also the cost per cookie and before you start questioning our profit margins keep in mind it takes a lot of love to make these delicious tasting cookies i'm now back in microsoft excel in my blank workbook and i'm ready to start using powerpivot and the first step is to create a data model whoa what is a data model what are we going to be doing well it's not really that complicated a data model is simply a collection of tables or data so we just looked at three sample sheets that all contain data we're going to create a data model using all of that data that we just looked at and then we're also going to establish relationships between the data on all of those different sheets and we're going to walk through how to do that but to get started within powerpivot let's go to the top left hand corner and click on manage this now opens up the core power pivot experience and right now we just see this big blank white screen we need to bring some of our data into powerpivot so i showed you the data in an excel sheet and you can bring in data from many different places right here on the home tab we see this section called get external data and you can get data from all sorts of places here for example i could get data from a database you could also get it from other services or hear from other sources chances are if you have data you'll be able to bring it into powerpivot now i have my data in an excel file so right here at the very bottom of the list there is the option to select an excel file you could also have it in text files or many other types but let's click on this and then click on next on the next screen here shows a friendly name for the connection and it's currently set to excel that sounds fine to me right down below i can select an excel file path so this is where our spreadsheet is located and if you're following along go to wherever you save that file i'll click on browse and then navigate to the file this opens up my file picker and i want to start by bringing in the customer information i'll click on customers and then click on open back within the prompt here i see a checkbox that says use first row as column headers now if you remember when we looked at the data the first row contained the column headers so let's check this box there are also some advanced settings and we can test the connection but we don't need to do that let's just click on next right here i can see that it found the source table it looks like it found the customers so this looks great i can click on preview and filter and here i can preview what the data looks like this is the exact data i was expecting so i'll click on ok right here next let's click on finish here i see that five rows were successfully transferred that looks great so let's click on close i've brought in the customer information but once again i said that the power of power pivot is that you can connect multiple different data sources and so i want to bring in the rest of my data so once again let's go up to get external data and click on from other sources again and the excel files at the very bottom so let's go down again and click on excel file right here i'll navigate to the next file and this time i want to bring in all of the orders so let's click on orders and then click on open right here again the first row contains the column headers so let's check this box and then click on next here it found the table we don't need a preview because i know it's going to work right next let's click on finish here we see now that it successfully imported the 700 rows of data that looks good so let's click on close here now you'll see that it successfully imported all the orders into powerpivot and down in the bottom left hand corner you can see that there are two different sheets we have all the customer information and we also have all of the order information next i want to show you how we can get the cookie type information into powerpivot but instead of going through and selecting other sources i want to show you how you can bring it in if it were already part of your excel spreadsheet i have my cookie type spreadsheet open and here i can see the table with all of the cookie type information i'm just going to copy this table and i'll bring it over to the spreadsheet where i'm building out this pivot so right here i have this table of information i'll blow it up so we can see it a little bit better now let's say you already have a table or some data in your spreadsheet you can very easily add that to your data model here i'll highlight the entire table and then up within the power pivot ribbon i see this option to add to data table let's click on this here now you can see that it added all of the cookie type information to my data model right down here i have an additional tab now it brought it in with the name table one and that's not really that descriptive so i'll double click on this and let's just call it cookie type once you're done typing that in we now have successfully brought all of our data into this data model one of the benefits of using powerpivot is not only can you look across multiple tables but you can also define relationships between your tables so just as an example here if i click into the customers sheet you'll see that i have all of these different customers at the kevin cookie company and they all have a customer id now if i click over into orders you'll see that these orders are all associated with these customers but excel on its own doesn't know that there are relationships between this data so i want to establish or define what these relationships are similarly here we look at the product name and here if i click into cookie type i see the product name or the cookie type here as well and then here i see the associated associated revenue and also cost so i want to tie orders together with cookie type so i have a few different relationships i just need to define them right now to define these relationships let's go up to the home tab up on top and over on the right hand side there's an option for diagram view let's click on this here i can see all of my data and right now it all appears in these rectangles here i could pull it down just to expand the view so i could see all of the different fields within that table i'll do the same here and also here now once again i want to define relationships between this data or all these different tables and here you'll see in customers i have a customer id and in orders i also have a customer id so this data is related so here i can click on customer id and i'll press and hold and drag over to customer id over here you'll see this line appear once i release now you see that there's a connection between these two different tables here i can hover over and this once again shows me the different fields that i related to one another so there you see customer id and also customer id over in orders with this line one other thing that you can see is over here there's a one and over here there's an asterisk basically what this means is it's a one to many relationship so let's take an individual customer one customer can have many orders so that's why you have a one here and an asterisk now i also want to connect the cookie type to the orders table and as we looked at earlier we saw that the cookie type is the same as the product it has a different name here but it's the same information so as part of every order let's say someone orders a chocolate chip cookie well in the cookie type table we have chocolate chip cookie so here i'll click on this value and just like we did before i'll drag this over to cookie type we want to relate these two different items and here this has established once again a one-to-many relationship so for cookie type like chocolate chip that could be part of many different orders so once again for one cookie type it could appear on many different orders now let's say that maybe i made a connection and i didn't mean to i could click on this line right here and i could press the delete key and then i could delete it from my model but once again i did it correctly so there's no need to delete this so i'll simply click on cancel but just in case you make a mistake and you want to go back you can very easily return to the previous state now that i've defined all of these different relationships let's go back to the data view back on the data view now i want to add some calculations here if i click into the order sheet i want to know well how much revenue did we make per order what was the cost and what was the total profit i can use a calculation here to tell me that now here you'll notice that i don't actually have any revenue or cost information for these different cookies within this table but if i jump over to cookie type here you'll see that for each cookie i know the revenue and also the cost for that cookie and because we define these relationships i can use those relationships to pull that data in here so why don't we run through an example to see how it works i'm going to expand some of these columns just so it's a little bit easier to see so first to calculate the profit for each order well i take the revenue and then i'll subtract the cost and that'll give me the profit so first let's add a column here called revenue i've now added a revenue column i also want to add a cost column let's click here and let's just call this cost and lastly i also want to add another column called profit so i'll click in here just double click and then i'll type in profit to calculate the revenue i'm going to take the revenue from the cookie type table and i'm going to multiply it by the units sold so here i can type in an equal sign and when i type in equals you'll see it appear right up here so it's kind of like just typing in an excel formula and i want to pull in the revenue information from the other sheet so here i'm going to type in related so this will return a related value from another table if you've ever done a vlookup or an x lookup before it's kind of a similar concept so here i'll click on related and this shows me all of the related information so because i tied all of these tables together i get access to all these different fields and here i see that in the cookie type table there's a field called revenue per cookie i'll select that and then let me close the parentheses here once i close the parentheses this is going to pull in the revenue per cookie for chocolate chip next i want to multiply it by the units sold so i'll insert a multiplication sign or the asterisk and then i'll type in units sold here when i type in units sold here it finds the value from the orders table so i'll click on that and now my formula looks good i'll hit enter here now you'll see that it automatically calculated the revenue for every single one of these orders so that's pretty cool how i'm able to connect the information from these other tables and then calculate this value it worked pretty well now i'm going to do the exact same thing with the cost and it's going to be the same as with the revenue here i'll enter the equal sign and here again i'll type in related when i click on this here i want to pull in the cost per cookie from the cookie type table so here i'll click on this value i'll close the parentheses and here i'll multiply it once again by the units sold there i could also just click on it over here and that also inserts this value or this field once i'm all done i'll hit the enter key here too you'll see now that it filled in the cost across all of these different orders lastly i want to calculate the profit and this is fairly easy to do now i'll insert the equal sign and now i can simply type in revenue and here you'll see that there's a new column called revenue so i could select that column then i'll hit minus and next i want to type in cost and here you see that it also finds the sheet called orders and cost so i'll select that item and then hit enter so check that out i now have the profit for every single one of these orders at the kevin cookie company and i don't know if you believe me but in all these tutorial videos i keep telling you the kevin cookie company is a pretty good company we have some pretty good profitability here now so far we've added a bunch of different calculations so we've added these different columns and we've calculated across all of these different rows but what if we want to find out well how many customers do we have or what is the total profit or what is the average profit per customer we can use measures to calculate that and for now let's go to the top corner and let's close out powerpivot for now and that will bring us back to our main spreadsheet right up here under powerpivot there is the option for measures let's click on this right here and let's add a new measure i'll show you how you can add a measure here and then we'll jump back into powerpivot in a moment and we'll also add some measures there let's click on this this opens up a prompt where i can start defining my measures and here i could pick the table that i want to create the measure on i'm going to put this on the customer table i think that works fine right down here i can enter in a measure name and measure one is not that descriptive so let's replace that and i'm going to type in total number of customers i want to know how many customers we have at the kevin cookie company i could also type in a description but i think the measure name is pretty descriptive so no need for a description here right down below i can now type in a formula so here it already has an equal sign and i want to count or get a distinct count of the number of customers just like in excel i can use similar functions so here i'm going to type in distinct and here you see i have a few different options and here's one called distinct count when i click on it it tells me that it counts the number of distinct values in a column now if you remember in my customer sheet each one has a unique id associated with that customer so i could get a distinct count of all of those ids so here i'll select distinct count and then next i can choose one of my fields so here i see all of my different field values and i want to get a distinct count of the customer id so here i'll choose customer id and then let's close the parentheses and here i can check the formula if i want looks like it's working fine now account of customers is a number so i'll select number as the category i don't need any decimal places and right down below let's click on ok i've now added my first measure and to see that measure why don't we jump back into powerpivot here let's click on manage up above i'm now back in power pivot and it dropped me on the order sheet let's jump back over to the one called customers and this is where i added my measure and when i click on customers at the very bottom i can see all of my measures here you see total numbers and then dot dot dot well that doesn't do us much good so let's expand this column width here i'll click on here and i'll expand the width so here you can see the output of my measure it says the total number of customers is five when i click on this down below here i can also see the measure up here so here i see the measure name and then i also see my formula next i want to show you how we can add a measure directly on this sheet so right down below i can click under the previous measure that i created and right here let me type in an equal sign i want to calculate the total profit across all of my orders so i'll enter the equal sign and then let's type in sum so it's just like entering in formulas and functions in excel here i'll select sum and now i can choose what i want to sum up so right here i'm going to go through and find the value or i want to find the field for profit so right down here i see orders and i see profit i'll select this one and then let's close the parentheses next i'll hit enter right now we can see that measure one has this amount of profit so if i click on it again here i can update the name now measure one's not that descriptive so let me remove that and here i'll simply type in total profit once i type in total profit i can hit enter and i've now renamed this measure and it's not really formatted that well so i can click on this item and if i go up above here i'll format it with a dollar sign or a currency sign and i'll choose usd for now here now you'll see that we had about 2.7 million total profit not a bad year next i want to add one more measure and for this one i want to use the two previous measures that i created i want to know the average profit per customer so right down here i'll enter an equal sign and once again i can type in my formula up here now to find the average profit why don't we take the first off the total profit and i'll type in total profit and here this brings up the measure that we previously created so i'll select total profit and then i'm going to divide it by the total number of customers so here i'll type in total and here we see total number of customers so i could use this measure as well so i'll select that item and then let's hit enter so here down below you'll see that it added another measure down here and once again it's simply called measure one so i'm going to update this and let's change this to average profit per customer next let's hit enter and i've now renamed this measure and once again let's format it i'll go up here and apply the currency formatting and here you can see that on average we make about 543 000 just per customer at the kevin cookie company now that we have some measures and we also have some calculations let's close powerpivot for now next i want to visualize the data in different ways and it'll be really useful to use a pivot table to do that to insert a pivot table let's go up to insert in the top left hand corner and over on the left hand side there's the option to insert a pivot table let's click on this drop down now we don't want to insert a pivot table just from a table or range we want to use this data model that we created remember the data model is all of this data that we brought in and all of those relationships let's click on this right here this opens up a prompt where i could define where i want to place the pivot table i want to put it on a new worksheet let's click on ok this has now created a new pivot table for me and i haven't yet selected any fields so i need to select some fields before the pivot table comes alive but before we do that i want to call out something that's really magical about powerpivot if we look over on the right hand side at the pivot table fields here you'll see now that it's connecting all of this data together in the pivot table so remember the intro when i said if you just create a standard pivot table you can create it based on one table of data but here now i've brought in multiple tables of data and the pivot table works the same way as a standard pivot table let's say i want to see my customers and then i want to see how many orders each customer has placed it's pretty easy here i'll click on customers and let's expand this category so here i can see all my customer information and i'll pull in the customer name i'll just click on that and drag it down to rows so over here you'll see all of my different customer names once again we have five customers at the kevin cookie company next i'll go over and let's minimize customers over in the pivot table fields area down below let's click on orders now and i want to know how many orders each customer has placed so here i can click on order id and i'll drag it down into values now right now it's just summing up all the order ids here you can see that it's summing i can click on this little drop down and i'm going to click on value field settings over here i can choose how i want to summarize the valued field right now it's some but let me change that to count and click on ok so just like that now i can see how many orders each customer has placed so here i can see that acme bytes has 206 orders that's quite a few orders but the neat thing here is remember that the customers data was separate from the orders data but because i defined those relationships here i was able to pull in the customer name i was able to pull in the orders and then that data is now related and i can very quickly analyze this data and also get insights from this data now one thing that's really powerful just a moment ago we created all of these different measures we can use those measures in our pivot table so let's say i want to see how much profit i made for each one of these different customers so over here on the right hand side i can expand customers and right down here i see my measure called total profit right here i'll click on that and let's pull that in once i pull that in here too i can very quickly see how much profit i made for each one of these customers so not only has acme bytes ordered the most but i've also made the most profit from them i need to make sure i take really good care of this customer because they're driving most of our profitability now acme bytes has quite a bit of profit and some of them aren't quite as high as i would like them to be so maybe i want to add some kpis around how much profit each customer is pulling in right up on top on the ribbon once again let's click on power pivot and over here there's an option for kpis let's create a new kpi this opens up a prompt where i can define a kpi or a key performance indicator this basically helps me very quickly identify how we're doing with that customer so with some of them i want to get the profit up so right here i can choose the kpi base field and here i have all of the different measures that i added in now i want to look at the total profit as a kpi so i'll select this one right down here i can choose a measure and i can choose one of these but i want to just go with an absolute value so let's say that maybe my targets let's say i want to try to get around 600 000 per customer in profit so i'll select that and then right down here i can define what's considered red what's considered yellow and what's considered green so right here i see on the low end i have about 300 000 so maybe i'll move this up and let's say if a customer is maybe under 400 000 maybe we need to work with our sales team to really drive some better sales and then if a customer customer's green maybe we look here and maybe it has to be above 600 000 to qualify as green down below i can choose an icon style i could also click into descriptions if i want to define what these different colors mean but for now i'm good with that so i'll just click on ok this has now added another column to my pivot table but what happened to all those nice colors here i see 0 1 and negative 1. well let's go back over here on the right hand side and you'll see a new icon added with this traffic light symbol if i click on that here i'll simply remove them for now and then i'll re-add them so here now i can see the color associated with the with all of these different customers i could also click on the goal so here i can see the goal is 600 000 for each one of these and i can see how each customer is performing compared to the goal so right here with trey delicious our profit's not that high i'm gonna have to talk to our sales team to see if they could push a little bit harder to sell even more cookies with our kpis now in place we've just been looking at all of this data in a table format but sometimes you want to just visualize data in a more visual way so right up on top when we have the pivot table selected let's go up to the top to pivot table analyze and right over here we can also insert a pivot chart let's click on this here i can choose the type of chart and maybe i want to see with a pie chart how much profit each customer makes up of the total so over here i'll select pie chart and the default one looks fine so i'll click on ok now right over here i don't want to show the count of orders so i'll simply drag that out i just want to see the total profit and here very quickly you see that i took all of this data that i had in powerpivot i was able to create a pivot table from it and now i have a pivot chart so right now i can visualize this data and maybe i want to sort this from the largest to the smallest so i'll click over here let's right click i'll go down to sort and i'll go from largest to smallest so right here in a visual way i can see how much of the total acme makes up how much wholesome foods makes up and once again trey delicious we need to get that profitability up some more now just to show you the true power of what you could do with pivot charts let's once again go up to pivot table analyze and i'm going to insert in a slicer let's throw a slicer in and here i see all the different items that i can slice the data on now you might notice i only see the customers and the orders but what if i want a slice based on the cookie type well over here i can click on all and here now i see cookie type it just has to do with the way we brought our data into the data model i just brought in a table so right here i can see everything so let me select the cookie type and then i'll click on ok and right here you now see that i have the slicer so right now i see the total profit across all cookie types for each customer but what if i want to know for chocolate chip here i can simply click on that and here you see that it updates the table and it also automatically updates the chart so right here i can see that acme bites is also the most profitable for chocolate chip cookies but here if i click onto fortune cookies actually wholesome foods has the highest amount of profit for fortune cookies so right here i can just click through this slicer and it automatically updates my table and it automatically updates my chart so this is a very fast way to analyze and look at your data and also to gather insights from your data hopefully by now you're starting to see the tremendous power of power pivot you can bring in multiple tables of data you can relate that data and then you can visualize the data in tables and also charts to get insight from your data now data on its own isn't really that valuable the value comes from being able to extract insights from that data and powerpivot makes that a lot easier all right well hopefully you enjoyed this video if you did please give it a thumbs up to see more videos like this in the future make sure to hit that subscribe button also if you want to see me cover any other topics on this channel leave a note down below alright well that's all i had for you today i hope you enjoyed and as always i hope to see you next time bye you

Channel: Kevin Stratvert

Views: 261,744

Rating: 4.9749498 out of 5

Keywords: kevin stratvert, power pivot, excel, microsoft excel, excel power pivot, power pivot excel, powerpivot, power, power platform, pivottable, pivot table, pivot, table, pivotchart, pivot chart, add-in, add in, data model, relationship, relationships, data, model data, calculation, calculations, measure, measures, insert pivot table, pivot table excel, tutorial, power pivot tutorial, pivot excel, in excel, how to use, pivot tables, charts, chart, create, make, power pivot in excel, excel pivot

Id: rB_IiYbOo7w

Length: 30min 38sec (1838 seconds)

Published: Fri Mar 05 2021