by Allen Wyatt
(last updated April 7, 2018)
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:
Figure 1. The Show Report Filter Pages dialog box.
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.)
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12198) applies to Microsoft Excel 2007, 2010, 2013, and 2016.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
When you create a PivotTable, it can have a name. You may want this name to appear within the PivotTable itself. There is ...Discover More
If you ever try to edit a PivotTable and get an error that tells you that the "underlying data was not included," it can ...Discover More
If you are using a data set that includes a number of zero values, you may not want those values to appear in a ...Discover More
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.