- My Beautiful Formatting Disappeared!
- I Cannot Pivot the Report
- The Summary Function I Want is Not Available
- Response Is Slow With External Data
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Response Is Slow With External Data
When you create a table linked to external data, you might run into problems with slow response in your worksheet. Sometimes error messages pop up, too. You might not be able to fix all these problems because they might occur because of problems in the external data source itself or, if the data source is remote, in the connection to it. There are a few things you can try that may make a significant difference, however.
Many of these problems arise when laying out the PivotTable after completing the PivotTable Wizard. As you drag fields onto the PivotTable, Excel tries to retrieve the data, which is a process that can be slow. Here’s what you can try:
- If you have not yet completed the PivotTable Wizard, but suspect that the layout process may be slow, click the Layout button in step 3 of the wizard. This step displays the Layout dialog box, which lets you define the PivotTable layout without the delays that might occur when using drag and drop after the wizard is complete.
- If you have already completed the PivotTable Wizard and find that the layout process is slow, select the Always Display Items command on the PivotTable menu. This command turns off data display, letting you complete the layout without waiting for data retrieval. When the layout is complete, select the command again to turn data display back on.
Another way to speed data retrieval is to use a Page field so that only part of the data to be retrieved at once (although this is not possible with OLAP data sources). Here’s how (when using the Layout dialog box to define the report):
- Drag a field onto the Page area.
- Double-click the field to open the PivotTable Field dialog box.
- Click the Advanced button.
- Select the option Query External Data As You Select Each Page Field Item.
This technique can also help when you receiving Out of Memory or Out of Resources error messages. It is also possible to avoid these messages by selecting the Optimize Memory option in the PivotTable Options dialog box.