MrExcel.com - Your One Stop for Excel Tips & Solutions

Pivot from Multiple Tables Using The Data Model

March 02, 2023 - by

Pivot from Multiple Tables Using The Data Model

Problem: I have a lot of rows in a data table and then some lookup tables. Is there a faster way to create a report than building a bunch of VLOOKUP formulas?

Strategy: Excel 2013 added a pivot table feature called the Data Model. This will solve the problem. This will work in all versions of Excel 2013, whether you have the Power Pivot tab installed or not.

The process works much better if you declare all of your data sets to be Ctrl+T tables first.

  • 1. Select one cell in your main data set and press Ctrl+T. Confirm that the data has headers and click OK.

  • 2. In the Table Tools Design tab, you will see a name for the table. Type a meaningful name, something like Data.

  • 3. Select one cell in your lookup data. Ctrl+T. OK. Rename this table Sectors.

  • 4. Go back to the original data. Select one cell. Insert, Pivot Table.

  • 5. In the Create Pivot Table dialog, check the box for Add This Data to the Data Model. Click OK.

While creating the pivot table, choose the box for Add This Data To The Data Model. This is near the bottom of the Insert Pivot Table dialog.
Figure 1044. Checking this box opens a wide variety of new options.

It seems like a really innocuous box, but by checking that box in Excel, you are loading the data into the Power Pivot data model that is hidden behind Excel. If this is the first time you’ve used the Data Model in this Excel session, you might notice a few-second delay before you get to the Pivot Table Field list.

At this point, everything feels like a regular pivot table with one small change. You will notice a line at the top of the Pivot Table Field List offering ACTIVE | ALL.

Two choices at the top of the Pivot Table Fields pane offer "Active" or "All".
Figure 1045. Before you move on to All, create a relationship.

  • 6. Go to the Analyze tab in the ribbon. Choose Relationships. Click New...

  • 7. There are four fields to fill out in the Create Relationship dialog. Start from your Data table and choose the key field used to link to the lookup table. For the Related table, choose the lookup table and the key field. Click OK.

The first Pivot Table tab in the Ribbon offers a Relationships icon. In the Create Relationship dialog box, specify Table = Date, Column = Customer. Related Table = Sectors. Related Column = Customer. This replaces a lot of VLOOKUP formulas.
Figure 1046. This is far easier than a VLOOKUP.

  • 8. You can now go to the ALL section of the Pivot Table Fields.

  • 9. You can now expand each table and choose fields from that table.

When you choose All from the PivotTable Fields list, you can choose fields from either the Data table or the Sectors table. Here, the Sector field is chosen from the Sectors table.
Figure 1047. Choose fields from any table.

The result is a pivot table with fields from Sheet1 and Sheet2.

The pivot table shows Sector & Customer in the Rows area and Sum of Revenue in the values area.
Figure 1048. The data model is an amazing improvement.

Joining two tables in a pivot table is an amazing improvement. Although Excel calls this the Data Model, it is really the Power Pivot Engine. Even if you don’t have two tables to join, there are some interesting reasons to run the data through the Data Model. See January Actuals and February Plan later in this chapter.

This article is an excerpt from Power Excel With MrExcel

Title photo by Mathew Schwartz on Unsplash