Welcome to MSDN Blogs Sign in | Join | Help

Microsoft Excel

The team blog for Microsoft Excel and Excel Services.
Help us make Excel 2007 faster …

Currently, the Excel development team is spending a lot of time tuning Excel 2007’s calculation performance to make it as fast as possible.  Given the near-infinite variety of things we see people build in Excel, we are always looking for good examples of workbooks that are calculation-intensive to help us compare Excel 2007’s calculation performance with previous versions’ performance on real-world files that matter to customers.  At some point last week it dawned on me that some of the Excel 12 blog readers might be in a position to help (given the number of comments and emails I have had from folks interested in or concerned about calculation and function speed).  So I’d like to appeal to folks to send in calculation-intensive workbooks they would like us to use as part of our performance tuning exercise.

Specifically, we are looking for workbooks that take some time to calculate (say anything over 5 seconds).  Other than that, pretty much anything goes with respect to features used in the product.  (Please note that some features, like linked workbooks, UDFs, database queries, etc. may require more than just the file.)  Of course, anything submitted will be treated as 100% confidential and used only for calculation performance testing.  Folks can either email the files to me (with any explanations necessary) using this link: http://blogs.msdn.com/excel/contact.aspx, or if email will not work, we can sort out other arrangements.  Once we have the workbooks, we baseline their performance in earlier versions of Excel and then run them on set of test machines (different CPU and memory configurations) using Excel 2007 and compare the results.

The Office marketing group has made some prizes available, so the person that submits the longest-calculating model will get an interesting prize, as will the person that submits the model that exercises the widest range of Excel’s calculation features.  Double points for using more than 30 functions in a model or for using array formulas.  Thanks in advance to anyone that sends in workbooks. 

PS Since this post has a limited lifespan, I will probably remove it at some point in the future.

PSS Updated to include email link directly in post

Posted: Tuesday, February 28, 2006 4:35 PM by David Gainer

Comments

Spence Lloyd said:

OK, so where do we send the files?
# February 28, 2006 11:52 PM

furu said:

Spence, there is "Email" link on the left -->
# March 1, 2006 1:41 AM

Brandon Bloom said:

This post is interesting, so when the offer "expires", please do not remove the post. Instead prefix/suffix the post with an experation notice.
# March 1, 2006 2:50 AM

dino_hsu_1019 said:

I still can't find David's Email or "Email" link, can you specify more clearly? tks.
# March 1, 2006 3:07 AM

Alan Hulme said:

We do a lot of data analysis using Visual Basic in Excel 2003 and have just compiled a test spreadsheet which is representative of the type of data analysis we do. Our analysis will be getting more complicated and we've just run this on a sample of PCs from an old Pentium II through our current PCs (3GHz Pentium IV's) to one of our teams personal dual core AMD 3800. We get times from 36 minutes through to about 4 minutes. Any improvement you can give via the next Excel release with 64 bit/multi core/visual basic so much the better. I'll email our test file in.
# March 1, 2006 3:13 AM

Alan Hulme said:

Sorry where's the email link to send the test file to ?
# March 1, 2006 3:14 AM

Jordan Windebank said:

I applaud the fantastic efforts are being undertaken to make Excel 2007, and Office 2007 in general.

Spence, Alan - The email link is in the right hand column.

http://blogs.msdn.com/excel/contact.aspx

Regards,
Jordan
# March 1, 2006 3:40 AM

David Gainer said:

Hi - Spence, dino_hsu, Alan, others, here is the link:

http://blogs.msdn.com/excel/contact.aspx

Or you can click the "Email" link in the "This Blog" box in the upper right of the page.

Let me know if that doesn't work.
# March 1, 2006 3:45 AM

Hein Rutjes said:

David, the Email link on the right (@ This Blog section) shows a contact form which doesn't allow me to send files.
# March 1, 2006 3:50 AM

David Gainer said:

Hein, the contact form will send me an email which I can then use to get in touch with you and sort out the best way to get the files transfered.  

