Microsoft Office Tutorials and References
In Depth Information
Figure 18-49: The PivotTable Fields task pane, with three active tables.
The next step is to set up the relationships among the tables. Choose PivotTable Tools ⇒ Analyze ⇒ Calculation-
s ⇒ Relationships. Excel displays its Manage Relationships dialog box. Click the New button, and the Create
Relationship dialog box appears.
For the Table, specify Orders; for the Foreign Column, specify CustomerID. For the Related Table, specify Cus-
tomers; for the Related Column (Primary), specify CustomerID (see Figure 18-50).
Figure 18-50: Creating a relationship between two tables.
Click OK to return to the Manage Relationships dialog box. Click New again and set up a relationship between
the Customers table and the Regions table. Both will use the State column. The Manage Relationships dialog
box will now show two relationships.
If you don't set up the table relationships in advance, Excel will prompt you to do so
when you add a field to the pivot table that's from a different table than you started
Now it's simply a matter of dragging the field names to the appropriate section of the PivotTable Fields task
1. Drag the Total field to the Values area.
2. Drag the Year field to the Columns area.
3. Drag the Region field to the Rows area.
4. Drag the StateName field to the Rows area.
Figure 18-51 shows part of the pivot table. I added two slicers to enable filtering the table by customers who are
on the mailing list, and by product.