Expanding PivotTable Rows to Sheets

Aziz once accidently pressed something while working with a PivotTable, which then expanded every row in the PivotTable to its own sheet. After he got over the surprise, he found that this was very helpful in examining his data. Problem is, he cannot figure out what it was that he accidently pressed so that he can again do this on other PivotTables.

There are actually a couple of ways to expand the data in a PivotTable, but none of them involve keyboard shortcuts. One way to expand the data is to double-click on a value in the PivotTable. Excel creates a new worksheet that provides detail for the value on which you clicked. For example, if you double-click on a value in a row of the PivotTable, then Excel creates a worksheet that contains all the values that make up that original row value. (Remember that PivotTables are used to consolidate information; they provide totals of information, so double-clicking “expands” that total so you can see what makes it up.)

Also, if you double-click on a grand total in a PivotTable, Excel creates worksheets for each row that is included in that grand total. (This sounds the closest to what happened with Aziz.)

There is another way to expand information in a PivotTable, through the use of Report Filter pages. When you create a PivotTable, if you put a field within the Report Filters section of the PivotTable, then you can later expand information to multiple worksheets based on that field. Follow these steps:

  1. Create your PivotTable as you normally would, but make sure there is at least one field in the Report Filter section of the table.
  2. Display the Options tab of the ribbon. (This tab is only visible when you select a cell within your PivotTable.)
  3. In the PivotTable group, at the left of the ribbon, click the Options drop-down list and choose Show Report Filter Pages. Excel displays a dialog box that lists all the fields in the Report Filter section of the PivotTable.
  4. Select the field you want to use for the data expansion and then click on OK.

At this point, Excel creates separate PivotTable worksheets for each of the discrete values in the field you selected in step 4. (For some data sets, this can be quite a few worksheets.)