Data field

The contents of this dialogue box is different for data fields in the Data area, and data fields in the Row or Column area of the Pivot Table dialogue box.

Subtotals

Specify the subtotals that you want to calculate.

None

Does not calculate subtotals.

Automatic

Automatically calculates subtotals.

User-defined

Select this option, and then click the type of subtotal that you want to calculate in the list.

Function

Click the type of subtotal that you want to calculate. This option is only available if the User-defined option is selected.

Show items without data

Includes empty columns and rows in the results table.

Name:

Lists the name of the selected data field.

Expands or reduces the dialogue box. The More button is visible for data fields only.

Options

Opens the Data Field Options dialogue box. The Options button is visible for filters and column or row fields only.

If the dialogue box is expanded by the More button, the following items are added to the dialogue box:

Displayed value

For each data field, you can select the type of display. For some types you can select additional information for a base field and a base item.

Type

Select the type of calculating of the displayed value for the data field.

Type	Displayed value
Normal	Results are shown unchanged
Difference from	From each result, its reference value (see below) is subtracted, and the difference is shown. Totals outside of the base field are shown as empty results. Named item If a base item name is specified, the reference value for a combination of field items is obtained by replacing the item in the base field with the specified base item. Previous item or Next item If "previous item" or "next item" is specified as the base item, the reference value is taken as the next visible member of the base field, in the base field's sort order.
% Of	Each result is divided by its reference value. The reference value is determined in the same way as for "Difference from". Totals outside of the base field are shown as empty results.
% Difference from	From each result, its reference value is subtracted, and the difference is divided by the reference value. The reference value is determined in the same way as for "Difference from". Totals outside of the base field are shown as empty results.
Running total in	Each result is added to the sum of the results for preceding items in the base field, in the base field's sort order, and the total sum is shown. Results are always summed, even if a different summary function was used to obtain each result.
% of row	Each result is divided by the total result for its row in the pivot table. If there are several data fields, the total for the result's data field is used. If there are subtotals with manually selected summary functions, the total with the data field's summary function is still used.
% of column	Same as "% of row", but the total for the result's column is used.
% of total	Same as "% of row", but the grand total for the result's data field is used.
Index	The row and column totals and the grand total, following the same rules as above, are used to calculate the following expression: ( original result * grand total ) / ( row total * column total )

Base field

Select the field from which the respective value is taken as base for the calculation.

Base item

Select the item of the base field from which the respective value is taken as base for the calculation.