I am working with an Excel 2013 workbook on Windows 10. It has ten pivot tables and takes a long time (~2 mins) to open. I have tried a lot of things to speed this up to no avail. Here are the specs:
The workbook is about 40 Mbytes in size. It is an *.xlsm file with a significant VBA app that can be activated by clicking a button. However, there is no Workbook Open event and no worksheet level events.
All of the pivot tables are based on data from a single worksheet within the workbook. No external data.
All of the pivot tables are set to NOT refresh on open. They are also set to not save data. (I tried both ways and if anything setting it to save data caused it to take longer to open.)
The pivot tables contain no formulas. The data worksheet has no formulas. The workbook in its entirety has very few formulas. The app creates formulas to populate the sheets, but then converts all of the formulas to values.
The pivot tables did have conditional formats, but even after deleting them all, the workbook still takes about the same time to come up.
This is not the startup delay issue. There is a dialog telling me that the load is 0% … 20% … 40% … complete.
I deleted just the pivot table sheets from the workbook and the workbook comes up in just a few seconds.
I also created a new workbook by copying over all the sheets and code thinking there might be a low-level corruption issue, but the copy didn't come up any faster.
Do pivot tables just naturally come up slow? Is there any property or setting I can change to speed up the workbook opening process? A number of people use these workbooks and are very dissatisfied.