Thanks for your help, all!
# March 1, 2006 3:53 AM

Hein Rutjes said:

Using named ranges becomes very sluggish after adding a lot of them (>5000). This is not so much true for
worksheets but especially for Range() lookups in VBA code. Furthermore, clicking the Name Box on the top left lets you waits a couple of seconds before
the name can be actually edited. We did some tests and found that Excel uses a linear search algorithm for finding named ranges, which meant that
finding names starting with 'A' is very fast and with 'Z' is very slow. Copying sheets also slows down considerably even when the worksheet is empty.
Creating and deleting names is also affected, especially when having to delete all names from a workbook and recreate them. We also found that when copying
a chart object from one workbook to another, all names are copied as well, which slows things down and creates a very large output workbook.
# March 1, 2006 3:53 AM

Charles Williams said:

Given the big grid increase to a million rows one major speed improvement you could do would be to add new versions of MATCH and VLOOKUP,HLOOKUP (FMATCH etc) which perform faster than the current versions for exact matches on unsorted data. The speed improvement would come from storing the row number of a successful match, and on the next execution check if that row number returns the 'correct' result then its done, otherwise do the usual linear search. You would probably need new versions of the functions because FMATCH could return a different row to MATCH when there were multiple matches to be found.
I have built functions using this technique and it works well.
# March 1, 2006 4:40 AM

John Greenan said:

Do you have a Reuters 3000 Xtra and a Bloomberg?  I can send spreadsheets which extract data from both and then take a minute or so to calculate (60 seconds from starting application.calculate to finishing).

I'm very interested to know how you actually test RTD performance under heavy load.  Can you describe that in a post?

I have a copy of beta-1, but the documentation is so sparse (non-existent) it's hard to actually see what to do to tweak calculations...

PS Blog is fantastic - just wish every Blog from Microsoft was as good.
# March 1, 2006 5:32 AM

dino_hsu_1019 said:

David, I have written my messages with a link to the four Excel files I provide for test.
# March 1, 2006 5:49 AM

Niek Otten said:

Hi David,

I have a 4 Mb workbook that batch processes 61 cases, mainly a matrix of 160 columns by 450 rows (actuarial projection)
It includes UDFs which cache intermediate results so next time calculation is faster.
1st calculation round on my 2.8 GHz Pentium 1 min 38 sec.
I couldn't get the batch to work reliably yet on the Office 12 beta.
Please let me know if you're interested; I certainly am!
# March 1, 2006 6:34 AM

Simon Murphy said:

David
Am I right in thinking VBA will not participate in multi-threading, and therefore performance is unlikely to be significantly better in Excel 2007?
In the work that I do its generally the VBA (well the VBA/Excel interface) that kills performance rather than native Excel functions.
Will you be posting about VBA at some point?
cheers
Simon
# March 1, 2006 9:24 AM

Bart Smit said:

David,

For failure report calculations about the plant i'm working (hot strip mill) i use array formulas. I can not send you the data but a lot of time is taken by checking / updating links in my other workbooks (sort of lookup at horizontal series of numbers to represent an asset) and vlookup formulas. Once the array formulas are build (5 arguments, 10.000 rows) they do not slow down the calculations. What i've noticed is that it's necessary to put my original data in the same workbook otherwise problems with the links will result a time out failure.
# March 1, 2006 10:22 AM

Anonymous Me said:

You could just create a dummy hotmail account to make this easier for people to send attachments...just looking to help you get more submissions :-)
# March 1, 2006 11:18 AM

Harlan Grove said:

Charles Williams's ideas for MATCH and the lookup functions would address Excel's lack of a linear interpolation function.

The other function that I'd recommend changing is OFFSET. Why is it volatile? I've read on Charles Williams's web site that INDEX isn't volatile, so it'd possible to rewrite

OFFSET(s!a,b,c,d,e)

as

INDEX(s!1:65536,ROW(s!a)+b+MIN(0,d+1),COLUMN(s!a)+c+MIN(0,e+1)):INDEX(s!1:65536,ROW(s!a)+b+MAX(0,d-1),COLUMN(s!a)+c+MAX(0,e-1))

