Categorized | excel 2010
Tags |

Excel 2010 vs Excel 2007 : Basic Performance Test




My first impression of Excel 2010 was that it ‘felt’ faster than Excel 2007. So one of the first things that I did was to take it for a test ride. Now, I must admit, testing isn’t my forte. A long long time back when I was a developer, I used to write horrible code. I had a fancy for declaring variables like i1, i11, i111 (and so on and so forth) and seldom wrote comments that could explain the flow. My project manager was a religious man to start with and my actions only strengthened his faith in the almighty. Those were to good old days I tell you … you shipped over the very first version which compiled successfully and labelled it beta or 2.0143 or something. The first feedback from the client typically had comments like “when we opened the application it gave an ‘Application Error 300xAABEC00x’ and crashed” or the “invoice form goes blank every time you click the submit button“. The next shipment was labelled 2.945 (or something closer to the whole number, indicating that we were getting better). And on and on it went till such time that the client was either satisfied or exhausted (whichever was earlier).

Excel 2010 vs. Excel 2007

Getting back to the topic at hand, we measured performance on four parameters:
1. Time to create pivot tables
2. Time to create charts
3. Time to fill a range with numbers
4. Time to fill a range with formulas

All these tests were carried out on unsaved workbooks so that any interference from the file format could be eliminated.

(Disclaimer: The tests weren’t done ‘scientifically’ and hence the results may be taken as indicative and not conclusive. Run times will vary based on hardware and software configurations.)

Excel 2007 vs. Excel 2010 – Create a Pivot Table

2007 – 12.08 sec
2010 – 10.38 sec

excel-2010-excel-2007-pivot-table""
I used the sample code given below to create a set of pivot tables, each one on a new sheet. Excel 2003 could not included in this particular test since the code that used to create a pivot table in that version would be a little different from the ones used for Excel 2007 and 2010. Excel 2010 had an average run time of 10.38 seconds which was around 14% lower than Excel 2007. Improvement? Yes. Earth shattering? No.

1
2
3
4
5
6
7
8
9
10
11
Sub create_pivot()
t = Timer
For i = 1 To 100
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R3457C6", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:=ActiveSheet.Cells(1, 1), TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion12
Next i
MsgBox Timer - t
End Sub

Excel 2007 vs. Excel 2010 – Filling up a Range with Numbers

2003 – 14.86 sec
2007 – 32.57 sec
2010 – 10.86 sec

excel-2010-excel-2007-comparison-fill-range-with-number

The most significant improvement in performance surprisingly came from the most mundane area – filling up a spreadsheet with numbers. Excel 2010 came first with a time of 10.86 seconds. It even left Excel 2003 behind, which came in second with a time of 14.86 secs. Excel 2007 came in last with a time of 32.57 seconds. Improvement – a full 119%.

1
2
3
4
5
6
7
8
9
Sub fill_range()
t = Timer
For i = 1 To 5000
For j = 1 To 10
Cells(i, j).Value = 10
Next j
Next i
MsgBox Timer - t
End Sub

Excel 2007 vs. Excel 2010 – Filling up a range with Formulas

2003 – 6.24 sec
2007 – 14.32 sec
2010 – 8.84 sec
excel-2010-excel-2007-comparison-fill-range-with-formulas

Another task where Excel 2010 does better than 2007 is when it comes to filling up a spreadsheet with formulas. Again the work that has been put in Microsoft in improving the performance is apparent. While Excel 2003 beats its successors at 6.24 secs, Excel 2010 is not too far behind at 8.84 seconds while Excel 2007 wallows in the dust at 14.32. Improvement – 38%.

1
2
3
4
5
6
7
8
9
Sub fill_formula()
t = Timer
For i = 1 To 500
For j = 1 To 10
Cells(i, j).FormulaR1C1 = "=SUM(10+20)"
Next j
Next i
MsgBox Timer - t
End Sub

Excel 2007 vs. Excel 2010 – Creating Charts

2007 – 29.62 sec
2010 – 10.74 sec

The final test was to measure the improvements in charting. We tested the application for its ability to create multiple charts. I would have loved to compare the results with those in Excel 2003 but then, as in the case of pivot tables, the code itself would have been slightly different and hence wouldn’t have allowed an apples-to-apples comparison. Here again, Excel 2010 trumps Excel 2007 by a wide margin of around 64%.
excel-2010-excel-2007-charting

Here’s the VBA code.

1
2
3
4
5
6
7
8
9
10
Sub create_chart()
t = Timer
For i = 1 To 10
    Range("A1:K4").Select
    Range("K4").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$K$4")
Next i
MsgBox Timer - t
End Sub

So well yes, there are improvements which help Excel 2010 score over Excel 2007 in all many areas. However if you were to compare it to Excel 2003, it still has some catching up to do. However comparisons that pit a version that supports 16,777,216 cells per sheet (Excel 2003) with the one that supports 17,179,869,184 cells per sheet (Excel 2010) (1024 times higher), as I’ve done in some of the tests above, are probably a bit too harsh on the later.


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Jon Peltier wrote:

    “…since the code that used to create a pivot table in that version would be a little [different] from the ones used for Excel 2007 and 2010.”

    Doesn’t matter one bit. If I’m the user, I don’t care about the code that runs behind the scenes, I care about how fast something happens.

    Or if you’re concerned, write 2003 code and run it in all three versions.

  2. Ajay wrote:

    @Jon – That’s true. But then who knows – the pivot table version change may mean (and I am blind guessing here) that greater functionality has been added (say for example the ability to work with slicers or the ability to accommodate a far bigger data set). I am assuming that the increase in pivot table creation time is a trade-off MS made ‘intelligently’. But then again, I would have been dead wrong now if I had said the same thing when Excel 2007 was launched.

  3. Martin wrote:

    The menu’s for Excel 2007 and 2010 are terrible. As a professional accountant I find both of these versions of Excel slow me down. Excel 2003 was the best version of Excel and I was a strong advocate for Excel up until the release of Excel 2007. If Excel does not return its menus to the original format I will look at alternatives to MS when Excel 2003 is no longer supported.

  4. krisha wrote:

    i like 2007 still :) )

  5. Sumeet wrote:

    I would love to learn VBA. I have just macro’s, but not gone back & edit them like you did with the number fmraot. That was cool.Thanks

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel