Create an Excel PivotTable Based on Multiple Worksheets

I recently got an excellent question from a student in the mind in one of my Microsoft Excel courses that I host on udemy take a look the question was hey I want to create a pivot table based on data across multiple worksheets open in front of you I have an example of this I've got four worksheets inside this workbook a worksheet about east records a worksheet about west north and south now very important to point out here each of these worksheets are practically identical they've got the same column headers region category and the three months the only difference is the data that's found within those lists one deals with East records another deals with West's and so on so this data I want to take it all and I want to merge it into one master list now in the past I've seen people copy and paste they'll take data from each of these four worksheets and paste them into what will consider the master lists creating one massive list that contains all the data and then basing your pivot table off of that well here's an alternative this is kind of a hidden feature found within the newer versions of Microsoft Excel now way back when 2003 and earlier of Microsoft Excel we used to have what's called the pivot table wizard it was like four steps that would walk you through the process of setting up your initial pivot table well when the 2007 edition came out that pivot table wizard went away but we still have access to it and because we still have access to it we can use multiple consolidated ranges as our pivot table source watch this so first step I'm gonna click into my east records here and on my keyboard I'm gonna run a shortcut key here I'm going to press alt D D like dog alt D this activates my office access keys now I can press a menu sequence that was used in the earlier versions of the office suite so now I'm gonna press the letter P P for a pivot table this opens up my pivot table and pivot chart wizard so now my first step is I want to get my data from multiple consolidated ranges I'm gonna create a pivot table I'll hit next now the page fields these are the filters the filter area within a pivot table I'm gonna say I want to create my own page fields I'm gonna hit next now I get to go grab all the ranges of data that I want to analyze in a pivot table so I'm gonna cook in a range I'm gonna come over here I'm gonna grab my east records I'm just gonna grab from b1 down to e 15 notice I'm leaving off the a column the region column that's the the identifier for this data and that's something I'm gonna use within the page field talk some more about that here in a moment so I'm gonna add that now I'm gonna continue doing this I'm gonna do it for West's I'll go grab the West data again leaving off the region I'll add that and I got to do it two more times here here comes the North at it and one more time for the South I'll add it now each of these again they're the same data same setup same columns just different records for different regions so now I need to identify that this right here east records is the East data and I'm gonna do this by using one of the page fields so down here I'm gonna activate a one page field and with east records selected I'm gonna label this as East there we have it then I'm gonna go through and do the same here here's north I'll label this one is the North records here comes the South records and then one more here's the West records so I've got my data sources I've got my page fields or the filters the filter part of the pivot table I'll hit next and here's my last step I just get to say where I want to create the PivotTable I'm gonna do a new worksheet so I'm gonna hit finish and I've now got a pivot table based on multiple consolidated ranges multiple worksheets worth the data here all consolidated into one pivot table so you can see here that they're giving me the row labels as my different categories for my different sheets there's my categories there and it's summing up the sales amounts for the three different months and I've got a grand total as well and the page fields happen up here there's my four let's expand that a little my four regions and if I select one I now got just the data for north here's the east and so on now that's all about consolidating multiple worksheets worth of data into one data source to be used within a pivot table remember the shortcut key alt D on your keyboard and then the letter P this will activate the pivot table wizard now here's one additional bonus that you're gonna get by watching this video take a look now remember we've got data our respective data on the four separate worksheets I want just one massive list one master list that contains all of this data well this is really quick with the pivot table I'm gonna come over here to my pivot table field and I'm gonna take out the three options first you know what I'm gonna get back to all my records here so I'm gonna take out the page fields I'm gonna take out the column I'm gonna take out the row so I'm left with just the grand total sales for all of the records across all four worksheets now here's a really neat one I'm gonna come over to this value I'm gonna give it a double click click click this creates a brand new worksheet and it's now the master list for all of the data every region every sale every month every category across all four the earlier worksheets now they gave me some default column headers their row column value and page I'm just gonna rename those this should be category let's make sure I spell that right category this should be month this should be sales and this should be region I've now got the master list of all of that data and I've got my pivot table try this out find your data that's across multiple worksheets use that shortcut key alt D plus the letter P open up the pivot table wizard and off you go

Channel: OfficeNewb.com

Views: 392,443

Rating: 4.8499451 out of 5

Keywords:

Id: r0wQ3DmDOvU

Length: 7min 16sec (436 seconds)

Published: Wed Oct 26 2016