Excel - File optimisations

Question

I'm working with a rather large Excel document (~9MB) in size which has over 60 sheets and each containing many CUBEVALUE formulas in it.

This document takes over 2 minutes to open (not counting refreshing of values) and while i have read many recommendations e.g:

• splitting of worksheets (not possible due to the nature of this file)
• shorter formulas, (not possible)
• tested on both 32 and 64 bit (performance is not notably different)

I was wondering if you guys came across any ways of optimising opening time for Excel without significantly altering the contents within in it, or any further suggestions.

Answer 1

1. Save it as an Excel Binary Workbook (.xlsb). You can retain macros, the filesize will be 25-35% of the original and many operations (not just opening/closing) will be faster.

2. Get rid of any volatile functions that are recalculating the worksheet unnecessarily. INDIRECT, OFFSET, ADDRESS, TODAY and NOW are among the list of volatile functions. Most can be replaced with non-volatile alternatives.

3. Improve the remaining calculation of the workbook by making worksheet formulas and functions more efficient. Help on this is available at Code Review - Excel, a StackExchange partner. No examples supplied so no specific help offered.

4. Improve any sub procedure cod run times at the same site. Large blocks should be processed 'in-memory' with arrays, not looped through cell-by-cell, etc. Again, no examples supplied so no specific help offered.

Answer 2

If you use corporate network try first downloading the file to you local computer and then opening.

It may also depend on existence of links to other files, try to reduce their number to minimum if there are any.

Nontheless, the volume of data in your file - is the main driver of opening time.