How to Create Pivot Table Data Model in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

So far, we have focused exclusively on pivot tables that are generated from a single table of data on this site. The 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. The Data Model is a new feature introduced in Excel 2013. In this article, we will show you 6 steps to create a pivot table Data Model in Excel.

The following figure shows parts of three tables that are in a single workbook. Each worksheet is in the same workbook, just shown in a separate window. The worksheets are Orders, Customers, and Regions. Every worksheet has a table, and we have also named these 3 tables 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.

6 Step-by-Step Procedures to Create a Pivot Table Data Model in Excel

If you look at the tables closely, you will find that the Orders and Customers tables have the CustomerID column in common, and the Customers and Regions tables have the State column in common (download the workbook to see the common things). We shall use these common columns to form relationships 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.

 💡 Notes: A pivot table created using the Data Model has some restrictions when compared with a pivot table created from a single data table. The most notable one is: that 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: Inserting PivotTable

For the first step, we will insert a pivot table from the dataset. Select any cell within the Orders table and choose Insert PivotTable From Table/Range. The Create PivotTable dialog box will appear.

  • Firstly, we have selected the cell range C5 in the Orders sheet.
  • Secondly, from the Insert tab PivotTable select From Table/Range.

Inserting PivotTable to Create Pivot Table Data Model


Step 2: Adding Data to Data Model

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

  • So, a dialog box will pop up.
  • Then, select “Add this data to the Data Model”.
  • Afterward, press OK.

Adding Data to Data Model

  • 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 place 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.
  • This is totally optional, later we’ll select the fields from the All tab.
  • Next, 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). We 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.


Step 3: Managing Relationships

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.

  • Then, select anywhere inside the new pivot table.
  • After that, from the PivotTable Analyze tab, select Relationships.

Managing Relationships to Create Pivot Table Data Model

  • Therefore, another dialog box will appear.
  • Next, click on New.


Step 4: Creating Relationship

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.

  • Then, another dialog box will pop up.

Creating Relationship

  • After that, select the following things:
    • Table: Data Model Table: Orders
    • Related Table: Worksheet Table: Customers
    • Column (Foreign): CustomerID
    • Related Column (Primary): CustomerID

  • Next, click OK and you will be returned to the Manage Relationships dialog box.
  • After that, click New again. Now we are going to create a relationship between the Customers table and the Regions table, like the following figure.
    • Table: Data Model Table: Customers
    • Related Table: Worksheet Table: Regions
    • Column (Foreign): State
    • Related Column (Primary): State
  • Then, press OK.

  • So, the Manage Relationships dialog box will now show these two relationships.

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


Step 5: Inserting PivotTable Fields to Areas

We have established a table relationship. Now just drag the field names to the appropriate areas of the PivotTable Fields task pane:

  • Firstly, drag the Total field to the Values area.
  • Secondly, move the Year field to the Columns area.
  • Thirdly, drag the Region field to the Rows area.
  • Fourthly, move the StateName field to the Rows area.

Inserting PivotTable Fields to Areas

  • Then, the following figure shows part of the pivot table.


Step 6: Adding Slicers

In this final step, we added two slicers (MailList and Product) to enable filtering of the pivot table by customers who are on the mailing list and by-products.

  • To begin with, select anywhere inside the pivot table.
  • Then, from the PivotTable Analyze, select Insert Slicers.
  • Afterward, from the All tab, select the following things:
    • Customers: MaiList.
    • Orders: Product.
  • Then, press OK.

Adding Slicers to Enhance Pivot Table Data Model

  • Finally, after doing that, the final pivot table will look like this.

 💡Tips: 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 the 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 Practice Workbook


Conclusion

We have shown you 6 easy steps to create a pivot table Data Model in Excel. If you have any questions, feel free to comment below. Happy Excelling :).


How to Create Pivot Table Data Model in Excel: Knowledge Hub


<< Go Back to Pivot Table in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

1 Comment

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo