Excel 2019 Bible

Chapter 30: Analyzing Data with PivotTables

3030

5. Relate the Customers table to the Regions table. Click the New button in the
   Manage Relationships dialog box and create a relationship similar to how you did it
   in the previous step (see Figure 30.33). Notice how the Customers table is now iden-
   tified as being part of the data model. When you created the relationship in the
   previous step, you added the Customers table to the data model.

FIGURE 30.33

Relating the Customers and Regions tables by state

6. Build the PivotTable. After you’ve closed the Manage Relationships dialog box,
   you’re ready to build the PivotTable. Move the Region and StateName fields to the
   Rows area, move the Year field to the Columns area, and move the Total field to the
   Values area. Figure 30.34 shows the PivotTable after this step. (Some of the data in
   Figure 30.34 was collapsed so that more of the PivotTable can be seen.)

The PivotTable based on three tables is complete. All that remains is some formatting. You

can format your PivotTable to suit your taste. To make your PivotTable look like the one in

data model complete.xlsx, follow this steps:

1. Add the slicers. Choose PivotTable Tools Analyze ➪ Filter ➪ Insert Slicer, select
   the All tab, and select Product and MailList.


2. Format the slicers. Right-click the MailList slicer and choose Size and Properties.
   In the Position and Layout section of the Format Slicer pane, change the Number of
   Columns property to 2.


3. Format the values. Right-click any value in the PivotTable and choose Number
   Format. Choose Number, set Decimal Places to 2, and select the Use 1000 Separator
   check box.


4. Add region subtotals. Right-click any Region item (such as Central) and choose
   Field Settings. On the Subtotals and Filters tab, choose Automatic.