Excel Calculation Bottlenecks
|Importance of Speed||Faster Calculations||Microsoft's Advice||Bottlenecks/Size|
|Lookups||Dynamic Ranges||Totalling||Generating Workbooks|
|Multi-Level Calculations||Array Formulae||SUMIF Example||Worksheet Links|
|System Bottlenecks||Slow Open/Close||Links||Exact Match|
|Array Formulae||Functions||Size & Memory|
Bottlenecks that slow down calculation and/or workbook opening and closing can occur in:
Some hardware and system software features and settings can slow down Excel.
Temporary files may accumulate in your \Windows\Temp directory (Win95/98/ME),
or your \Documents and Settings\
The FastExcel Version 4 Clean Workbook command allows you to clean up your temp directory without leaving Excel.
FindFast (Off 97 & 2000) & Fast Search (Office XP)
FindFast/Fast Search has a disconcerting habit of springing to life whenever you dont need it, and in my opinion it doesnt do anything very useful anyway besides chew up a lot of hard disk space. My advice is to turn it off:
Make sure your Windows swap file is located on a disk with a lot of space, and that you de-fragment the disk from time to time.
If one or more of your workbooks seem to open and close more slowly than you think they should, check out these possible problems.
Too many Temp Files.
Try deleting ALL the files and subfolders in your \Windows\Temp directory (Win95/98/ME), or your \Documents and Settings\<user>\local settings\temp directory (Win2K/WinXP). These can build up over time and cause Excel to open files very slowly. See MSKB Q299372
Make sure you have completed any software installations that require a reboot before you delete all the temp files!
Workbook with password-protected structure.
A workbook that has its structure protected with a password (Tools-->Protection-->Protect Workbook) will open and close much slower than one protected without a password.
Used Range problems
Excess used range can cause slow opening, especially if caused by hidden rows/columns with non-standard height/width. Delete the excess rows and columns, or use FastExcel V4's Clean Workbook command.
If this is turned on it will slow down opening and closing workbooks.
FindFast (Off 97 & 2000) & Fast Search (Office XP)
Make sure you have FindFast and FastSearch turned off.
Large number of Controls on Worksheets.
A large number of controls (checkboxes, hyperlinks etc) on worksheets
can slow down opening a workbook because of the number of temporary
Large number of Links to other workbooks.
If possible open the workbook(s) you are linking to before the workbook that links. Often its faster to open a workbook than read the links from a closed workbook.
Virus scanner settings.
Some virus scanner settings can cause problems with open/close/save, particularly on a server. If you think this might be the problem, try temporarily switching the scanner off. See MSKB 262530
Slow Calculation causing slow open/save
Under some circumstances Excel will recalculate your workbook when it opens or saves it. If the calculation time for your workbook is long and this is causing a problem make sure you have calculation set to manual, and that consider turning off calculate before save (Tools-->Options-->Calculation).
Addins/VBA Auto-Open problems.
See if the workbook opens faster when you open it but hold the shift key down. This disables any macros in the workbook.
Too many files in XLStart or the Alternate Startup Directory.
If you have a large number of files in your XLStart directory, or an alternate startup directory (Tools-->Options-->General), this may slow Excel down.
Too many Add-Ins.
I tend to use a lot of add-ins, and if I don't check from time to time the number of add-ins automatically loaded can get large. Periodically prune the list of add-ins that are automatically loaded (Tools-->Addins), or check out my Auto-Reversioning Demand-Loading Addin Loader.
Early version of Excel97
The original version of Excel 97 had a number of problems that could cause slow opening/lockup. Make sure your installed version is at the SR2 level (Help -->About should say Microsoft ® Excel 97 - SR2). If you have an earlier version contact Microsoft for a free upgrade CD, or download the updated from the Microsoft site.
When Excel 2002 opens a workbook that was last saved by a previous version of Excel substantial extra work is done to calculate and error check the workbook. Where possible make sure all your workbooks have been saved by Excel 2002/2003, and avoid having some users on previous Excel versions.
If you have upgraded to Excel2002/2003 from an earlier version and you have Norton Antivirus installed the integrated virus checking may cause slower file opening.
Excel 2002/2003 has advanced encryption methods which may slow open and close for protected workbooks.
New Excel 2002/2003 features such as Fast Search and Background Error Checking may also slow response time.
A large number of VLOOKUPs may cause slow opening with Excel2002/2003: see MSKB 327365
WinXP Pro SP1 with Windows2000 Server: Office XP slow file retrieval and crashes
There are frequent reports of slow opening with Office XP/WinXP. One possible solution is suggested to be SMB signing incompatibility: see
Toolbar Files (.XLB).
LAN or WAN problems.
The complex structure of an Excel workbook means that Excel does
not read or write a workbook sequentially. In addition Excel SAVE's
a workbook first as a temporary file, then deletes the workbook, and
then renames the temporary file. This process is timing dependent
and may contribute to opening/closing bottlenecks or problems, particularily
with a Server or Virus scanner.Sometimes you are better off copying
a workbook from the Server to your local PC, operating on it locally,
and then copying it back.
See also David McRitchie's Slow Response page and MSKB Q280504
Excel Calculation Bottlenecks
These are some of the most frequently occurring Excel calculation bottlenecks.
Repeated or duplicated calculations.
Because most spreadsheets are constructed by copying formulae containing
a mixture of absolute and relative references it is very easy to create
a large number of formulae containing repeated or duplicated calculations
See FastExcel Map CrossReferences to automatically determine a near-optimum worksheet calculation sequence.
A good design compromise is to try to split large workbooks as follows:
Small workbooks may combine the reports and summary reports sheets, and the parameters and static data sheets.
Dynamically linked pictures can significantly slow both calculation
and editing. These linked pictures get refreshed during a recalculation
and also during many editing and VBA operations.
Exact Match Lookup using VLOOKUP, HLOOKUP, MATCH
Array Formulae :
Adding SUM functions at the foot of each column to large sets of single-row, multiple column array functions can sometimes cause calculation time to increase by a factor of 2 or more.
See also Optimising Array Formulae.
SUM, SUBTOTAL, COUNT, SUMIF and COUNTIF
The FastExcel Profile Workbook command shows you the degree of volatility of your whole workbook.
Conditional Formats & Data Validation:
Conditional formats and data validation are great, but using a lot of them will significantly slow down calculation.
FastExcel Profiler shows you the number of the conditional formats used.
Defined Names are one of Excels most powerful features, but they do take some additional calculation time. Using Names that refer to other worksheets adds an additional level of complexity to the calculation process. Also try to avoid nested names (names referring to other names).
Because Names are calculated every time a formula that refers to them is calculated you should avoid putting calculation-intensive formulae or functions in defined names. It can be significantly faster in these cases to put your calculation-intensive formula or function in a spare cell somewhere and refer to that cell instead, either directly or via a name.
FastExcel shows you the number of defined names used.
Character Manipulation in Formulae
Manipulating character strings in formulae is relatively slow. When you have a lot of formulae handling character strings you may notice this:
Rob Bovey recommends using IF(ISBLANK(A1), ) to test if a cell is blank rather than IF(A1=, ).
Concatenating two columns/rows to make a new column/row is slow.
Row height of 0.1
Using a row height of 0.1 will slow down calculation. This slows
down the screen refreshes which occur during calculation.
This problem was discovered by Stephen Bullen Office Automation Ltd
Size & Memory Problems.
Here are some of the factors that can cause workbook size and memory problems. See also reducing memory used.
Used Range Problems.
You can correct the Used Range problem by deleting the unwanted rows
and columns and saving the workbook.
In Excel97 and later accessing the UsedRange property of a worksheet causes Excel to reset the used range, but this will still include the last cell with formatting.
FastExcel Version 4 contains a Clean Workbook command that allows you to clean the used range in a non-destructive way.
Tracking Changes in a Shared Workbook.
This will rapidly cause your workbook file-size to increase.
Saving in Dual format: Excel 97 & 5.0/95.
This will double the size of your file.
Intersheet references use more memory than onsheet references. References to ranges on other worksheets should be as small as possible because Excel can use considerable memory storing intersheet dependency trees.
Visual Basic Changes.
Charts, Graphics, Zoom etc.
Excel will only use a limited amount of memory on charts, graphic objects and zoom before you get an Out of Memory message, regardless of how much RAM you have.
To minimise memory and workbook size, make sure you have resized your bitmaps to the size you want BEFORE inserting them into Excel.
Some conditions cause Excel to make Graphic Objects visibility zero height and/or width. When this happens the graphic objects still use memory and filesize, but theu are hard to find and delete except using VBA.
Excel will only use a limited amount of memory on external links before you get an Out of Memory message, regardless of how much RAM you have.
Opening a Lotus WK4 file.
When you open a Lotus WK4 file and save it as an Excel file, the
file may get very large. This is caused by the import process assigning
individual formats to each cell in a column.
Using many different formats will increase the size of your workbook.
Multi-Cell Array Formulae
When you have a large rectangular block of formulae you may be able to convert them to a single mult-cell array formula. This will reduce file size somewhat, but does not appear to significantly affect memory.