Excel Data Modeling - Stop Using XLOOKUP | Learn PowerPivot

okay so let's say the boss wants you to connect some Excel list together because they're going to ask questions that require pulling data from both lists to solve this some folks may decide to use the xlookup function or maybe even the vlookup to connect retrieve and consolidate the data from the list yeah let's face it this can be tough to accomplish and a real headache in a suitcase to build so let's see if we can make our lives easier and use something called Data modeling in Excel in case you're unfamiliar with data model in in super simple terms all it means is you're going to connect some lists or tables together and we're going to use power pivot to connect our list because power pivot allows us to join together sheets from an Excel file or from separate files of which we'll show you how to do both power pivot also allows us to create relationships between our lists and that just means you can connect the sheets together which in turn allows you to pull data from across multiple sheets the best part is you can then build pivot tables from the data model you've created in power pivot to begin let's take a look at the data we're going to use here we have a list of our orders that the companies received and some of the details they keep track of is the product category the subcategory the customer source which is just how the customer found us the order date the quantity ordered the price of the item and the customer ID now if we jump over to our customer sheet here we have the customer first name their last name their contact information and notice that each customer has their own unique ID so if we jump back to our order sheet you'll notice that the customer ID is also included now this customer ID is a common field between the lists and what it's going to do is act like a bridge between the two lists so Pro tip for you if you ever try this in real life your lists need to have a common field between them so Excel will know how to connect the data between the lists more about that later moving on we want to make sure the power pivot has been enabled if it has not been already so to enable it go up and click on where it says file options addins and then you should see it here in this inactive area to activate it click down here on the drop Arrow switch it to the com setting then click go and from there just look for Power pivot just check the box click add and once it's installed you'll see it up here on the ribbon okay so the next step is to tell power pivot what sheets or files we want to use to begin let's make sure we're in a new Excel file then click on power pivot then manage and that will open up power pivot in Excel the first step is to import the data we want to use and we can do that by going up here and clicking on from other sources then scroll down to the Excel section click it and choose next and on this next step we need to tell Excel what file we want to use so click on the browse button navigate to our source file and then after we select it make sure to click where it reads use first row as column headers then go ahead and click on next and we can see both our sheet and our file right here and since we want to use both let's go ahead and check both of them go ahead and click on finish Excel will import our data and then we can click close okay we can see that Excel has brought in the data from the orders sheet and the customer sheet which is good good so the next step is to connect our lists so they can talk to each other this is technically referred to as creating table relationships for starters be aware that the terms list and tables are used interchangeably they essentially mean the same thing so moving forward list equals tables all right so for your benefit let's have a real quick chat about the types of table relationships you can work with there are three types of relationships you can have between tables and they are one: one one to many and many to many now explaining these various types of relationships can really be its own video but for today let's just keep it simple the most common types of relationships you'll encounter is one to one and one to many an example of a one to one relationship would be one employee one paycheck or one vehicle to one vehicle identification number the next kind of relationship is one to many an example of a on to many relationship is one student can take many classes one employee can be on many projects one product can be sold many times one customer can have many orders hopefully by now you get the idea and you can make a really strong case that a on to many relationship is the most common type of relationship you're going to work with Okay so getting back on point of connecting our list let's go up here and click on the diagram View and that will show us our tables so in order to create a relationship between the two tables we need to have a common field between them and if you recall in our example the customer ID is the common field so we can create the relationship by dragging the customer ID field from the customer's table over onto and on top of the customer ID on the orders table when we let go Excel will wave its magic wand and build a bridge between the two tables if we study this we have a one to many relationship which makes sense if we apply some simple logic because one customer can have many orders all right so that works and just to reinforce this since we now have a relationship between the two lists we can now extract data from both of them so let's go ahead and click on the data view icon which brings us back to our initial screen okay from here it's very common for people to create calculations within power pivot of which we'll do in a little while but for now let's go ahead and create a pivot table just to get a feel of what we've accomplished so to create a pivot table let's go up here and click pivot table and then pivot table again and because we have a new file we can just put the pivot table on our existing sheet now if we look over here on the right side we can see our two sheets and since they're part of the data model we can now pull information from across these sheets so let's say we want to see our product category the last names of the people who ordered from that product category and given the data we have to work with the total quantities of each and there we go a simple demonstration to show you the data mod model we created is getting information from across both sheets you like this so far good and that's just how you're going to leave a like for this video okay admittedly this pivot table is kind of lame since the data that we're working with is not that great so let's go up here and remove our pivot table by selecting pivot table analyze clear all and now let's see if we can make things a little more practical and to do that we'll go back into Power pivot to make our adjustments so the goal here is to show you how to get back to the data model in case you ever want to work with it along with showing you how to create calculations within the model so if power pivot is still open we can jump back into it by using our taskbar but for the sake of illustration let's say you closed out a power pivot and need to get back into it so just go up here and click where it says power pivot then manage and that will bring you right into the model moving on for this example let's say we want to create a formula that shows our total sales to accomplish this we need to make sure we're on the Ord table and then click in this cell right here so let's start off by typing an equal sign then go over and click on our quantity sold followed by an Asis then click on the unit price and we can see the formula being built up here now we can just press the enter key Excel will do all the heavy lifting and right there is our numbers so the next step is to change the column heading and we can do that by going up here and doing a double click on the label and we can type in what we want in this case let's go with sales so now we're done in power pivot and we can go up here and just close out and go back into Excel now if we look over here on the right hand side specifically at the orders table notice the sales field has been created so let's go ahead and create a brand new pivot table and we can start by dragging our product category into this Rose quadrant the last name below the product category and now let's drag our sales field over into the values quadrant and check it out now we're getting somewhere okay so let's take a second and and give you a little bonus and show you how you can add another pivot table to the sheet based off the model you've already created so for starters let's click on a blank cell and then go up and select insert pivot table from data model and how about we place the pivot table on this existing sheet just for demonstration purposes so for this new pivot table let's say we want to see the customer Source the names of the people who have used that source and the sales amounts so we'll drag our customer Source here here the last name below that field and then we'll put our sales over here into the values quadrant and there we go not too bad moving on earlier we mentioned that when you use power pivot you can pull data from one file or many files and for this demonstration we've used just one file so let's conclude things by showing you how to use more than one file so here we have two files one contains the customers and the other contains the orders now in full disclosure it's the same data we've used before it's just been saved into two separate files okay so to begin make sure you're on a new Excel file then go up and click on power pivot from there on the left hand side select manage and once you're in power pivot go ahead and select from other sources scroll down to where it says Excel then choose the first file and make sure to include the column headings from there just follow the remaining steps and click on finish then the next step is to pull in the other Excel file and we can do that via a rinse and repeat so click on other sources navigate down to excel choose the file we want walk through steps and there we go so once both files have been imported the next step is to connect them to connect them let's go up here and click on diagram View and click and drag over the common field we get the relationship created then go back and click on data view if you wish and from here it's pretty much the same thing we've done before we can go up up build our pivot table add the fields that we want and we are off and running all right and that'll do it for this one we hope you've enjoyed the video and we will see you next time

Channel: Professor Cubs

Views: 76,660

Keywords: excel, excel xlookup, excel vlookup, powerpivot, xlookup and powerpivot, excel power pivot, exvel xlookup, connect lists in excel, table relationships, xlookup vs powerpivot, how to use powerpivot, excel powerpivot

Id: f3FCmKrrYoQ

Length: 10min 41sec (641 seconds)

Published: Fri Mar 01 2024