Leading the way in Microsoft Office Development

Excel

Potential Problems

If someone adds or deletes a column in the original source data, the pivottable cannot be refreshed and displays an error.

You can use a pivot table to link to a range of data on another sheet and display it in a different order.

A pivot table is extremely useful when you have lots of changing rows, or blank rows , when the number of rows changes frequently.

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.

Multiple Consolidation Ranges Example

Shortcut Keys

To switch from one summary function to another select an item in the data area of your pivot table, click the field settings button and then choose from the list that appears in the Pivot Table field dialog box.

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

Use pivot tables as an alternative to linking workbooks using formulas

SS good examples