Leading the way in Microsoft Office Development

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

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