Hey everyone, Kevin here. Today I want to show you how you can use Power Pivot in Microsoft Excel. So, what is Power Pivot? 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 Power Pivot 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, but what’s the difference between Power Pivot? Well, just like the name implies, Power Pivot is 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 Power Pivot, 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 Power Pivot. Power Pivot comes with Microsoft Excel. We just have to make sure that we turn it on. To turn on Power Pivot, 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 Power Pivot 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 Power Pivot 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 Power Pivot is a com add-n, 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 Power Pivot for Excel, and once I’m all done, let’s click on OK. Now that we’ve turned on the Power Pivot 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 this 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 placed 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 Power Pivot, 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 Power Pivot, let’s go up 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 Power Pivot, 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 here from other sources. Chances are if you have data, you’ll be able to bring it into Power Pivot. 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 it 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 saved 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 check box 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 could 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 could click on preview and filter and here I could 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 and 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 of the orders into Power Pivot, and down in the bottom left-hand corner, you can see that there are two different sheets. We have all of 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 Power Pivot, but instead of going through and selecting other sources, I want to show you how you can bring it in if it were a ready 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 Power 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 table1, 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 Power Pivot 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 customer 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 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 is 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 1 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 1 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 defined 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 will 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’s 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 XLOOKUP 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 of 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 equals 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 can 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, and 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 that 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 Power Pivot for now, and that’ll bring us back to our main spreadsheet. Right up here under Power Pivot, there’s 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 Power Pivot 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, and I think that works fine. Right down here, I can enter in a measure name and measure 1’s 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 a count of customers is a number, so I’ll select numbers 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 Power Pivot. 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 5. 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’s sign. I want to calculate the total profit across all of my orders. So, I’ll enter the equals 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 1’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 could 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’s 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 1, 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 Power Pivot for now. Next, I want to visualize the data in different ways, and it will 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 Power Pivot. 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 in the intro and 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 5 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 of 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 value field. Right now, it’s sum, 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 Bites 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 Bites 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 could choose a measure and I could 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 the customer is maybe under 400,000, maybe we need to work with our sales team to really drive some better sales, and then if the customer is 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 -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 could 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 Tres Delicious, our profit’s not that high. I’m going to 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 Power Pivot, 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, you can see how much of the total Acme makes up, how much Wholesome Foods makes up, and once again, Tres Delicious, we need to get that profitability up some more. Now just to show you the true power of what you can 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 to 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 Bytes is also the most profitable for chocolate chip cookies, but here if I click onto Fortune cookies, I actually Wholesome Foods has the highest amount of profit for Fortune cookies, so right here I could 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 Power Pivot 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. All right, well, that’s all I had for you today. I hope you enjoyed, and as always, I hope to see you next time, bye.