Programming Excel with VBA and .NET

Change the Layout

The process of dragging fields from the Field List onto the pivot table as shown in Figure 13-3 is called setting the pivot table layout. You can change that layout by dragging fields to different locations on the pivot table. Figure 13-10 shows the areas to which you can drag fields.

Tip

Excel doesn’t call these parts of the pivot table areas. It just refers to fields in those locations as page fields, column fields, row fields, or data fields.

Figure 13-10. Setting pivot table layout

Each of the fields in the PivotTable Field List corresponds to a column in the data source. It’s difficult to discuss the effect of dragging fields to the different areas of a pivot table; it is easier to just show you as I did earlier. However, I left out one area: use the page area to create individual views for each item in a field.

For example, if you drag ProductName in Figure 13-7 to the page area, you get a report for a single book, as shown in Figure 13-11.

If you drag ProductName from the legend of the pivot chart in Figure 13-8 to the page area, Excel charts each book separately. Use page fields to summarize large amounts of data—they are kind of overkill for this example, however.