Excel 2013 v Excel 2010 Speed Test

Posted on October 27, 2015 by Dick Kusleika

There are a lot of good comments on the Excel 2013 Is Unreasonably Slow post, but none of them worked for me. Even a generous offer from keepItCool, but I can’t send the problem child due to proprietary information. So I’m trying to demonstrate the problem in a simpler fashion.

Sub TestTimes()
    
    Dim wb As Workbook
    Dim i As Long
    Dim clsTimer As New CTimer
    
    On Error Resume Next
        Kill Environ$("TEMP") & "\TemplateInsert.xlsx"
    On Error GoTo 0
    
    clsTimer.StartCounter "Version: " & Application.Version & Space(1) & Format(Now, "yymmddhhmmss")
    Set wb = Workbooks.Add
    wb.Sheets(1).Range("A1").Value = "Insert Template"
    wb.SaveAs Environ$("TEMP") & "\TemplateInsert.xlsx"
    wb.Close
    clsTimer.Split "Create template"
    
    For i = 1 To 5
        Set wb = Workbooks.Add(Environ$("TEMP") & "\TemplateInsert.xlsx")
        clsTimer.Split "Insert template " & i
    Next i
    
    clsTimer.StopCounter
    
End Sub

Sub TestTimes()

Dim wb As Workbook

Dim i As Long

Dim clsTimer As New CTimer

On Error Resume Next

Kill Environ$("TEMP") & "\TemplateInsert.xlsx"

On Error GoTo 0

clsTimer.StartCounter "Version: " & Application.Version & Space(1) & Format(Now, "yymmddhhmmss")

Set wb = Workbooks.Add

wb.Sheets(1).Range("A1").Value = "Insert Template"

wb.SaveAs Environ$("TEMP") & "\TemplateInsert.xlsx"

wb.Close

clsTimer.Split "Create template"

For i = 1 To 5

Set wb = Workbooks.Add(Environ$("TEMP") & "\TemplateInsert.xlsx")

clsTimer.Split "Insert template " & i

Next i

clsTimer.StopCounter

End Sub

I create a template and insert it into a new workbook five times. This is more or less what my other code is doing. Actually it’s quite a bit less and the templates in the other code are a lot fatter. But I think it demonstrates the point.

Split	Excel 2013 (Home)	Excel 2010 (Home)	Excel 2013 (Office, Local)	Excel 2013 (Office, Network)
Start	0.75	0.55	0.66	2.82
Create template	257.68	161.60	445.96	562.40
Insert template 1	449.85	246.10	739.31	927.03
Insert template 2	638.71	345.13	1,058.17	1,292.53
Insert template 3	832.92	429.95	1,367.04	1,716.61
Insert template 4	1,030.99	517.06	1,696.34	2,109.36
Insert template 5	1,247.35	611.32	2,023.30	2,467.81
End	1,247.63	611.70	2,024.43	2,523.20

Excel home is my machine. I have both versions installed. The office times are where my original code runs in production. The “Local” times are when the code is on the desktop and the “Network” time is when the code is on a server.

There’s a lot I’m not happy about here. If I could cut the 2013 times in half, I would be not-unhappy. Note also that I’m not protecting or unprotecting anything – a known change in 2013 that slows things down.

Give it a try, if you like, and let me know what your times are. The home hardware is Intel i7 860 @ 2.8Ghz with 8GB RAM / Windows 7 64 bit, Excel 32 bit.

You can download TemplateInsertTimeTest.xlsm

17 thoughts on “Excel 2013 v Excel 2010 Speed Test”

Daniel says:

October 28, 2015 at 12:20 am

I’d like to try it, but where do I get

Visual Basic

CTimer

1

CTimer

? Is it from some external reference, or is it from your own toolbox?
André says:

October 28, 2015 at 5:20 am

Start 1
Create template 579
Insert template 1 865
Insert template 2 1135
Insert template 3 1422
Insert template 4 1722
Insert template 5 2023
End 2026

