Excel 2019 Bible

Chapter 36: Working Directly with the Internal Data Model

36

At this point, you can click the newly created PivotTable to activate the PivotTable

Fields list, if necessary. Choose the All selector, as shown in Figure 36.7. This shows

all available tables in the Fields list.

FIGURE 36.7

Select All in the PivotTable Fields list to see both tables in your internal data model.

5. Build out your PivotTable as normal. In this case, Job_Title goes to the Rows
   area and Sales_Amount goes to the Values area.
   As you can see in Figure 36.8, Excel immediately recognizes that you are using
   two tables from your internal data model and prompts you to create a relationship
   between them. You have the option of letting Excel auto-detect the relationships
   between your tables, or you can click the Create button. It’s always best to create
   the relationships yourself to avoid any possibility of Excel getting it wrong. Click
   the Create button.


6. Excel activates the Create Relationship dialog box shown in Figure 36.9. Here
   you select the tables and fields that define the relationship. In Figure 36.9, you
   can see that the Transactions table has a Sales_Rep field. It is related to the
   Employees table via the Employee_Number field.

After you create your relationship, you have a single PivotTable that effectively uses

data from both tables to create the analysis you need. Figure 36.10 illustrates the final

PivotTable after number formatting has been applied to the sum of the sales amount.