XL 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).
You can also download a zipped XLS file called Version_Comparison.zip which contains the detailed results.

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.

• Workbook BR is too large to open for Excel's memory limits in Excel 97 and 2000 so I have assumed the same timing as Excel 2002.
• Workbooks BR and GL have more than 64K dependencies, so the status bar always shows "Calculate" in versions before Excel 2007.
• Workbooks GL and IN have been optimised for calculation speed, the other workbooks are largely unoptimised.
• Workbook IN is unusual because a recalculation takes much longer than a Full Calculation. This is probably because it makes heavy use of OFFSET to optimise calculation speed.
• Workbook PH has a very poor worksheet calculation sequence, which is why Excel 2002 calculates it much faster than earlier versions.

Calculation Times

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:

• XL 12 - Excel 2007
• XL11 - Excel 2003
• XL10 - Excel 2002
• XL9 - Excel 2000
• XL8 - Excel 97

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.
You can download this Variant Benchmark workbook to see how the time varies on your system, and to determine exactly how large the overhead is, and to derive the timing equation:

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 cells were either read one cell at a time (512 x 1 Cell) or in a single block of 512 cells (1 x 512 Cells).

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:

• Excel 12 SP2 has significantly improved VBA performance compared to Excel 12
• Excel 12 has a much higher overhead (almost 10 times larger) for writing cells than previous versions.
• The Excel 12 overhead for reading cells is also larger than previous versions.
• Writing cells back to Excel is much slower than reading cells
• When you need to read or write more than one cell it is much more efficient to do so in large a blocks.

You can download the detailed results workbook, which contains the individual times for each Excel version.