How can I speed-up my MS Excel sheet?

· MS Excel
Authors

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

  1. you use a large data set,
  2. you use slow built-in functions,
  3. you implemented a slow user-defined function (UDF) or
  4. 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:

Excel settings

Screenshot of MS Excel 2010 Options (File->Options->Formulas)

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:

Screenshot of MS Excel options (File->Options->Advanced)

Change your references and formulas

Remove formating

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.

Conclusion

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.

1 Comment

Comments RSS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s