Excel - file optimization
I am working with a fairly large Excel document (~ 9 MB), more than 60 sheets, and each of them contains many formulas CUBEVALUE
.
This document takes 2 minutes to open (not counting the value updates) and so far I have read a lot of guidelines like:
- splitting worksheets (not possible due to the nature of this file)
- shorter formulas, (possibly)
- tested on both 32 and 64 bit (performance is not significantly different)
I was wondering if you guys came across any ways to optimize the opening times of Excel without significantly altering the content inside it or any further suggestions.
source to share
-
Save it as a binary Excel workbook (.xlsb). You can save macros, the file size will be 25-35% of the original, and many operations (not just open / close) will be faster.
-
Get rid of any volatile functions that unnecessarily recalculate the worksheet. INDIRECT, OFFSET, ADDRESS, TODAY and NOW are volatile functions. Most of these can be replaced with non-volatile alternatives.
-
Improve the remaining workbook calculation by making the worksheet formulas and functions more efficient. Help on this subject is available at Code Review - Excel , a StackExchange partner. No examples were provided, so no specific help was offered.
-
Improve cod runtime for subprocesses on the same site. Large blocks should be processed "in memory" using arrays, and not looping cell by cell, etc. Again, no example is provided, so no specific help was offered.
source to share
If you are on a corporate network, try downloading the file to your local computer first and then open.
It can also depend on the existence of links to other files, try to reduce their number to a minimum, if any.
However, the amount of data in your file is the main driver of the open time.
source to share