Pivot Table Options

Replaced in 2007

Name - Type a new name to change the name of the report. In a Pivot Chart report, this box contains the name of the associated Pivot Table report, and changing it renames the Pivot Table report, not the PivotChart report. You can provide a name for the pivot table. Excel will provide a default name.

Format Options

Grand totals for columns - Displays grand totals for columns in a Pivot Table report. In a Pivot Chart report, the grand totals are displayed in the associated PivotTable report.
Grand totals for rows - Displays grand totals for rows in a Pivot Table report. In a PivotChart report, the grand totals are displayed in the associated PivotTable report.
Autoformat table - Applies the default Microsoft Excel autoformat to your Pivot Table report. Use the Format Report command on the PivotTable toolbar to apply a different autoformat to your PivotTable report. This option is not available in PivotChart reports.
Subtotal hidden page items - Includes hidden page field items in Pivot Table report subtotals. In a PivotChart report, the subtotals are affected in the associated PivotTable report. This option is not available in reports based on source data from OLAP databases.
Merge labels - Select to merge cells for all outer row and column labels in the PivotTable report. This option is not available in PivotChart reports.
Preserve formatting - Retains any formatting you apply to a PivotTable report when the data is refreshed or the layout changes. This option does not apply to Pivot Charts and any formatting changes will be lost when the chart is refreshed.
Repeat item labels on each printed page - Select to print outer row field item labels at the top of each printed page. Item labels are repeated for all row fields to the left of the field for which a page break separates a group of items. Clear this check box to print the item labels on only the first printed pages where they appear.
Mark total with * - Displays an asterisk (*) after every subtotal and grand total in PivotTable reports that are based on OLAP source data to indicate that these values include any hidden items as well as the displayed items. Clear this check box if you do not want to see the asterisks.
Page layout - Select the order in which you want page fields to appear. The default page field layout is Down, then over. This option is not available in PivotChart reports.
Fields per column - Select the number of page fields you want to include in a row or column before starting another row or column of page fields. This option is not available in PivotChart reports.
For error values, show - Select the For error values, show check box to display a value in place of an error; then type a value to display. This option is not available in PivotChart reports.
For empty cells, show - Select the For empty cells, show check box to display a value in place of blank cells; then type a value to display. This option is not available in PivotChart reports.
Set print titles - Select to use the field and item labels in the PivotTable report as row and column print titles. If you change the report layout so that the field and item labels are in different rows and columns, the new label rows and columns are automatically used as the print titles. Before you select this check box, click Page Setup on the File menu, click the Sheet tab, and clear the Rows to repeat to top and Columns to repeat at left check boxes, and make sure the current report is the only one in the print area.

Data Options

Save data with table layout - Saves a copy of the internal data for the report in the workbook file so that you do not have to refresh the report before you can work with it when you open the workbook file. If you do not select this option, your workbook file will be much smaller. However, you must either select the Refresh on open check box or click Refresh Data on the PivotTable toolbar when you open the workbook before you can work with the report. If you do not save this data, it will also be discarded from other reports in the workbook that are based on this PivotTable report or the PivotTable report associated with the current PivotChart report. This option is ignored in report template files.
Enable drill to details - Clear the check box to prevent showing detail when you double-click a cell in the data area of a PivotTable report. In a PivotChart report, this option affects only the associated PivotTable report. This option is not available for reports based on source data from OLAP databases.
Refresh on open - Updates the PivotTable report and any corresponding PivotChart will be automatically updated with the source data whenever the workbook is opened.
Refresh every - Select to automatically refresh the PivotTable or PivotChart report from the external source data, and then enter the number of minutes to wait between refreshes in the minutes box.
Save password - If you use an external database that requires a password as your data source for the PivotTable or PivotChart report, select the Save password check box to store the password as part of the report. When you update your report, you do not need to re-enter the password.
Background query - Clear to wait for the data to be retrieved when you run the query to retrieve the external data for the report. When this check box is selected, Microsoft Excel runs the query in the background while you continue your work. This option is not available for reports based on source data from OLAP databases.
Optimise memory - Select to save memory when you refresh your report from an external data source. This option is not available for reports based on source data from Microsoft Excel lists or from OLAP databases.