How to Create Pivot Table Data Model in Excel 2013

So far, we have focused exclusively on pivot tables that are generated from a single table
of data. Data Model feature has brought additional advantages to pivot charts. With
the Data Model, we can use multiple tables of data in a single pivot table. We have to
create one or more "table relationships", so the data can be tied together.

New Feature
The Data Model is a new feature in Excel 2013

Creating a pivot table using Data Model

The following figure shows parts of three tables that are in a single workbook. Each
worksheet is in the same workbook, just shown in the separate window. The worksheets
are Orders, Customers, and Regions. Every worksheet has a table and I have also
named these 3 tables as Orders, Customers, and Regions (to keep it organized).
The Orders table contains information about product orders. The Customers table
contains information about the company’s customers and the Regions table contains
regional information.

Using these three tables we shall create a pivot table, using the Data Model.

1
How to Create Pivot Table Data Model in Excel 2013
If you look at the tables closely, you will find that the Orders and Customers tables
have the CustomerID column as common, and the Customers and Regions tables
have the State column as common (download the working file to see the
common things). We shall use these common columns to form relationship among
the tables.

One to Many Relationships

Notice that the relationships between the tables are "one-to-many". For every row in
the Orders table, there is exactly one corresponding row in the Customers table, and
that row is determined by the CustomerID column. Similarly, for every row in
the Customers table, there is exactly one corresponding row in the Regions table, and
that row is determined by the State column.

Note
A pivot table created using the Data Model has some restrictions, when compared with a
pivot table created from a single data table. Most notable one is: you can’t
create groups. In addition, you can’t create calculated fields or calculated items.

Our goal in this example is to summarize sales by state, by region, and by year. Notice
that the sales and date information is in the Order table, the state information is in
the Customers table, and the region names are in the Regions table. Therefore, we
shall use all these three tables to create our target pivot table.

Here is the step by step process we have used to create the pivot table:

Step 1

Select any cell within the Orders table and choose Insert ➪ Tables ➪ Pivot Tables.
The Create PivotTable dialog box will appear.

Step 2

Choose the data that you want to analyze and Choose where you want the
PivotTable report to be placed - these two options will be as it is. Select the Add
This Data to the Data Model check box and click OK.

2
How to Create Pivot Table Data Model in Excel 2013

Select the Add This Data to the Data Model check box and click OK.

If you look at the PivotTable Fields task pane (on the right side of the newly created
worksheet), you will find that it is a bit different as this time we've selected to work
with Data Model. The task pane contains two tabs: Active and All. The Active tab
lists only the Orders table and the All tab lists all the tables in the workbook. You can
take any table under the All tab to the Active tab. To take the Customers table under
the Active tab, activate the All tab, right-click the Customers table, and choose Show
in Active Tab from the options. Then do the same for the Regions table.

The following figure shows the Active tab of the PivotTable Fields task pane.
Customers and Regions tables are expanded to show their column headers (field
names). I have also changed the configuration of the task pane (task pane layout). To get
this change, click on the Tools control and from the drop-down menu, I've
chosen Fields Section and Areas Section Side-by-Side.

3
How to Create Pivot Table Data Model in Excel 2013

The PivotTable Fields task pane, with three active tables under the Active tab.

Step 3

Now we are going to set up the relationships among the tables. Choose PivotTable
Tools ➪ Analyze ➪ Calculations ➪ Relationships. The Manage
Relationships dialog box will appear.

4
How to Create Pivot Table Data Model in Excel 2013

Manage Relationships dialog box.

Step 4

Click the New button. The Create Relationship dialog box will appear.

Step 5

In the Table drop down, select Orders, and in the Column

(Foreign), select CustomerID; in the Related Table, select Customers and in
the Related Column (Primary), select CustomerID.

Creating a relationship between two tables. Now Orders table's customerID is related to
Customer table's customerID.

5
How to Create Pivot Table Data Model in Excel 2013

Step 6

Click OK and you will be returned to the Manage Relationships dialog box.

Step 7

Click New again. Now we are going to create a relationship between

the Customers table and the Regions table like the following figure.

Creating a relationship between Customers table and Regions table.

The Manage Relationships dialog box will now show these two relationships.

6
How to Create Pivot Table Data Model in Excel 2013

Manage relationships dialog box now showing two relationships.

Note
If you forget to set up the table relationships in advance, Excel will prompt you to do
so when you will try to add a field to the pivot table from a different data table.

Step 8

We have established the table relationship. Now just drag the field names to the
appropriate areas of the PivotTable Fields task pane:
■ Drag the Total field to the Values area.
■ Drag the Year field to the Columns area.
■ Drag the Region field to the Rows area.
■ Drag the StateName field to the Rows area.

The following figure shows part of the pivot table. I added two slicers (MailList, and
Product) to enable filtering the pivot table by customers who are on the mailing list, and
by product.

7
How to Create Pivot Table Data Model in Excel 2013

The pivot table, after adding two slicers to filter the pivot table.

Tip
You can convert the pivot table to formulas. To do this, select any cell in the pivot table
and choose PivotTable Tools ➪ Analyze ➪ OLAP Tools ➪ Convert to
Formulas. The pivot table will be replaced by cells that use formulas. These formulas
are generated with CUBEMEMBER and CUBEVALUE functions. Although the new
range of data will no longer be a pivot table, the formulas will update when the data
changes.

Download this sample file to practice yourself

data-model.xlsx

Happy Excelling :)