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 the 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 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 PivotTable. 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 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 on 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 sales split between customers grouped by states and product types in different product categories.
Drag tables fields into Drag fields are as shown on the image below.
Your PivotTable should look like this.
Excel 2013 and later version have this powerful feature that was reserved only to PowerPivot users in the previous versions, where you can pick different fields from different worksheets into the same PivotTable.
Just remember that there always have to be some relation between these tables, otherwise, Excel is going to return strange results.