File Size Possible Issue (Downsizing ideas?)

[dwilliamson1024]

I have an Excel file (I don't consider it to be too big (88,000 KB) that also references one other data dump file so sometimes caches an extra 42,000 KB.

When end-users are using the file and actually in the file, everything runs very quickly; however, I am starting to get complaints on how long it takes to actually open the file up. Anyone run into this?

Any ideas?

Would it help if I separated out the raw data and only displayed the charts and metrics? Then it would only really reference the data if someone adjusts a pivot or something. Would this speed up the opening process, or will it only slow down the file to maneuver around in it after the fact? Thoughts?

 

[S O WXLS]

Have you checked that all the used ranges are set to the correct area?

 

[dwilliamson1024]

SO WXLS Could you elaborate please? I don't understand.

 

[deletedalien]

i have ran in to this same problem and yes your best bet (considering the raw data will no longer change or is historical data) then yes you can create a pivot table or tables as necessary and then copy only the sheets containing the pivots to a new workbook and save THAT workbook for distribution, it will be much quicker and lighter, users can filter data and change the summaries by sum, average, count etc...

Just my 2 cents.

HTH

 

[dwilliamson1024]

The data does change, but it is pulling from a data cube we made and so the ability to store the file as a "supporting document" in the same folder location is very doable. Just didn't know if that slows down the end-user when they try to manipulate the pivots since they are stored outside the open workbook?

 

[S O WXLS]

The used range can sometimes expand well beyond where it should be, and it can cause issues such as increased files size, performance decreases etc.

Quick and easy way to see if it is the case:

On each sheet, go to the last column of data - select the column after this (blank one) and then press CTRL+SHIFT+RIGHT ARROW to select all the other blank columns. Right click and select "delete columns". Then save the workbook, repeat this for blank rows (select blank row, then press CTRL+SHIFT+DOWN ARROW to select all other blank rows)

It may or may not be the case, but it's a quick and easy check that can be done nevertheless...

 

[deletedalien]

The data does change, but it is pulling from a data cube we made and so the ability to store the file as a "supporting document" in the same folder location is very doable. Just didn't know if that slows down the end-user when they try to manipulate the pivots since they are stored outside the open workbook?

In that case no it wont slow down the users who open this file UNLESS they refresh the pivot, regardless if the data has changed or not.

 

[dwilliamson1024]

They won't be refreshing the pivots but there are slicers, etc and have the ability to slice and dice the data for different suppliers, buyers, production planners, etc to get more granular...or double clicking to get the raw data. This would happen a lot, which would create a change to the pivot which means it would have to go to the external file to read and cache the data, correct?

 

[SpillerBD]

No the Pivot Cache does not get updated. When you refresh the the Pivot Data, it refreshes the Pivot Cache. Having both the Pivot Cach (required for the pivot table) and the source data for the Pivot Cache is likely redundant. The Pivot Cache can also be updated through a data connection anyway.
Pivot table layout, filters, slicers and such do not update the cache.

 

[dwilliamson1024]

ok. good to know. Thanks!