About adding and removing fields

Use the field list to view the list of fieldsets and fields that are available from the source data, and to add fields to the row, column, filter, data, and detail areas of the PivotTable list. In the field list, the fields that are not bold are available but not yet displayed in the PivotTable list. The bold fields are already displayed. You can scroll and click the expand indicators ( or boxes) next to the fieldsets to show or hide fields in the field list.

Use detail fields to display all of the available detail data and see what data is available to be summarized. When you add fields to the detail area, you display all of the detail records from the source data for the fields. Field names become column labels, and the detail records are displayed in rows below the column labels.

Detail fields

Detail data

If your PivotTable list is not based on source data from an OLAP database, you can add a calculated detail field that uses an expression to calculate detail values.

Use row and column fields to summarize and compare data. These fields display the unique items of data within a field down rows or across columns. The cell at each row and column intersection summarizes the data for an item.

Column field

Column field item

Row field

Row field items

Data fields provide the values to be summarized in the PivotTable list. When you add a field to the data area, the values from the field are summarized for the row and column field items.

Data field

Summarized data

A filter field is similar to a page field in a Microsoft Excel PivotTable report. Use filter fields to confine the view to a particular part of the available data. When you select an item in a filter field, data is displayed and calculated only for that item. For example, if you add a Region filter field, you can have the PivotTable list display and calculate data for the regions you select.

Filter field

Filter field item

Summary for the selected region

You can create new data fields by adding total fields to the PivotTable list. Total fields provide summarized data for use as data fields. For example, if your source data includes a Sales field that shows the amount of each sale, you could add a Sum of Sales total field to calculate the total sales, or a Count of Sales total field to calculate how many units were sold.

You can also add a calculated total field that uses an expression to calculate total values.

To eliminate data that you don't want to see or summarize, you can remove fields from the PivotTable list layout. Removing a field does not remove it from the available source data. A field that you remove is no longer displayed in the PivotTable list, but it remains available in the field list for viewing.

After you add a field to a PivotTable list, you can move it to other areas. When you move a field, you change the layout of the PivotTable list, and you change the data that's displayed and the summaries that are calculated.

Filter settings are retained when you remove a field. If you remove a field and later add the field back to the layout, the same items are again hidden.

You can view information about fields in a PivotTable list, including information about the field in the source database from which the data is taken and the type of data that the source database supplies for the field.

If the designer of the PivotTable list has set restrictions on adding and removing fields in the browser, you won't be able to add or remove fields. If your PivotTable list is based on source data from an OLAP database, such as one created by using Microsoft SQL Server OLAP Services, you'll notice that you can't add fields to the detail area. You'll also notice that some fields can be added only to the row, column, and filter areas. Other fields, listed under Totals in the field list, can be used only as data fields.