Expanding PivotTable Rows to Sheets

by Allen Wyatt
(last updated April 7, 2018)

2

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 Filters section of the table.
  2. Display either the Options or Analyze tab of the ribbon, depending on your version of Excel. (These tabs are 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. (This option is available only if you are filtering your PivotTable; see step 1.) Excel displays the Show Report Filter Pages dialog box. (See Figure 1.)
  4. Figure 1. The Show Report Filter Pages dialog box.

  5. Select the field you want to use for the data expansion.
  6. 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, 2010, 2013, 2016, 2019, and Excel in Office 365.

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

Intelligent Title Case

A common editorial need is to change the capitalization used on different words in a selection of text. Word provides a ...

Discover More

Pulling Filenames into a Worksheet

You can use Excel for all types of data processing. You may want to work with filenames in a worksheet, but the first ...

Discover More

Creating See-Through Text Boxes

When laying out your document, you may want to use a text box that appears to be positioned over your text, but to be ...

Discover More

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!

More ExcelTips (ribbon)

Pointing PivotTables to Different Data

Changing the data source PivotTables go to can be a bit tricky. This tip explains what can happen when you re-point your ...

Discover More

Default Formatting for PivotTables

Wish there was a way to define how you want PivotTables formatted before you actually create the PivotTable? You may be ...

Discover More

Suppressing Zero Values in PivotTables

PivotTables are great for digesting and analyzing huge amounts of data. But what if you want part of that data excluded, ...

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. Maximum image size is 6Mpixels. 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 4 + 0?

2019-04-07 13:50:16

Bo

This happened to me too! I accedentally created a very helpful table without knowing how I did it. Thank you for showing me what to do next time I need it.


2018-05-01 05:09:21

hillp

Excel 2010
almost to what im looking for, but not quite...i just want to list all raw data inside a separate sheet from the master pivot table.

the method that you share above just create another pivot table in every sheet....

think as pivot table to summarize data... and from that summary i want to generate individual sheet of raw data based on the category i analysed in the pivot table

any other option? VBA maybe?


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.