Hide and Show Details in Excel Pivot Tables

I continue to explore the many great features of Excel Pivot Tables. In this video lesson, I show you how to place fields in the PAGE area of the template; how to hide details in order to see the big picture and to reveal the hidden details in various formats. You will also learn how to automatically generate multiple pivot table reports!

Watch This Video on YouTube

Here is the link to watch this video tutorial on YouTube .

 Here are the steps to follow in this Excel video lesson:

  1. For this lesson I am moving the data sheet to a new workbook. Select Edit, Move or Copy Sheet (Click the “Copy” check box) and select “To a new workbook.
  2. Create the Pivot Table (Data, Pivot Table Report). In step 3, choose the “Layout” tab.
  3. On the Template, move “Territory” to the “Page” area; “Sales Reps” to the “Row” area; and move “YTD Sales” to the “Data” area twice. Click OK and then Finish.
  4. Rename the new worksheet – e.g. Pivot Table.
  5. Format the Numbers in the 1st Sum of YTD Sales Field – Right Mouse Click, choose Field Settings and the Number Tab.
  6. For the 2nd Sum of YTD Sales, Right Mouse Click, Field Settings and then “Options.” In the Options tab select “% of Total” from the drop-down “Show Data as:” box.
  7. Filter the Territories in the “Page” area.
  8. Move the Territories from the “Page” area to the “Row area.” You now have 2 Row Fields. Territory is the “Outer Row” and Sales Rep is “nested” as the “Inner Row.”
  9. Experiment with “Show Details” and “Hide Details” on the Pivot Table Toolbar.
  10. To create individual worksheets for the territories: Move Territory back to the Page area. From the drop-down options on the Tool Bar, select Show Pages.

NEW! My DVD Training Series, “The 50 Best Tips … ” is available for sale at the online store for The Company Rocks

Learn How to Quickly Create Pivot Tables – With Real World Business Examples!