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 1. 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 2 here. So, I'm curious which method would apply to your case.

So comment  below and let me know, method 1 or method 2. And also, if you aren't subscribed to this channel,  do subscribe because you're going to get better in Excel, as long as you watch the videos. Okay, so  let's jump in and I'm going to show you both versions.

Let's start with method 1. Here, I want to combine  the data from these two different tabs. Take a look at this, the first column here for Store 1  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 Control-T.

My table has headers, I'm going to go with OK. Let's go ahead and remove the table formatting  and give this a better name, I'll call it "TableStore1". 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  "TableStore2". 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 2, I'm going to go select "From Sheet" and this is going to send it to Power Query. The name is  automatically taken from the table.

This looks fine. I'm just going to 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 changed type step.  Remember that my columns were in a different order, but because the changed 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 1". 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 & Load",  "Close & Load to", "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 TableStore1.

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 "AllStores".

Take a look at the data types. They all look good. "Close & Load", "Close & Load To". 

Now this time, I'm going to create a pivot table directly on this. I'm going to go with  PivotTable 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 Query.

So, I'm  just going to double-click and go to the TableStore2 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 & 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 1 and Store 2, or I can take a look at everything in one go. Now, let's take a  look at method 2. So, in this second example, this is my dataset.

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 VLOOKUP's or now, let's say XLOOKUP's 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  step, 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 "TableData.

" 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 "TableMaster". 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 "TableData". The related table is our "TableMaster".  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 ProductCode, and for Related Column, we are going  to go with ProductCode 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 going to 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, PivotTable 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. TableData and TableMaster 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 2. 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 going to see you in the next video.