Excel 2007 Recalculates Slowly

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.