Add or remove a field

Add a field

Add a field to the row, column, data, filter, or detail area

1. On the toolbar in the PivotTable list, click Field List .

2. Use the scroll bars and the expand indicators ( and boxes) to find the field you want to add to the PivotTable list.

3. Click the field, and in the lower-right corner of the field list, select the area you want to add the field to.

4. Click Add to.

Notes

• If Add to becomes unavailable when you select Row Area, Column Area, Data Area, or Filter Area, the field you selected might not be available for use as a row, column, data, or filter field. If your PivotTable list is based on source data from an OLAP database, only the fields that are not listed under Totals in the field list can be used as row, column, data, or filter fields.

• If Add to becomes unavailable when you select Detail Data, detail data might not be available from the source data. For example, if your PivotTable list is based on source data from an OLAP database, often only summary data, rather than detail data, is available.

Add a total field

You can add total fields to the data area, but not to the row, column, detail, or filter area.

1. Click the field that contains the values you want to summarize.

2. On the PivotTable list toolbar, click AutoCalc , and then click the summary function you want.

Note   The AutoCalc button is unavailable when the source data for the PivotTable list does not allow you to add total fields ù for example, when the source data is from an OLAP database.

Add a calculated total or detail field

You can add a total or detail field that uses an expression to calculate data.

1. On the PivotTable list toolbar, click Calculated Totals and Fields .

2. On the menu that appears, do one of the following:

   • To create a total field, click Create Calculated Total.

   • To create a detail field, click Create Calculated Detail Field.

3. In the Calculation tab of the Commands and Options dialog box, type the expression you want, and then click Change.

Tip

To add field references, select a field that you want to use from the Insert Reference To box, and then click the Insert Reference to button.

Notes

• To edit the expression that is being used in a calculated field, select the field, edit the expression, and click Change on the Calculation tab in the Commands and Options dialog box. The field values will be automatically recalculated using the new expression.

• By default, data fields that summarize numeric data use the Sum summary function. Fields that summarize other types of data use the Count summary function.

Remove a field

When you remove a field from the layout of a PivotTable list, data from the field is no longer displayed; but the field remains available in the field list for viewing.

1. Click the field label of the field that you want to remove from the PivotTable list.
2. Drag the field outside the PivotTable list, until the pointer changes to .

Note   You can delete total fields and calculated detail fields from both the PivotTable list and the field list by right-clicking the field and then clicking Delete on the shortcut menu.