__
MS Office Professional Plus 2013

__
Intel(R) Xeon(R) CPU E5-2609 v2 @2.50 GHz
8GB RAM
André says:

October 28, 2015 at 7:35 am

Daniel,

The example availabe for download has all you need to run the test.

Ctimer is in the class module.
Dick Kusleika says:

October 28, 2015 at 7:38 am

Thanks @Andre

@Daniel, there’s a link to download the file at the bottom of the post. It has the timer class and everything else you need.
Hui... says:

October 28, 2015 at 9:06 am

But I feel so much safer using 2013
[/Sarcasm]

Hui…
snb says:

October 28, 2015 at 10:47 am

XP, Excel 2010

Version: 14.0 20151028164642 Start 0
Version: 14.0 20151028164642 Create template 113
Version: 14.0 20151028164642 Insert template 1 171
Version: 14.0 20151028164642 Insert template 2 223
Version: 14.0 20151028164642 Insert template 3 274
Version: 14.0 20151028164642 Insert template 4 327
Version: 14.0 20151028164642 Insert template 5 380
Version: 14.0 20151028164642 End 380
Charlie says:

October 28, 2015 at 12:51 pm

@snb – you convinced me – time to go back to Win XP :)

@André Thanks for the download hint, I had just copied & pasted the code example…

Version: 16.0 1510281945   Start               3,111
Version: 16.0 1510281945   Create template   313,047
Version: 16.0 1510281945   Insert template   535,847
Version: 16.0 1510281945   Insert template   761,701
Version: 16.0 1510281945   Insert template   978,517
Version: 16.0 1510281945   Insert template  1217,718
Version: 16.0 1510281945   Insert template  1453,015
Version: 16.0 1510281945   End              1461,480
                                          
Version: 14.0 1510281947   Start               5,533
Version: 14.0 1510281947   Create template   173,717
Version: 14.0 1510281947   Insert template   240,832
Version: 14.0 1510281947   Insert template   320,733
Version: 14.0 1510281947   Insert template   392,659
Version: 14.0 1510281947   Insert template   472,248
Version: 14.0 1510281947   Insert template   542,474
Version: 14.0 1510281947   End               549,205
                                          
Version: 14.0 1510281950   Start               2,275
Version: 14.0 1510281950   Create template   141,196
Version: 14.0 1510281950   Insert template   177,943
Version: 14.0 1510281950   Insert template   226,328
Version: 14.0 1510281950   Insert template   272,359
Version: 14.0 1510281950   Insert template   312,766
Version: 14.0 1510281950   Insert template   346,100
Version: 14.0 1510281950   End               349,242

Version: 16.0 1510281945 Start 3,111

Version: 16.0 1510281945 Create template 313,047

Version: 16.0 1510281945 Insert template 535,847

Version: 16.0 1510281945 Insert template 761,701

Version: 16.0 1510281945 Insert template 978,517

Version: 16.0 1510281945 Insert template 1217,718

Version: 16.0 1510281945 Insert template 1453,015

Version: 16.0 1510281945 End 1461,480

Version: 14.0 1510281947 Start 5,533

Version: 14.0 1510281947 Create template 173,717

Version: 14.0 1510281947 Insert template 240,832

Version: 14.0 1510281947 Insert template 320,733

Version: 14.0 1510281947 Insert template 392,659

Version: 14.0 1510281947 Insert template 472,248

Version: 14.0 1510281947 Insert template 542,474

Version: 14.0 1510281947 End 549,205

Version: 14.0 1510281950 Start 2,275

Version: 14.0 1510281950 Create template 141,196

Version: 14.0 1510281950 Insert template 177,943

Version: 14.0 1510281950 Insert template 226,328

Version: 14.0 1510281950 Insert template 272,359

Version: 14.0 1510281950 Insert template 312,766

