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

Bottlenecks that slow down calculation and/or workbook opening and closing can occur in:

  • Your System.
  • Excel calculations.
  • Excel memory and workbook size.
  • Excel settings.

System Bottlenecks:

Some hardware and system software features and settings can slow down Excel.

Temporary Files

Temporary files may accumulate in your \Windows\Temp directory (Win95/98/ME), or your \Documents and Settings\ \local settings\temp directory (Win2K/WinXP).
They are created by Excel for the workbook, and in particular for controls being used by open workbooks. If Excel crashes for any reason these files may not be deleted.
They may also be created by software installation programs.
Too many temporary files can cause problems, so clean them out from time to time, but be careful not to do this if you are have done a software install that requires you to reboot your PC, but have not yet done so. In this case you should reboot before deleteing the temp files.
An easy way to access your temp directory is from the Windows Start button: Start-->Run-->%temp%-->Ok.

The FastExcel Version 4 Clean Workbook command allows you to clean up your temp directory without leaving Excel.

RAM:

64 or 128 MB of RAM usually works well.

Paging to your virtual memory swap-file is extremely slow. You need enough physical RAM for the operating system, Excel and your workbook(s), and RAM is not expensive.

  • If you have more than very occasional hard disk activity during calculation, you need more RAM.

For Windows 95/98

  • 32 MB RAM is fine for small workbooks.
  • 64 MB RAM is OK unless you need to open more than about 32MB of workbooks.
  • 128 MB RAM works well except when using workbooks containing large amounts of data.
  • You will continue to see small speed improvements with 256MB when using large workbooks.

For Windows ME/NT/2000 its probably a good idea to have an additional 32 or 64MB, and for Windows XP an additional 128MB.

Excel 2002 (Office XP) can make effective use of more RAM than previous versions. With very large workbooks you may see speed improvements using 384MB or 512MB of RAM.

Excel 2003 has substantially increased memory capacity and can use 1024MB or more of RAM.

MHZ:

If you are using large spreadsheets get the fastest machine you can afford.

Although it is often possible to improve Excel calculation time by a large factor, as you would expect, Excel always runs faster on a fast machine, so if you have a slow machine you should upgrade to a faster one.

Of course FastExcel might give you an alternative solution!

Outlook Journalling

Outlook Journalling is often cited as a cause of slow response.

If you have Microsoft Outlook installed make sure that you have Journalling turned off, otherwise your machine may behave like a snail. To turn Journalling off try one of these (depends on your version of Outlook):

  • Outlook-->Tools-->Options-->Journal-->Uncheck Excel.
  • Outlook-->Tools-->Options-->Preferences-->Journal Options

FindFast (Off 97 & 2000) & Fast Search (Office XP)

FindFast/Fast Search has a disconcerting habit of springing to life whenever you don’t need it, and in my opinion it doesn’t do anything very useful anyway besides chew up a lot of hard disk space. My advice is to turn it off:

  • Office 97 & 2000: Use the Control Panel, FindFast Icon and delete the indexes etc.
  • Office XP: File -->Search-->Other Search Options --> Fast Searching is Enabled --> Search Options --> Advanced --Delete. ( If Fast Searching is Enabled does not appear, it is not installed).

Fragmented Swap-File

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.

Opening/Saving Bottlenecks.

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.

Outlook Journalling.

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.

  • Office 97 & 2000: Use the Control Panel, FindFast Icon and delete the indexes etc.
  • Office XP: File -->Search-->Other Search Options --> Fast Searching is Enabled --> Search Options --> Advanced --Delete. ( If Fast Searching is Enabled does not appear, it is not installed).

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 files used.
This may also cause problems opening/saving a workbook on a WAN (or even a LAN).
These temporary files may also build up in your windows temporary directory:
- Try deleteing all files and subfolders in Windows\Temp or documents and settings\<user>\local settings\temp

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.

Excel 2000.

  • The original version of Excel 2000 was very slow at updating links to closed Excel workbooks. Make sure you are at the SR1 level.
  • When in Automatic calculation mode Excel 2000 calculates workbooks last saved by Excel97 as they are opened. Where possible make sure all your workbooks have been saved by Excel 2000, or use manual calculation. See MSKB Q210162

Excel 2002/2003

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

http://asia.cnet.com/itmanager/netadmin/0,39006400,39108281,00.htm

http://support.microsoft.com/default.aspx?scid=kb;EN-US;810907

Toolbar Files (.XLB).

Check the size of your toolbar file. Mine is about 12KB.

Adding/changing or customising toolbars causes the size of your toolbar.XLB file to increase. Each user has their own XLB file. Deleting it (renaming it to toolbar.OLD is safer) removes all your toolbar customisations: A new .XLB file will be created the next time you open Excel.

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 MSKB Q254733 for WAN problems with Forms.

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 and references.
The golden rule for removing calculation bottlenecks is to look for these repeated/duplicated calculations, move them to a single 'helper' cell, and then reference the helper cell from the original formulae.

Links

My advice is to avoid workbook links wherever possible.

Workbook Links:

Avoid Workbook Links wherever possible:

Links are slow, easily broken, error-prone and not always easy to find and fix. If Excel tells you that your workbook has links (phantom links), but you can’t find them, download Bill Manville’s excellent FINDLINK.XLA from Stephen Bullen’s website http://www.oaltd.co.uk/.

Fewer larger workbooks are usually (but not always) better than many smaller workbooks: the exception is usually when you have a lot of front-end calculations that are very rarely recalculated so it makes sense to put them in a separate workbook.

Excel calculates workbooks in workbook name sequence , so make sure the inter-workbook references only go in one direction (no forward links).

