Advanced PivotTables: Combining Data from Multiple Sheets

hi welcome to Tut's plus my name is Bob flu sir what do you do when you're creating a pivot table and the data that you want to combine are coming from several different worksheets well you're in luck if you're using the 2013 version of Excel Excel 2013 has this great feature called the data model and it allows you to create relationships kind of like the way you do in a database if you want to follow along in this tutorial using one of your own files that's great or if you want from the touch plus page where you're watching this tutorial you could download a zip file that contains the Excel workbook that I have open on my screen and you can see here it's called pivot consolidate down here on the bottom you can see that there are three worksheets in this workbook let's take a quick look at them before we do anything you see here we're on the customer info sheet and we have a field for order number and some general customer information we go to the order info sheet we see we also have an order number field plus month product and whether the fruit is regular or organic and then if we go here to the payment info sheet see again we have the order number and now we have some payment information so what we're going to do before we even create the pivot table is we want to convert each of these sheets into an actual table so let's go back here to the customer info sheet and I'm going to click somewhere in here somewhere in the data area and what I'll do is I'll go up here to the insert tab and third item over here on the ribbon bar table I'm going to click that so we insert a table of course we're not actually inserting it we're really converting it and since I'm clicked inside this data area the create table dialogue box correctly guesses oh yes this is the data area you want yes the table does have headers if you're doing this and for whatever reason this check box isn't check make sure you check it click OK and now you see we have a table if you want click somewhere in here to deselect don't click out here you want to make sure to click in here and if you want you can scroll up to see the top of the worksheet because it's a table we have this nice striped formatting and we have these filter buttons but that's really not what we need what we do need is because we just convert it to a table you see Excel puts us into the design chap and once again if we look over on the very far left side of the ribbon bar we have the name of the table so let's take that just click there and instead of Table one I'm going to call this customer underscore info and I'll just press ENTER Excel doesn't allow you to have spaces in the name of table so you could use either an underscore or a mixed case let's do that two more times go to the order info sheet click somewhere here in the data area insert table yes it guesses correctly click OK click somewhere in here to deselect and instead of table two let's click that and we'll call this order underscore info press ENTER and third time go to the payment info sheet and click somewhere in the data area insert table click OK click somewhere in here by the way you don't have to click in there you could leave it selected doesn't really matter and then I'll go and instead of table three I'll call this payment underscore info ok finally we're ready to go and create the pivot table so I'm over here on the payment info sheet again make sure you're somewhere in this table and P of the tables are also things that we insert so you go back to the insert tab and there's a pivot table you want to make sure to click pivot table not recommended pivot tables by the way recommended pivot tables are one of the best new features of Excel 2013 but that's not important right now we'll click pivot table and so sorry select the table or range well we're already there that's great we want to put the pivot table in a new worksheet that's exactly what we want but here's the key down over here where it says add this data to the data model click a checkbox in there click OK now it creates this blank pivot table and we have the task pane over here on the right and it doesn't look too much different from a regular pivot table except you see have this section here active and because we created it from the payment info sheet or table we have payment info and we have the fields there but you see over here it says all click all and check this out now we have the other tables that we created but we can't use them yet because we have to go and relate these tables to each other also just for clarity instead of calling the sheet one I'm just going to double click that and I'll call this if it table just so we know what it is maybe I'll just drag this over here if you're familiar with setting up relations between tables in a relational database like Microsoft Access this will be familiar to you if not that's okay I'm going to explain it when we looked at these three sheets we saw that the first column in each one was the order number column the order number field that's where this comes into play when we set up the relationships we're going to say alright the payment info table is related to the customer info table where the order number is the same between each one and the payment info table is going to be related to the order info table also where the order number field is the same there's no reason to relate the customer info table to the order info table because they're related implicitly through the payment info table so here's how we go and set up those relationships so again we're on the pivot table sheet unclick somewhere in this blank pivot table and we're over here in the analyse tab because we're clicked in the pivot table we have the pivot table tools in these tabs if you click somewhere outside it those tabs go away so just make sure you click somewhere inside the pivot table and that you're on the analyze tab and over here you see I have this button for relationships click that and we have the manage relationships box so we're going to create two relationships first click new and this is all right well what table do you want so I'll click there and I'll choose payment info and the column that I want is order number and as I just showed you in the diagram we're going to relate the payment info table to the order info table also at that order number column now let's say the order number field was only in one of those sheets it might still work if you don't have that order number column in the other sheets but there's a greater chance for error so it's just a lot better to have this by the way that order number is unique so again if you're familiar with databases you might be familiar with sent of a primary key a primary key is a unique value that appears once in the table if you think of something like a social security number that's something that's going to be unique so we are relating the payment info table to the order info table where they both have that order number click OK and shows up in here we'll create the second one so again click new and again we'll say payment info at the order number field we're going to relate that to the customer info table also at the order number and again click OK and now we have the second relationship in there so that's all you have to do here and down here on bottom click close now there's nothing visibly different here in this pivot table task pane but if you had not created those relationships then when you start dragging the fields into the pivot table weird things would start to happen so let's go and expand these you see each one of these tables has this little twirly arrow and you can click those to open them up now we don't have to use the order number in the pivot table the order number filled its function it did it's Karma and it's relating the tables so let's go and put some other fields into the table so from the customer info table I'm going to go and take state and I'm going to drag that into the rows section let's scroll down here I also want to take month and drag month into rows and you see what's happening there in the pivot table and let's take product and drag product into columns and what we're really interested in let's go and roll open payment info what we're really interested in is the sale that's the dollar value so let's take that and drag that into sum of values and also let's set up a filtering up here on top and if you remember from each of these worksheets we had the distinction is the customer a new customer or an existing customer that's what we called status so here from the payment info table let's take status and drag status into filters so now we have a fully working pivot table and we can see each state how each state did in each month with each product and you can go and filter instead of all maybe we just want to see let's say exist customers and okay that or maybe we want to see only new customers and filter that and now it works just like any old pivot table that you'll work with so you can see that the new data model feature in Excel 2013 allows you to cherry-pick different fields from different worksheets into the same pivot table just keep in mind that the rows of each table do have to be related to each other somehow and you will have the best chance of success when those tables do have some common field like an order number so I hope you enjoy this and you found it helpful once again for tuts + my name is bob Flusser and i'll see at the next video

Channel: Tuts+ Computer Skills

Views: 1,604,251

Rating: 4.9231739 out of 5

Keywords: Microsoft Excel (Software), Pivot Table, spreadsheets

Id: Tu6SGU-rK6c

Length: 9min 37sec (577 seconds)

Published: Sun Jun 01 2014