Using Secondary Rows

Contributed by: Dawn Griffiths

Difficulty level: Beginner

Problem

You have a PivotTable and want to use two or more fields for its rows.

Solution

Suppose you have an empty PivotTable based on a table of sales data with columns named Category, Product, Month, and Amount. You want to calculate the total amount for each category, product, and month.

You can solve this problem using secondary rows. You add secondary rows by following these steps:

1. Drag the Category field in the Fields section to the Rows section; then drag the Product field to the Rows section, placing it underneath the Category field. This adds secondary rows to the PivotTable so each Product value appears under the relevant Category.

2. Drag the Month field to the Columns section and the Amount field to the Values section; this calculates the sum of the amount values for each category, product, and month.

Figure 1 shows the resulting PivotTable and its PivotTable Fields pane.

Figure 1. Adding secondary rows to a PivotTable

Note

You can also add secondary rows by drilling down into the data. Double-click a row or column item in the PivotTable; then use the Show Detail dialog box to specify which data you want to display.

Discussion

Secondary rows let you slice, dice, and group your PivotTable data and include summary values for different combinations. You can also include secondary columns ...