Pivot data refresh from binary source file slow

Question

Hello,

The system I use is

OS Windows 7 64bit and Microsoft Excel 32bit (server system I don't know at this moment)

Below my question regarding the optimization of an excel file.

The old file

Shared xlsx file of 30Mb

3.6million cells

+/- 5Mb of repetitive formulas

+/- 10Mb of conditional format

Slow start save and closing

New file

Shared xlsb file of 9Mb

3.6million cells

replaces formulas with names containing the formula

removed some excess formatting

50-60% faster start and save

closing time same

In the old situation we have other excel files with pivots that extract data from this file.

When I have optimized the file and adjusted the link in the pivots to the binary file, I experienced that the time waiting for the data to be extracted from the binary source file is very very long 10-15min.

Nothing I have read on the net tells that this is an disadvantage of binary files.

So therefore I post this to ask if I do something wrong, should I change something for it to work with binary files?

I really would like to have a solution because the speed advantage working in the source file is very big.

Thanks in advance!

PDG

Answer 1

Hello,

Thank you for the reply.

Sorry this forum is for consumer level entry support, for issues related to ‘Pivot Table’ in Excel, your query would be better addressed in the Excel IT Pro as mentioned in the previous post. You may have to wait for the response in that respective forum.

Thank you.

Answer 2

Hello,

Hereby an update as requested by the forum mail.

I haven't found a solution / answer to this problem on this forum or the MS tech forum.

Our own IT department is also looking in to it.

From those three sources I have no answer as to why this occurs with binary files or what I can do about it.

Anyone with any suggestion to get in contact with specialist on the use of binary files throughout MS Excel 2010 would be very much appreciated.

Regards!

Answer 3

Hi Pep,

Thank you for posting your query in Microsoft Office Community.

Since your query is related to Pivot Table, you may also post this question on following TechNet forum for better suggestion:

http://social.technet.microsoft.com/Forums/en/excel/threads  

If you have any questions related to Office products, you can always reply and I’ll be happy to assist you.

Thank you.