Pivot Table Data Options

Some pivot table options deal specifically with how a pivot table interacts with external data. Although you've seen the PivotTable Options dialog box before, several options in the lower third of the dialog are specific to external data queries. To display the dialog box shown in Figure 9.18, right-click inside the pivot table and choose Table Options. The options specific to external data are listed here:

• Save data with table layout This option means that Excel will save your external data in the workbook so that the data is immediately available upon opening the file. Keep in mind that this behavior adds to your file size. By default, the setting for this state is "enabled."

• Enable drill to details When you double-click a data item in a pivot table, the default behavior for Excel is to drill into the details beneath that data item. Clearing the check box for this setting prevents this behavior. This option is not available for reports based on OLAP databases without special add-ins.

• Refresh on open Enabling this setting will ensure that the pivot table is automatically refreshed with the source data whenever the workbook is opened.

• Refresh every Select this check box to automatically refresh the PivotTable at an interval you specify.

• Save password Select this option to store the external data source password as part of the workbook. This will ensure that you will not be required to reenter your password when you refresh your data.

• Background query If you have a large external dataset that takes an extraordinarily long time to refresh, you can enable this setting so that you can continue your work while your data is updating. This option is not available for reports based on OLAP databases.

• Optimize memory When this option is enabled, Excel evaluates the results of the external query before it populates the pivot cache to determine whether storage of each row or column array in the results can be optimized for performance. Unfortunately, documentation on this option is thin, and it is unclear how this is actually done or how this option affects performance.

Figure 9.18. The options in the lower third of this dialog box are all specific to external data queries.