Pivottable options

Use the PivotTable Options dialog box to control various settings for a PivotTable.

Name    Displays the PivotTable name. To change the name, click the text in the box and edit the name.

Layout & Format

Layout section

Merge and center cells with labels    Select to merge cells for outer row and column items so that you can center the items horizontally and vertically. Clear to left-justify items in outer row and column fields at the top of the item group.

When in compact form indent row labels     To indent rows in the row labels area when the PivotTable is in compact format, select an indentation level of 0 to 127.

Display fields in report filter area     Select Down, Then Over to first display fields in the report filter area from the top to the bottom, as fields are added to it, before taking up another column. Select Over, Then Down to first display fields in the report filter area from left to right, as fields are added to it, before taking up another row.

Report filter fields per column     Type or select the number of fields to display before taking up another column or row based on the setting of Display fields in report filter area.

Format section

For error values show     Select this check box, and then type text, such as "Invalid", that you want to display in the cell instead of an error message. Clear this check box to display the error message.

For empty cells show     Select this check box, and then type text, such as "Empty", that you want to display in the cell instead of a blank cell.

Autofit column widths on update     Select to adjust the PivotTable columns to automatically fit to the size of the widest text or number value. Clear to keep the current PivotTable column width.

Preserve cell formatting on update    Select to save the PivotTable layout and format so that it is used each time that you perform an operation on the PivotTable. Clear to not save the PivotTable layout and format and resort to the default layout and format each time that you perform an operation on the PivotTable.

Totals & Filters

Grand Totals section

Show grand totals for rows    Select or clear to display or hide the Grand Total column next to the last column.

Show grand totals for columns    Select or clear to display or hide the Grand Total row at the bottom of the PivotTable.

Filters section

Subtotal filtered page items    Select or clear to include or exclude report-filtered items in subtotals.

Note: The OLAP data source must support the MDX expression subselect syntax.

Mark totals with *    Select or clear to display or hide an asterisk next to totals. The asterisk indicates that the visible values that are displayed and that are used when Excel calculates the total are not the only values that are used in the calculation.

Note: This option is only available if the OLAP data source does not support the MDX expression subselect syntax.

Allow multiple filters per field    Select to include all values, including those hidden by filtering, when Microsoft Office Excel calculates subtotals and the grand total. Clear to include only displayed items when Excel calculates subtotals and the grand total.

Note: This setting is only available for a non-OLAP data source.

Sorting section

Use Custom Lists when sorting    Select or clear to enable or disable the use of custom lists when Excel sorts lists. Clearing this check box may also improve performance when you sort large amounts of data.

Display

Display section

Show expand/collapse buttons    Select to display the plus or minus buttons that you use to expand or collapse row or column labels. Clear to hide the plus or minus buttons that you use to expand or collapse row or column labels. You might want to hide the plus or minus buttons when you print a PivotTable or when you display a PivotTable just for viewing.

Show contextual tooltips    Select to display tooltips that show value, row, or column information for a field or data value. Clear to hide tooltips that show value, row, or column information for a field or data value.

Show properties in tooltips    Select or clear to display or hide tooltips that show property information for an item.

Note: This setting is only available for an OLAP data source.

Display field captions and filter drop downs    Select or clear to display or hide PivotTable captions at the top of the PivotTable and filter drop-down arrows on column and row labels.

Classic PivotTable layout    Select or clear to enable or disable dragging fields on and off of the PivotTable.

Show items with no data on rows     Select or clear to display or hide row items that have no values.

Note: This setting is only available for an OLAP data source.

Show items with no data on columns    Select or clear to display or hide column items that have no values.

Note: This setting is only available for an OLAP data source.

Display item labels when no fields are in the values area    Select or clear to display or hide item labels when there are no fields in the value area.

Note: This check box only applies to PivotTables created prior to Office Excel 2007.

Show calculated members from OLAP server     Select or clear to display or hide calculated members in a dimension. This check box does not affect calculated measures.

Note: This setting is only available for an OLAP data source.

Field List section

The following two options are mutually exclusive.

Sort A to Z    Select to sort the fields in the PivotTable field list in ascending alphabetical sort order.

Note: This setting is not available for an OLAP data source.

Sort in data source order    Select to sort the fields in the PivotTable field list in the order that is specified by the external data source.

Note: This setting is not available for an OLAP data source.

Printing

Print expand/collapse buttons when displayed on PivotTable    Select or clear to display or hide expand and collapse buttons when you print a PivotTable. This check box is not available if the Show drill buttons check box is cleared in the Display tab of this dialog box.

Repeat row labels on each printed page    Select or clear to repeat the current item labels of the row label area on each page of a printed PivotTable.

Set print titles     Select or clear to enable or disable the repeating of row and column field headers and column item labels on each printed page of a PivotTable.

Note: To actually print the labels, you must still enter values in the Rows to repeat at the top or Columns to repeat at left boxes under the Print titles section in the Sheet tab of the Page Setup dialog box. (On the Page Layout tab, in the Page Setup group, click Print Titles.

Data

PivotTable Data section

Save source data with file    Select or clear to save or not save the data from the external data source with the workbook.

Notes: 

• This setting should not be used to manage data privacy.

• Also, this setting is not available for an OLAP data source.

Enable expand to detail    Select or clear to enable drilling down to detail data from the data source, and then displaying the data on a new worksheet.

Note: This setting is not available for an OLAP data source.

Refresh data when opening the file    Select or clear to refresh or not refresh the data when you open the Excel workbook that contains this PivotTable.

Note: This setting is not available for an OLAP data source.

Retain items deleted from the data source section

Number of items to return per field    To specify the number of items for each field to temporarily cache with the workbook, select one of the following:

• Automatic     The default number of unique items for each field.

• None     No unique items for each field.

• Max     The maximum number of unique items for each field. You can specify up to 1,048,576 items.

Note: This setting is not available for an OLAP data source.