Try to use simple direct cell references that work on closed workbooks. That way you can avoid recalculating ALL your linked workbooks whenever you recalculate ANY workbook, and you can see the values Excel has read from the closed workbook, which is often important for debugging and auditing the workbook.

Worksheet Links:

Using fewer worksheets usually calculates faster, but you need to balance this against maintainability and useability.

Using lots of worksheets can make your workbook easier to use, but there can be a large performance and memory penalty, particularly if you have complex inter-linking between Worksheets.

Excel 97 and 2000 calculate worksheets in worksheet name sequence, so try and make sure that references between sheets always refer backwards to sheets that have already been calculated. Forward worksheet cross-references to sheets that have not yet been calculated can be very slow.

Excel 2002 handles inter-worksheet dependencies in a more global way, and can be substantially faster than previous versions on problem workbooks.

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:

  • A worksheet for What-If parameters (Dashboard).
  • A worksheet with your more-or-less static data on it.
  • As few as possible worksheets for calculations.
  • A worksheet for reports.
  • A worksheet for summary results.

Small workbooks may combine the reports and summary reports sheets, and the parameters and static data sheets.

Linked Pictures:

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.
You can tell if the pictures embedded on your worksheets are dynamically linked because when you select them a formula appears in the formula bar that points to the source for the picture.

Exact Match Lookup using VLOOKUP, HLOOKUP, MATCH

Exact Match Lookup is slow, approximate match lookup is fast.

See Optimising Lookups Tips and Tricks , and FastExcel V4 AVLOOKUP.

If you are doing lookup’s using the exact match option for these functions the calculation time for the function is proportional to the number of cells scanned before a match is found.

For lookups over large ranges this time can be very significant.

Lookup time using the approximate match options of VLOOKUP, HLOOKUP and MATCH on sorted data is fast and is not significantly increased by the length of the range you are looking up. (Characteristics are the same as Binary Search).

Array Formulae :

Array formulae are very powerful, but need to be handled with care.

Single-cell array formulae are evaluated multiple times, depending on the number of cells referred to in the formula. This can take significant time, and may or may not be faster than the alternatives.

It is important to minimise the number of cells referenced by array formulae.You may be able to use Excel’s DSUM function or FastExcel’s AVLOOKUPS function to speed up conditional sum array functions.

A range array formula covering multiple cells may be faster to calculate than individual formulae in each cell (although the speed advantage seems less in Excel97 and Excel2000).

Because array formulae and functions like SUM which reference ranges influence the sequence in which Excel calculates, you should try and avoid mixing row and column references or overlapping array references, for example:

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.

Functions

SUM, SUBTOTAL, COUNT, SUMIF and COUNTIF

Don’t SUM unnecessary cells.

The calculation time for these functions  is proportional to the number of cells you are summing or counting, so make sure you don’t include more cells than you need. See Dynamic Ranges .

User-Defined Functions

UDF’s can be slow.

It’s usually significantly faster to use Excel’s worksheet functions and calculations than to use user-defined functions. This is because there is significant overhead transferring information from Excel to the UDF and back.

See Excel Calculation Details - User Defined Functions for more details.

Volatile Functions

Avoid using volatile functions if possible.

Usually Excel’s dependency recalculation engine  will only calculate only the minimum number of cells. If you have many volatile functions  the recalculation time can increase to close to the full calculation time.

The FastExcel Profile Workbook command shows you the degree of volatility of your whole workbook.

Other:

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

Defined Names are one of Excel’s 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.
The slow-down can be bypassed by:

  • using row height of less than 0.09 (treated as zero)
  • hiding the rows
  • setting Application.Screenupdating=false before 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.

Used Range problems are a frequent cause of file bloat.

Excel attempts to track the last used cell on each worksheet, but does not always reset this as you might expect. The last used cell is not just the last cell that has contained a formula or data, but also the last cell with formatting (includes non-standard column width or row height). Sometimes the last cell somehow gets set thousand of rows or hundreds of columns too large, either because of excess formatting or because a large number of cells, rows or columns have been inserted or deleted. This can cause the file size and memory used to grow very large.
You can check the location of the last visible used cell on a worksheet using Ctrl-End or Edit-->GoTo-->Special-->LastCell. If you have hidden cells or rows it is a good idea to unhide them before doing the Ctrl-End.
The FastExcel Profile Workbook command will show you, for each worksheet, how many cells Excel thinks are being used, and the percentage of these (%Waste) that are outside the last formula or data cell.

You can correct the Used Range problem by deleting the unwanted rows and columns and saving the workbook.
Make sure you backup your workbook before the deletion in case you have formulae referencing the deleted cells:
If you have formulae which reference the rows and columns you have deleted Excel will try to adjust the references in these formulae. If the cells referred to no longer exist the reference becomes #N/A.

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.
All FastExcel Profile … commands will highlight this format in orange.

InterSheet References.

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.

I recommend Rob Bovey’s Code Cleaner.

Making changes to any Visual Basic modules contained in your workbook will tend to accumulate redundant material that will grow the size of the workbook. Use Rob Bovey’s Code Cleaner regularly to eliminate this: http://www.appspro.com/

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.

Importing bitmaps.

To minimise memory and workbook size, make sure you have resized your bitmaps to the size you want BEFORE inserting them into Excel.

Zero-Sized Graphics.

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.

External Links.

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.
See MSKB article Q123269 for more details.

Formats.

Using many different formats will increase the size of your workbook.
Converting a file from Lotus 123 may cause this problem to occur.

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.
© 2001-2020 Decision Models  Legal Disclaimer Privacy Statement E-Mail Webmaster