Version: 14.0 1510281950 Insert template 346,100

Version: 14.0 1510281950 End 349,242

I should add that this is all happening in VirtualBox VMs. The host is a Thinkpad T430s with i5-3320 CPU @ 2.6 GHz and 16 GB RAM (the VMs have 4 GB RAM).

Daniel says:

October 28, 2015 at 2:00 pm

(PS replace the commas with dots, I have a non-EN locale…)
Daniel says:

October 28, 2015 at 2:04 pm

And, sorry, one more thing, don’t mean to pollute these comments (but can’t edit my comment either):

The first two runs are on Windows 7 64-bit, the last one is on Windows XP 32-bit. Office is 32-bit in all cases. And the host is running Ubuntu 15.10 64-bit.
Dick Kusleika says:

October 28, 2015 at 3:28 pm

No worries about the comments, Daniel. Thanks for all the good data.

Other stuff

Split	Excel 2010 “Two”	Excel 2013 “Two”	Excel 2013 “Three”	Excel 2010 “Three”
Start	0.15	1.03	0.11	1.18
Open template 1	220.58	566.98	167.20	126.28
Open template 2	404.51	855.08	324.48	211.55
Open template 3	624.12	1,124.41	489.23	304.83
Open template 4	781.47	1,433.73	656.74	391.59
Open template 5	950.48	1,717.69	828.01	468.29
End	999.50	1,796.36	828.35	468.63

Sub TestTimesTwo()
    
    Dim wb As Workbook
    Dim i As Long
    Dim clsTimer As New CTimer
    
    For i = 1 To 5
        On Error Resume Next
            Kill Environ$("TEMP") & "\TemplateInsert" & i & ".xlsx"
        On Error GoTo 0
    Next i
    
    clsTimer.StartCounter "Two Version: " & Application.Version & Space(1) & Format(Now, "yymmddhhmmss")
    Set wb = Workbooks.Add
    wb.Sheets(1).Range("A1").Value = "Insert Template"
    
    For i = 1 To 5
        wb.SaveCopyAs Environ$("TEMP") & "\TemplateInsert" & i & ".xlsx"
        Workbooks.Open Environ$("TEMP") & "\TemplateInsert" & i & ".xlsx"
        clsTimer.Split "Open template " & i
    Next i
    
    wb.Close False
    
    clsTimer.StopCounter
    
End Sub

Sub TestTimesTwo()

Dim wb As Workbook

Dim i As Long

Dim clsTimer As New CTimer

For i = 1 To 5

On Error Resume Next

Kill Environ$("TEMP") & "\TemplateInsert" & i & ".xlsx"

On Error GoTo 0

Next i

clsTimer.StartCounter "Two Version: " & Application.Version & Space(1) & Format(Now, "yymmddhhmmss")

Set wb = Workbooks.Add

wb.Sheets(1).Range("A1").Value = "Insert Template"

For i = 1 To 5

wb.SaveCopyAs Environ$("TEMP") & "\TemplateInsert" & i & ".xlsx"

Workbooks.Open Environ$("TEMP") & "\TemplateInsert" & i & ".xlsx"

clsTimer.Split "Open template " & i

Next i

wb.Close False

clsTimer.StopCounter

End Sub

and

Sub TestTimesThree()
    
    Dim wb As Workbook
    Dim i As Long
    Dim clsTimer As New CTimer
    
    clsTimer.StartCounter "Three Version: " & Application.Version & Space(1) & Format(Now, "yymmddhhmmss")
    For i = 1 To 5
        Workbooks.Add.Sheets(1).Range("a1").Value = "InsertTemplate"
        clsTimer.Split "Add template " & i
    Next i
    
    clsTimer.StopCounter
    
End Sub

Sub TestTimesThree()

Dim wb As Workbook

Dim i As Long

Dim clsTimer As New CTimer

