There are many reasons, why your Excel sheet is slow and there are many things what you can do about a slow Excel sheet.
Your sheet is slow because
- you use a large data set,
- you use slow built-in functions,
- you implemented a slow user-defined function (UDF) or
- you access a slow external library.
So, what can we do about it?
General Speed-up for Excel Sheets
Turn off Automatic Workbook Calculation
The most annoying property of a slow Excel sheet is that you have to wait after each data entry. You can avoid this pause by setting the re-calculation to manual:
Then, a re-calculation can be performed pressing the “F9” key.
Enable parallel processing of Excel Workbooks
If your sheet is generally slow and it is not due to some external library call, enabling parallel processing often helps:
Change your references and formulas
Conditional formating can slow down your sheet significantly. Try to remove it, where possible.
Find a fast rewrite for your Excel cells
There are numerous things in your Excel sheet which can slow you down. Starting from the order of references (left to right is faster than right to left) to avoiding volatile functions like today() and random().
Official Microsoft Excel Performance tips.
A great source of tips speeding up Excel is the FastExcel blog.
Separate your Excel worksheet into several sheets
Excel has a clever mechanism for automatic re-calculation of only the cells which might change after a new imput. But, Excel can track 65,536 dependencies to unique references for automatic calculation, only. If Excel finds more references, Excel gives up the clever routine and just re-calculates everything.
Call User Defined Functions
Rewrite your algorithm as a User-Defined Function in VBA
In some cases, it is required to rewrite your model as a user defined function. You can do that in many ways, a simple way is to write your algorithm as a UDF in Visual Basic for Applications (VBA).
Rewrite your algorithm in a fast User-Defined Function
Look into my previous post which language to choose for implementing a UDF.
Substitute your slow UDF by a call to Theta Proxy
If you cannot rewrite your UDF, Theta Proxy is a new product, which can help: If you have a slow UDF, you can call Theta Proxy trains the slow function and can substitute your UDF with a fast proxy. E.g. a slow Monte-Carlo simulation in a UDF can take several seconds for each call. Then, Theta Proxy can perform the same task in a split second.
You can speed up your sheet using several techniques. Optimize the Excel options, rewrite your sheet, rewrite your algorithm as a UDF or call Theta Proxy for speed-up of UDFs you cannot rewrite.
Leave a Reply