Expanding PivotTable Rows to Sheets

by Allen Wyatt
(last updated August 6, 2015)

10

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.)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12198) applies to Microsoft Excel 2007 and 2010.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Conditional Processing During a Mail Merge

The Mail Merge capabilities can appear limited at first glance. One thing that is often overlooked (and which makes the ...

Discover More

Displaying the Document Map

One of the viewing modes you can use for a document involves the use of the Document Map. This shows a quick outline of your ...

Discover More

Maximum Length Limit for a Macro

Make your macros too long, and Excel may just refuse to run them at all. This tip explains what the limit is for macros, and ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Displaying a PivotTable's Name in the PivotTable

When you create a PivotTable, it can have a name. You may want this name to appear within the PivotTable itself. There is no ...

Discover More

Rows in a PivotTable

PivotTables are used to analyze huge amounts of data. The number of rows used in a PivotTable depends on the type of analysis ...

Discover More

Editing PivotTables without Underlying Data

If you ever try to edit a PivotTable and get an error that tells you that the "underlying data was not included," it can be ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 + 4?

2015-12-05 03:07:51

Vaman Shenoy

When I enter numeric value it gets automatically converted into date. Then I have to change it to number. Please help me resolve


2015-10-25 11:21:35

Vanessa

Is there a way of showing the calculated fields as a list beside the pivot table as opposed to it opening in a separate sheet when you double click on it.


2015-09-24 17:55:39

andrew

Can those new sheets be dynamically updated? I mad a change to the orginal data but even when I do a refresh on the pivot table, the data in those drill down sheets never updates.


2014-08-13 14:23:18

Deana

Is there a way to generate the worksheets sorted in a certain order?


2014-08-11 11:21:07

Chuck

Here is a link that explains how to create an Auto delete for the "drill down sheets.
http://www.contextures.com/excel-pivot-table-drilldown.html


2014-06-09 05:54:32

ALI

When we double click the Pivot Table Row it will create a new sheet showing all the data based on rowID. is it possible to name the Sheet created same as RowID.


2014-05-01 09:17:21

Surendera M. Bhanot

Good Information on Pivot Table. Would like to use it in future.


2014-04-30 13:54:27

Jignesh pandya

by double clicking data excel generates new worksheet after viewing data
i want to delete this sheet using keyboard


2013-05-23 14:33:49

Claude

I see that Excel creates new worksheets every time you double click on a value in a pivot table but it is annoying that you have to manually delete the new worksheet(s) manually if you don't need the additional information on a permanent basis.


2012-10-15 09:54:52

Terri

When I double click on a grand total, I don't get a worksheet for each row -- I get one worksheet that has all the rows on it. Am I reading you incorrectly?


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.