clsTimer.StartCounter "Three Version: " & Application.Version & Space(1) & Format(Now, "yymmddhhmmss")

For i = 1 To 5

Workbooks.Add.Sheets(1).Range("a1").Value = "InsertTemplate"

clsTimer.Split "Add template " & i

Next i

clsTimer.StopCounter

End Sub

Well “Three” looks promising. Of course in the real world I’m not simply adding a value to a range. So I’ll have to see if creating a template and adding a workbook based on that template five times is more or less than creating five workbooks from scratch.

Alan Hutchins says:

October 30, 2015 at 8:17 am

How refreshing to see the reality. Excel 2013 looks nice, but compared to Excel 2010 is appallingly slow on my work machine. dell Latitude E7240 i5 with 4GB RAM on Windows 7 32 bit.

1 step forward and about 7 backward.

Does a 64 bit machine wit 32 bit Excel 2013 run any better?
Best Excel Tutorial says:

October 30, 2015 at 1:25 pm

Nice test! I thought Excel 2013 will be better. I’m shocked. That means that’s newer doesn’t mean better for sure.
Pingback: Excel Roundup 20151102 « Contextures Blog

Another approach to speed up your code ?

Public c00 As String

Sub TestTimes()
  Dim clsTimer As New CTimer
  sn = Array(Environ("TEMP") & "\TemplateInsert.xlsx", "Version: " & Application.Version & Space(1) & Format(Now, "yyyymmdd hhmmss"), "Insert Template", "Create template")
  c00 = ""
    
  On Error Resume Next
    Kill sn(0)
  On Error GoTo 0
    
  clsTimer.StartCounter sn(1)

  With Workbooks.Add
    .Sheets(1).Range("A1").Value = sn(2)
    .SaveAs sn(0)
    .Close
  End With
  clsTimer.Split sn(3)
    
  With GetObject(sn(0))
    For j = 1 To 4
      .Sheets.Copy
      clsTimer.Split sn(2) & j
    Next
    .Windows(1).Visible = True
  End With
  clsTimer.StopCounter
    
  Debug.Print c00
End Sub

Private Sub WriteToFile(ByVal sDesc As String)
  c00 = c00 & vbLf & Replace(Me.Description, ",", "_") & "," & Replace(sDesc, ",", "_") & "," & Me.TimeElapsed
End Sub

Public c00 As String

Sub TestTimes()

Dim clsTimer As New CTimer

sn = Array(Environ("TEMP") & "\TemplateInsert.xlsx", "Version: " & Application.Version & Space(1) & Format(Now, "yyyymmdd hhmmss"), "Insert Template", "Create template")

c00 = ""

On Error Resume Next

Kill sn(0)

On Error GoTo 0

clsTimer.StartCounter sn(1)

With Workbooks.Add

.Sheets(1).Range("A1").Value = sn(2)

.SaveAs sn(0)

.Close

End With

clsTimer.Split sn(3)

With GetObject(sn(0))

For j = 1 To 4

.Sheets.Copy

clsTimer.Split sn(2) & j

.Windows(1).Visible = True

End With

clsTimer.StopCounter

Debug.Print c00

End Sub

Private Sub WriteToFile(ByVal sDesc As String)

c00 = c00 & vbLf & Replace(Me.Description, ",", "_") & "," & Replace(sDesc, ",", "_") & "," & Me.TimeElapsed

End Sub

Desk Lamp says:

November 3, 2015 at 9:30 am

My little experiment. I set it to iterate 50 times instead of 5 to give a more reliable measure and ran the code a few times to get an average. Then i ran the code with animations on and with them switched off (“Turn off unnecessary animations” in windows “Ease of Access Center”). This is with Excel 2013 (64bit).

With animations on (average end time): 12000
With animations off (average end time): 9300

There’s a 25% saving for you! I’ve always hated those animations.

And one other test just for my curiosity:

Application.Sceenupdating = FALSE (average end time):3800