which presumably makes the dynamic range reference nonvolatile. If this is so, why does OFFSET need to be volatile? INDIRECT I can understand, but not OFFSET.
# March 1, 2006 1:43 PM

Harlan Grove said:

Charles Williams's ideas for MATCH and the lookup functions would address Excel's lack of a linear interpolation function.

The other function that I'd recommend changing is OFFSET. Why is it volatile? I've read on Charles Williams's web site that INDEX isn't volatile, so it'd possible to rewrite

OFFSET(s!a,b,c,d,e)

as

INDEX(s!1:65536,ROW(s!a)+b+MIN(0,d+1),COLUMN(s!a)+c+MIN(0,e+1)):INDEX(s!1:65536,ROW(s!a)+b+MAX(0,d-1),COLUMN(s!a)+c+MAX(0,e-1))

which presumably makes the dynamic range reference nonvolatile. If this is so, why does OFFSET need to be volatile? INDIRECT I can understand, but not OFFSET.
# March 1, 2006 2:05 PM

David Gainer said:

Howdy all … first, to those of you that have already sent in files or links to places we can download your files, thanks very much.  We will work them into performance testing asap.  We really do appreciate your help.

Second, to all of those of you that posted comments along the lines of “how do I send the files”, my apologies for not being more clear.  To make this as simple as possible, please just send the files/instructions to access the files to xlfiles@microsoft.com.  That will route them straight to me, and I can take it from there.

Alan, your file sounds wonderful, looking forward to it.

Hein, Charles, thanks for the feedback.

John, I am not sure we have both.  Is there any chance you could just replace the feeds with RAND()?  Would the subsequent calcs be as long?  I will add testing RTD to my list of topics.

Niek, yes, we are very interested, please send it in.

Simon, VBA UDFs will not be multi-threaded, but any calcs before or after the VBA will.  If you want to send a sheet that we can test anyway, we would love to have it … helps us make sure your stuff will be as fast in 12, even if there are no speed ups on multiple core machines.

Bart, any chance that you can just replace the data with random values and send your sheet in?  What is interesting for us is the functions you have used and the way you lay them out, not necessarily the values that flow through those formulas.

Mike, thanks.  Sort of did what you suggested :)
# March 1, 2006 7:15 PM

Michael Griffiths said:

I'll try to submit a workbook once I fill in dummy data/etc.

It's sitting at 20 MB at the moment, and because I'm generating records dyamically it can be far larger (depending on the data source). I can generate 6,000 or 15,000 pieces of data when I hit Calculate (which is turned off for sanity reasons).

Time to calculate generally hovers around 5 minutes, deployed over a Citrix connection.

However, mose used functions are IF statements and VLOOKUPs. If you can speed up those, I'd appreciate it.
# March 2, 2006 11:44 AM

David Walker said:

Yes, why IS offset volatile?  Inquiring minds want to know.
# March 7, 2006 4:03 PM

David Walker said:

There are times when I need to MATCH or VLOOKUP something in a whole column, and I don't know if Excel knows to stop after the last used row (I presume it does).  Matches that are not found seem to take a whole lot longer than matches that are found in the last available row (or column).

# March 7, 2006 4:05 PM

Microsoft Excel 2007 (nee Excel 12) said:

A long time ago, I posted a request for calculation-intensive workbooks and received a lot of very valuable

# April 23, 2007 7:17 PM

Microsoft Excel Help us make Excel 2007 faster | Paid Surveys said:

PingBack from http://paidsurveyshub.info/story.php?title=microsoft-excel-help-us-make-excel-2007-faster

# May 29, 2009 12:17 PM

Microsoft Excel Help us make Excel 2007 faster | debt settlement program said:

PingBack from http://edebtsettlementprogram.info/story.php?id=23672

# June 15, 2009 1:13 PM
New Comments to this post are disabled
Page view tracker