The Slow File Load
If you have an incredibly complex Excel workbook containing multiple Pivot Tables tied to massive external data sources, opening the file can take several minutes. By default, Excel may be configured to automatically “refresh” all Pivot Tables the moment the file is opened. While this ensures your data is perfectly up-to-date, it means you cannot quickly open the file just to check a formatting issue or grab a single piece of static information without enduring a massive, CPU-heavy data query that locks up the application.
How to Disable Refresh on Open
You can change the settings for each Pivot Table so that they only refresh when you explicitly tell them to, drastically speeding up the time it takes to open your workbook.
1. Open your Excel workbook and navigate to the sheet containing your Pivot Table.
2. Click anywhere inside the Pivot Table to select it.
3. On the ribbon at the top of the screen, click the PivotTable Analyze tab (or just “Options” in older versions).
4. On the far left side of this ribbon tab, click the PivotTable button, then click Options.
5. A new dialog box will appear. Click on the Data tab at the top.
6. Look for the checkbox labelled Refresh data when opening the file.
7. Untick the checkbox.
8. Click OK.
How to Manually Refresh Your Data
With this setting disabled, the file will open instantly, displaying the data exactly as it was when you last saved and closed it. When you are actually ready to pull down new data from the server, simply click anywhere inside the Pivot Table, navigate to the PivotTable Analyze tab, and click the large Refresh button. This puts you entirely in control of when the heavy processing occurs.