Create a PivotTable in Excel using multiple worksheets by Chris Menard

hey this is Chris Menard let me show you something really cool with pivot tables in Microsoft Excel I want to make a pivot table but the pivot table is going to be based on multiple worksheets so here's the first thing you need to do I have a list of reps that work at our company rep 1 2 3 & 4 and then there's our information so I'm going to click in here one time and just to show you this if you hit the name box it only says b1 because I'm in b1 I'm going to convert it to a table which is ctrl T now after you convert to a table you hit ok don't worry about the filter arrows if you want to get rid of them let's get rid of them for a second but here's what that did now when I click here that made it table 1 right there now once again I've got rep number I'm going to go to the orders that our customers put in and if you look over in column K I've got the rep numbers 0 1 through 0 4 depending on the customer and I do want to point one other item out I called it rep and I did this intentionally in k1 and back here it is rep number in a1 so I'm gonna go back here and I've got to do another table if you don't like ctrl T insert table but ctrl T is just easy hit ok and once again I'm gonna just turn off the filters so here we go now when I click the arrow I've got two tables I'm gonna go to the insert tab and make a pivot table new worksheet sounds perfect you have to check this box at the bottom or doesn't work click OK notice it says table 2 and if I collapse and expand it that's pretty cool I'm on the word act click the word all and it picked up the other table automatically so now what I need to do is I'm gonna expand them both I'm up here on pivot table tools analyze relationships new pick table to pick table one in case you're wondering which one's the primary key the first table that I made let me cancel this just for a second close back here that's your primary key I'm on the rep worksheet because rep one two three and four of us is just one time but yet over here one rep can have many customers so that's why this one's primary so let me go back to the pivot table now analyze relationships new I'm sorry about that I should have had that ready to be explained earlier so primary was rep number and I'm gonna match it to rep so I'm saying rep number in one worksheet is equal to reppin the other click OK click close I'm good to go so now what I can do I'm trying to figure out the total for every rep back in table one full name there my reps back in Table two it's going to be something numeric invoice amount perfect that is how you relate tables so step one step one was to make each range a table with control-t the next step is when you create your pivot table make sure you check this box here so after you make them tables you got to check that box hit OK and then from there you go to the word all and you set up your relationships so anyway that's how you join worksheets in Microsoft Excel if you need instructor-led training or you going to take my online training course just send me a message and I'll send you a coupon thank you

Channel: Chris Menard

Views: 304,364

Rating: 4.8270731 out of 5

Keywords: excel, pivottable, tables, multiple worksheets, pivot table, excel pivot table, excel multiple worksheets, combine worksheets into pivottable, excel worksheets and pivottables, create pivottable from worksheets, create pivot table in excel, create pivot table from multiple worksheets, create pivot table, excel pivot table tricks, excel multiple worksheets into one pivot table, excel multiple worksheets pivot table, create pivot table from multiple worksheets in excel 2016

Id: wdemm8LzzVY

Length: 4min 56sec (296 seconds)

Published: Thu May 25 2017