Combining Multiple Tables in a PivotTable using the Data Model

Data Model:

It’s a cut down version of Power Pivot which still available in Excel 2010 as add-in. Power pivot allows you to combine multiple tables in a pivot form. Data Model is the new feature added in Excel 2013.

Let’s find out how to use the Data Model to combine multiple tables in a Pivot Table.

Below are the two data sets in two different worksheets fist one is Regional details by 'customer id' and in another data contains 'order details' but it doesn’t have regional information. So by using Data model we can combine both data and create a single pivot table.

First data set: Regional details by customer id

Second data set: Order details by customer id

Before creating Pivot we need to convert rages to Excel tables

Converting ranges to excel tables:

To convert ranges to excel tables follow below steps:

Select the data go to the Insert tab and select Table, as shown in below check the range and the “My table has headers” option is checked the click on ok to convert range to table

Then change the name of table it’s not mandatory but it will help to identify table in a pivot.

Select design tab and change the table name to “Regional_details”.

Follow the same steps for other data and give the table name as “Order_details”

Next step is to add tables in the data module. Add one table into that and other will get automatically added in the pivot.

To add a table in data module, select any cell from any table and INSERT pivot, as mentioned below 3 steps need to follow.

1. Select any cell from any table.
2. Insert pivot table.
3. The important steps it to check the “Add this data to the Data Module” option.

Click ok to confirm

Once the pivot table appears in new worksheet you will see two options under ‘Pivot Table Fields’
‘ACTIVE’ & ‘ALL’. Click on the ‘ALL’ and now all tables will appear 

Before selecting any column from both tables last thing to do is establish a relationship between the tables.

Even if both tables were added in a pivot table there is no relation in between them to create a relationship in between both tables. In both tables there is a unique column named “Customer ID” which will help to get all details by region using data from both tables.

Click on ANALYZE --> Relationship and then click on --> New

Then below window will appear in which select both tables.

And then select the column, which containing unique data

And click on OK to confirm

Now you can get amount details by region

Now you can add multiple tables in a pivot table but there few limitation as below

1. You cannot get data on double click as you usually get from the normal pivot table.
2. You cannot add calculated fields or Items.
3. You cannot apply the group function.

Then why and when we should use Data Module option:

It’s a substitute of VLOOKUP, the Data Module will combine data from multiple tables by using common field which is nothing but VLOOKUP. So if there are many tables with lots of fields and records then Data Module is good option….

Hope it helps you…