Leading the way in Microsoft Office Development
 Home|Excel|Word|PowerPoint|Consultancy|Feedback|Contact 
 Microsoft Excel > Pivot Tables > Advanced Techniques< Previous | Next > 

 

Showing and Hiding Data

 
 

You can hide an inner field by double clicking on its outer field.

 
 

Double click the outer field heading to display the inner field.

 
 

Alternatively you can show and hide fields by selecting the corresponding button on the PivotTable toolbar.

 

 

AutoShow

 
 

You can use the AutoShow feature to display only a certain number of items in a field based on values in the data area of your table.

 
 

Choose the field in the pivot table and select (PivotTable > Field Settings)

 
 

Select Advanced.

 

   

 

Empty Cells and Cells containing Errors

 
 

By default cells that do not contain any numbers in the data area are left blank.

 
 

It is possible to display a value or a text entry in any empty cells.

 

   

 

It is possible to use formulas to calculate additional fields in your pivot table, and it is possible that this formula might return an error.

 
 

It is possible to display an empty cell, value or a text entry in any cells that contain errors.

 

 

Using a subset of Data

 
 

If you only want to use part of a list then you must use the advanced filter to filter the relevant data to another location on the worksheet.

 
 

Specify the location of your source data

 


 

Moving a Pivot Table

 
 

Once a pivot table has been created it can be easily moved.

 
 

It can be moved either by using the wizard or by cutting and pasting the cell range that contains the pivot table.

 
 

Select any cell in the table and select (Data > PivotTable and PivotChart Report). Specify a new cell address on the existing worksheet.

 

 

Pivot Table Restrictions

 
 

You cannot create a pivot table that contains more than 8000 total items.

 
 

You cannot create a pivot table that contains more than 256 fields in the Data Area.

 



 

Shortcut Keys

 
 
(Alt + Shift + Left Arrow)Ungroup the selected pivot table items.
(Alt + Shift + Right Arrow)Group the selected pivot table items.
(Ctrl + Shift + 8)Select the entire pivot table.
(Alt + Down Arrow) 
(Alt + L)Displays the pivot table field dialog box
(Alt + P)Move the selected field to the Page area.
(Alt + C)Move the selected field to the Column area.
(Alt + D)Move the selected field to the Data area.
(Alt + R)Move the selected field to the Row area.
(Up Arrow) 
(Down Arrow) 
(Left Arrow) 
(Right Arrow) 
 

 

Grouping on a particular field can be very useful if you want summarize data over a range of values -- Right-click the field label and choose Group and Outline | Group...

 
 

Blank rows in the data set will prevent you from using Grouping in your Pivot Table

 



 Copyright © 2004-2007 Better Solutions Limited. All Rights Reserved.< Previous | Top | Next >