Create a Pivot Table from Multiple Sheets in Excel | Comprehensive Tutorial!

sometimes you want to create a pivot table in excel that's based on multiple sheets but there are two ways you can do this depending on your situation so your scenario might be this you have multiple sheets with the same columns each sheet is for a different category for example we have similar data here but for different stores i need to combine this data before i create a pivot table and i don't want to use copy and paste because it's annoying and my columns can have a different order if that's your case you're going to need method one another case is that you have details about a column in a separate tab so for example here we have our sales data which include product codes but we don't have the product name the name of the product and more details about the product is in a separate sheet here i'd have to use a lookup function to get everything in one table before i create my pivot table but that's not the right way to do this you're going to need method two here so i'm curious which method would apply to your case so comment below and let me know method one or method two and also if you aren't subscribed to this channel do subscribe because you're gonna get better in excel as long as you watch the videos okay so let's jump in and i'm gonna show you both versions let's start with method one here i want to combine the data from these two different tabs take a look at this the first column here for store one is transaction number and the last column is sales amount for store 2 i have similar type of information except i have it in another order my first column is date my last column is quantity the column headers though are identical they're just in different places i want to create a pivot table that's based on the combination of this information first thing i'm going to do is to turn each of these into an official excel table so just click somewhere inside the table and press ctrl t my table has headers i'm going to go with okay let's go ahead and remove the table formatting and give this a better name i'll call it table store one let's go ahead and do the same thing for store 2. instead of using the shortcut key you can also go to the insert tab and select table table has headers let's remove the formatting to go back to the original formatting and call this table store 2. next step is to combine the data now here i don't want to use formulas instead i'm going to go to the data tab get and transform data select from sheet so currently i'm in data for store two i'm gonna go select from sheet and this is gonna send it to power query the name is automatically taken from the table this looks fine i'm just gonna update the data type here for date and just go with date instead of date and time this looks good the department is text decimal number and a whole number here so that's fine now i can go ahead and create a connection to this and then do the same for the first table but take a look at this when i go back to the source tab i can already see the code that it's used this is my table name this means that i can also just right mouse click and duplicate this query and go back to the source step in the duplicate query and change this to store 1 and press enter now the second step is a change type step remember that my columns were in a different order but because the change type step refers to the column names if i had different names i would get an error in this stage so it's also a good way of checking whether your columns have the same headers everything looks good i'm just going to update the name of this to say store one next step is to append these together but before i do that i just want to go and create a connection to these two tables so let's go close and load close and load two only create a connection because we don't want to load another table that has the same information to our sheet so we just want to create a connection and click on ok we can see the connections right here let's just double click on any of these to open the power query editor and now we can append these together in a separate query go to combine append queries and append queries as new this is going to create a new query for the appended version first table is table store one second table is this one if you have three or more tables go to this option in our case we just have two click on ok this is our final appended version let's rename this to all stores take a look at the data types they all look good close and load close and load two now this time i'm going to create a pivot table directly on this i'm going to go with pivot table report i want this on a new sheet click on ok i have my pivot table right here i can take a look at the quantity sales amount by the different departments now in case i also want to have the store number in my data so i can filter by the different stores i can add the information to my sheet here or i can add it directly in power queries i'm just going to double click and go to the table store 2 query let's just go ahead and add a column to this i'm going to add a custom column type in store and let's put in store 2 click on ok now i have store 2 everywhere here i'm going to change this to a text column we can do the same for store 1 add column custom column store so just make sure you give it the identical header name so we can append them properly and this is going to be store 1 and click on ok and change this to a text data type now we can double check if everything came over properly i have store 1 and on the bottom i have store 2. i can go ahead and close and load this to my pivot table directly here let's right mouse click and refresh and i see the store information in here so i have store one and store two or i can take a look at everything in one go now let's take a look at method two so in this second example this is my data set i have transaction number date product code quantity and sales amount now for product code i have a separate master data here that has a lot more information about this product what i want to do is combine this information together so that i can create a single pivot table out of this now the old way of doing this is to write a bunch of vlookups or now let's say x lookups to get this information in one big table and then create a table out of this one but don't do this instead follow these steps well first snap let's create a table out of these in case they're not a table already this table has headers let's go through the motion remove the table style let's call this one table data go to the master data tab this one is also not a table so i'm going to quickly convert it into one and call this table master now before we create a pivot table out of this we are going to connect these together one way of doing this is by going to the data tab to the data tools section here and then click on relationships we are going to create a relationship between the two tables select new pick the tables and columns you want to use for this relationship so for the first table we are going to go with this one which was called table data the related table is our table master now we need to think about how they are related well they are related by this product code here so for column foreign i'm going to select product code and for related column we are going to go with product code as well they can have different names it just happens that they have the same name in this case once you've set up this connection click on ok and let's close this it looks like nothing's happened but something has happened in the background first of all these two tables have been added to the data model and a relationship has been created between them now in case you're curious how this looks you can go to the data model here so if you click on this power pivot window it's going to open up i can see two different tabs for my tables and if i go to diagram view here i'm gonna see a relationship between them you didn't have to do anything here all of this happened in the background the moment you defined these relationships now let's go ahead and insert our pivot table we're going to go to insert pivot table and we want this from the data model you can get to it directly by clicking on this down arrow key and then selecting from data model now this might look a bit different to your version i'm showing this on office 365 and it got an update to look like this you might have to click on your pivot table and then select from data model in the dialog box in this case i don't have to i'm just going to click from data model it's asking me if i want it on a new sheet or existing worksheet let's go with a new sheet and now we can set up our pivot table so these are the different tables we have in place table data and table master is what i need i can see it in the all section here you can also just right mouse click and show it in the active tab so you can separate these tables to the other tables that you have in your workbook now let's go to active and we just have these two i want to get the total quantity and the total sales amount and now i can take a look at the different departments here add another row for category and so on okay so this was method two by creating a relationship between the two tables we were easily able to create this pivot table we didn't have to write any formulas i hope you found these two methods useful do hit that thumbs up and i'm gonna see you in the next video

Channel: Leila Gharani

Views: 221,773

Rating: 4.9721122 out of 5

Keywords: XelplusVis, Leila Gharani, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, Microsoft Excel tutorials, Microsoft Excel, Excel 2016, Excel 2013, Excel 2019, XelPlus, Microsoft 365, Excel 365, pivottable, pivot table, excel pivot, pvt table, pivot tables beginners, excel merge data, excel combine data, excel append data, power query, power pivot, excel relationships, data model

Id: Yv7QBZXEDDc

Length: 11min 1sec (661 seconds)

Published: Thu Jul 01 2021