Excel 2007 Recalculates Slowly
by Jon Peltier
Friday, October 10th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In Poor Charting Performance in Excel 2007 and Excel 2007 Chart Performance – Revisited I showed results of chart redrawing tests that showed Excel 2007 to be about an order of magnitude slower than Excel 2003 when recalculating chart data and redrawing the charts.
Simon Murphy of Codematic and the Smurf on Spreadsheets blog wondered how much of the difference was related to chart redrawing itself, and how much was related to recalculation. To assess the difference, I fired up my two Virtual PC sessions, both running Windows XP SP2 with 1023 MB of RAM. One VM has Excel 2003 SP3 loaded, the other has Excel 2007 SP1. I ran four tests in each version of Excel, all with 100 columns, and with 1000, 2000, 5000, and 10,000 rows (0.1, 0.2, 0.5, and 1.0 million cells). I used this command in the Immediate Window of the VB Editor:
debug.Print now, timer : for i=1 to 100 : activesheet.calculate : _
next : debug.Print now, timer
This recalculates the sheet 100 times, outputting the time (date and time as well as elapsed seconds) before and after the recalc. The average recalc time consists of the difference in these times, divided by 100. The test results are available in Recalc2003v2007.csv. I summarized the data in the following pivot table.
The chart below clearly shows that recalculation in Excel 2007 is slower than in Excel 2003, taking anywhere from 23% to 32% longer.
While Excel 2007’s recalculation is slower than Excel 2003’s, the main effect slowing down chart redrawing in Excel 2007 is not due to the small difference in recalculation. The order of magnitude difference in chart redrawing must be related to the new Office shapes used to construct charts in Excel 2007.
Related Posts:
- Poor Charting Performance in Excel 2007
- Excel 2007 Chart Performance – Revisited
- Chart Redrawing Performance of Excel 2003 and 2010
- BonaVista Releases Chart Tamer
- Back in the Swing
- I Keep Saying, Use Bar Charts, Not Pies
- Introducing Control Charts (Run Charts)
- Miscellanea for 8 May 2009
- Political Pie Charts
- Bad Graphics – Funnel Chart
Posted: Friday, October 10th, 2008 under Excel 2007.
Comments: 3
Comments
Comment from Simon
Time: Saturday, October 11, 2008, 6:06 am
Thanks for checking that out Jon.
Comment from Charles Williams
Time: Tuesday, November 11, 2008, 4:05 am
See http://www.decisionmodels.com/VersionCompare.htm for a comparison of Excel calculation speed with a range of real-life workbooks.
Comment from Jon Peltier
Time: Tuesday, November 11, 2008, 7:42 am
Charles -
Thanks for the link to your extensive study. Your recalculation results, showing an improvement of 2007 over 2003 is more valid than my simplistic test. I’m sure testing within a VM has skewed my observations.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.