XL Version Timing Comparison
|Smart Recalculation||Evaluation Circumstances||Calculation Process||Dependency Trees|
|Controlling Calculation||"CALCULATE" in Status Bar||Calculation Methods||Calculating from VBA|
|Volatile Functions & Actions||User-Defined Functions||Repetitive Calculation||Version Timing Comparison|
Comparison of Timings for Excel Versions from Excel 97 to Excel 2007
It's interesting to see how Excel performance has changed over the last few versions.
This page compares calculation times, open
save close times, and the time it takes to transfer data
from Excel to VBA (Read) and from VBA to Excel (Write).
Excel 2007 Chart performance
You can also find comparisons of Excel 2007 and 2003 Chart performance in Jon Peltier's blog. His conclusion is that Excel 2007 Charts are about an order of magnitude slower.
Notes on the workbooks
I used 11 workbooks ranging in size and complexity to compare the timings across versions.
Each workbook was calculated 4 times, 2 Full calculations followed by 2 Recalculations in each Excel version. This was done to detect improvements caused by calculation chain reordering and multi-threaded calculation in Excel 2007.
The timings were all done on an AMD 4600+ dual core system (2.41 GHz) with 2 Gigabytes of RAM using Windows XP, using the FastExcel calculation timers.
For Excel 2007 the calculation timings were done with Multi-Threaded Calculation (MTC) both On and Off (MT On & MT Off) to see what improvement this made.
The Excel Versions are:
Apart from XL11 (Excel 2003) the calculation times show considerable improvement, although the times for individual workbooks vary more widely.
The detailed timings for the individual workbooks can be found here.
Open Close Save Times
Each workbook was opened, saved as a different name and then closed using each Excel version.
The timings were done by hand and therefore times less than a second are somewhat approximate.
For Excel 2007 a comparison was done of the 3 different file formats:
XLS - Excel 2003 compatible file format
XLSB - Excel 2007 Binary format
XLSX - Excel 2007 XML format
If you look at the details you will see that both the XLSB and XLSX formats (which are both Zip files) produce considerably smaller files than the XLS unzipped format.
Excel 12 is considerably slower than previous versions at opening workbooks, regardless of the format used, whereas Saveing and Closing times are comparable across versions.
VBA Read/Write times.
A major factor in the time taken by most VBA macros and User-defined functions is the time taken to transfer data between Excel and VBA. The most efficient way to do this is by assigning as large a block of cells as possible (a Range) to a Variant variable (Read) and then assigning the resulting 2-dimensional Variant Array back to the Range after it has been modified (Write).
The reason it is more efficient to read/write a block of cells in
one go rather than do it one cell at a time is because there is a
significant overhead associated with each Read and Write.
Time=Overhead + Time_per_cell* no of cells.
The Overhead and Time-per-cell are constants for each Excel version and amount of data in the cells.
Note that if you have the Google Office com-addin installed the times will be substantially larger.
The chart compares the times taken by each version of Excel to read
into VBA and write back to Excel 512 cells of data.
The timings for reading one cell at a time are dominated by the overhead, whereas the timings for reading 512 cells at a time are dominated by the time taken to transfer the data.
The results show that:
You can download the detailed results workbook, which contains the individual times for each Excel version.