Excel Versions Screen Test (Updated): how fast is Screen Updating?

Gurs has an interesting benchmark he has been running on various systems and Excel versions over the years. His results seem to show a massive performance decline in later Excel versions.

Looking at his benchmark and its VBA code you can see that has ScreenUpdating ON and repeatedly calculates in Automatic mode. Each calculation triggers some RANDBETWEEN functions with a number of dependent cells.
Gurs does not want to speed up his benchmark because that would destroy his historic speed comparisons.

But the problem is that a large portion of the time in his benchmark is taken by screen updating, and so his benchmark results vary significantly depending what part of the worksheet is actually visible on the screen, and hence how many visible cells get refreshed at each calculation.

Running Gurs Benchmark with different Excel versions but on the same PC

I ran Gurs benchmark on my desktop PC with Excel 2003 to Excel 2013., with a constant screen area visible (rows 1:118 and columns A:BF). Since I cannot install Excel 2016 on the same PC as previous versions without causing unwanted problems I used a VM on my desktop and also ran the benchmark on my Surface Pro 3.

gurs

This shows loops per second (higher is better) with Screen Updating On and Off, the ratio of OFF to ON and the ratio to Excel 2003.

The results for Excel 2003, 2007 and 2010 show that:

  • Excel 2007 and 2010 are slower than Excel 2003
  • Screen Updating On is 15-20 times slower than Screen Updating Off

But something changed with Excel 2013!

  • Screen Updating ON gets significantly faster
  • Screen Updating OFF gets significantly slower

My visual impression is that Excel 2013 does not try to update the screen on every iteration when the update frequency is high, and this is the reason for the change.
And although I don’t have an exact comparison for Excel 2016 it looks comparable to Excel 2013.

Creating a Pure Screen Updating Benchmark

So my next step was to try to create a pure screen updating benchmark.

In column A I put 28 =RAND() formulas, and then in columns B:V i put very simple formulas that linked back to column A.

screentest1

This gives me 616 cells that will change on each calc, and its easy to keep all 616 cells visible on the screen. The VBA code times 10000 calculations with Screen Updating Off and again with Screen Updating Nn. The difference between these is the time taken by the screen updating.


Sub ScreenTest()
'
' time screenrefresh
'
Dim i As Long

Dim tStart As Double
Dim tEnd As Double
Dim tScreenOn As Double
Dim tScreenOff As Double

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
tStart = MicroTimer
For i = 1 To 10000
.Calculate
Next i
tEnd = MicroTimer
tScreenOff = (tEnd - tStart)
.ScreenUpdating = True
tStart = MicroTimer
For i = 1 To 10000
.Calculate
Next i
tEnd = MicroTimer
tScreenOn = (tEnd - tStart)
End With
    MsgBox "Off " & Int((tScreenOff) * 1000) & _
" On " & Int(tScreenOn * 1000) & _
" Diff " & Int((tScreenOn - tScreenOff) * 1000) & " Millisecs"

End Sub

The timing results for this test of screen updating are:

screentest3

For this (very extreme) benchmark Excel 2013 screen updating is about 180 times faster than previous versions.

But with screen updating turned off Excel 2013 runs this benchmark 5 times slower!

I think what has happened is that Excel 2013 is still doing all the work to update and format the values, but has added a check to limit the frequency of requests to Windows to actually repaint the screen.

Note: this does NOT mean that Excel 2013 runs everything 5 times slower: this is a very extreme case benchmark.

 

Advertisements
This entry was posted in Calculation, Formatting, Uncategorized, VBA. Bookmark the permalink.

8 Responses to Excel Versions Screen Test (Updated): how fast is Screen Updating?

  1. Eric Nolan says:

    Thanks. That was pretty interesting. Anyone who does Excel VBA work professionally should be aware that screen updating and iterating through ranges one cell at a time have a ridiculously huge impact on performance. I am most interesting in the data for tests where screen updating is off. It is a bit shocking to me that Excel 2003 would be FIVE TIMES slower at this.

    The theories that screen size and more complex formatting impact performance when screen updating is on sound good and that can explain the drop off in performance in later versions. It doesn’t explain the problems with screen updating off. In the linked forum there were some theories as to why the new versions might be slower. One person suggested that Gurs’ test used full column ranges and due to the huge increase in how many cells this is it can slow things down a lot but Gurs’ said that this isn’t the case. Another comment suggested that changes to functions like RAND might cause this but if this is the case I would consider it a significant issue. The old version of RAND would have to have very severe functional problems to excuse the replacement being five times slower.

    Another comment suggested that it was good practice to recode your macros for a new version but in my experience this is not usually done. Business applications that work are expected to keep working and they are very reluctant to crack something open and change it with the inherent risk that some hard to detect bug will cause a hard to detect error in the output. I’ve worked on complex solutions that have quite long run times. One quarterly report generator took multiple hours to finish the job. If these things take five times longer to run when a company finally upgrades after sitting on Office 2003 for 10 years the client is not going to be happy at all. Luckily for me (and Microsoft I think) I have very rarely seen this happen and in every case I can remember it was due to calculations which used entire columns (eg: sum(A:A)) instead of just the part that was in use.

  2. fastexcel says:

    I have updated the timing results to show the timings with screen updating on and off, and to add more comments on the results. As you say, this is certainly not a typical use case.

  3. GollyJer says:

    Does anything change if using an xlsb file when available?

    • fastexcel says:

      xlsb is just the external file format: once the file is opened everything in Excel memory is the same.
      So no it does not change anything.

      • GollyJer says:

        I haven’t worked with Excel in a while but I remembered this from when I did.

        “In addition to the new XML-based file formats, Office Excel 2007 also introduces a binary version of the segmented compressed file format for large or complex workbooks. This file format can be used for optimal performance and backward compatibility.”

        Using xlsb always improved performance for my projects at the time.

  4. fastexcel says:

    XLSB is faster to open and save than XLSX. Apart from that there is no performance gain.

  5. Alan Jones says:

    Good post. I am curious about Excel 2016. I have often thought that Excel 2016 seemed slower, but hard for me to pinpoint where. Newer hardware but some things would take longer to display or calculate. Anyone else seen this on Excel 2016?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s