• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 11. Working with Pivot Tables > Pivoting a Pivot Table

Pivoting a Pivot Table

The key to working with pivot tables is knowing how to change the way the table groups your data so it displays your data the way you want it to. These changes are called pivots.

To pivot a pivot table:

1.
Display the forms in your database and double-click the form containing the pivot table you want to work with.

In this example, we're using the Sales Analysis form from the Northwind sample database.

2.
Click the Edit Pivot Table button at the bottom of the window.

Excel launches and opens your pivot table as an Excel document (Figure 11.12).

Figure 11.12. Arrange the fields to display your data effectively.


The Sales Analysis pivot table draws on five fields to provide its data and groupings: LastName (of the employee that made the sale), Years, Subtotal (of a specific order), OrderDate, and ShipCountry (the destination country for the order). In Figure 11.12, the Subtotal field provides the data for the body of the table, the Years field groups the values in the rows, and the LastName and OrderDate fields (in Quarters), group the values in the columns. The ShipCountry field's header resides in the Page area.

3.
Click the LastName field header and drag it to the left of the Years field header (Figure 11.13).

Figure 11.13. Move a field name to change how the pivot table displays your data.


The pivot table appears with the data rows grouped by LastName, then by Years (Figure 11.14). The table now displays yearly sales data for each employee.

Figure 11.14. Your table, changed to reflect the pivot.


4.
Drag the OrderDate field header between the LastName and Years field headers.

The data rows are now grouped by LastName, then OrderDate, and then Years (Figure 11.15). This arrangement emphasizes which quarter of the year the sales took place.

Figure 11.15. This arrangement focuses on quarterly sales.


5.
Drag the OrderDate field header to the right of the ShipCountry field header in the Page area.

The pivot table appears with the data rows grouped by LastName and Years (Figure 11.16). This arrangement emphasizes yearly sales without considering quarterly fluctuations.

Figure 11.16. This arrangement focuses on yearly sales.


6.
Drag the ShipCountry field header from the Page area to the right of the Years field header.

The pivot table appears with the ShipCountry fields in the Rows area (Figure 11.17).

Figure 11.17. This arrangement breaks out sales by country.


7.
Drag the ShipCountry field header onto the Total header (Figure 11.18).

Figure 11.18. Move the Ship Country field header over the Total header…


When you drag a field header onto the Total header, the values in the field you moved (in this case, the ShipCountry field) modify how data is displayed in the pivot table's body (Figure 11.19).

Figure 11.19. …to display the countries to which sales were made.



PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint