If you are using Excel 2013 or a newer version you can create PivotTable from data that are coming from several different worksheets. All the tools you need are integrated with Excel.
In earlier Excel versions, you had to use PowerPivot to achieve the same result.
In this tutorial, we have three sheets: Customer, Order, and Payment.
All the necessary data are split between them. We have to combine them using a tool that comes with PivotTable.
Each of these tables has a column Order_id with unique values.
Creating and naming tables
First, let’s create a table from each of them. Click the data inside the first sheet (Customer) and navigate to Insert >> Tables >> Table or use Ctrl + T.
Create tables on other sheets (Order and Payment).
Click inside each table and navigate to Table Tools >> Design >> Properties and change their names to Customer, Order, and Payment accordingly.
It’s time to insert a Pivot Table. Click the first Table and navigate to Insert >> Table >> PivotTable. Normally you would click OK and start working with a PivotTable.
But this time check the checkbox Add this data to the Data Model in order to work with multiple tables.
Rename the new sheet to PivotTable.
Now, if you look at the PivotTable Fields, you will notice that there are additional buttons there: Active and All.
Click All to display all tables.
We can’t use these tables yet, because Excel doesn’t know what are the relations between them.
Al columns are related to each other with the Order_id column, which is the same in all three tables.
Click the PivotTable and navigate to PivotTable Tools >> Calculations >> Relationships.
Click it to open the Manage Relationships window.
Inside this window, we are going to create two relationships. Click the New button to create the first relationship.
The first one is going to be Payment to Order. Remember that these relationships are identified by the Order_id. Create the relationship as you can see in the image below and click OK.
The second relationship is between Payment and Customer.
We don’t have to create a relationship between Order and Customer because there is an implicit relation through the Payment table.
All the relations that we create are inside the Manage Relations window.
Click the Close button.
You won’t notice any difference inside the PivotTables Fields, but all the work was behind the scenes. Without relations, the pivot wouldn’t behave the way we would like.
Let’s take a look at the fields of our table. We don’t need Order_id anymore. It was necessary for relations between the tables and now it’s not important anymore.
What we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.
Drag table fields into Drag fields as shown in the image below.
Your PivotTable should look like this.
Excel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets into the same PivotTable.
Just remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.