Excel Screen-updating Performance Comparison.

The time that Excel takes to refresh the screen display can be a significant factor in overall processing speed.

Things that affect screen updating Time

• Excel only updates the visible part of the Excel windows – changing Zoom, the size of the visible window or changing to a different resolution monitor will change screen updating time.
• The more cells that change within the visible windows the slower screen updating will be. So switching to an empty worksheet minimises screen update time.
• Complexity of formatting – the more work Excel has to do to format visible cells the slower screen updating will be. Using heavy conditional formatting rules is the most severe cause of slow screen updating.
• Updating the statusbar – frequently updating statusbar text can be slow.

My benchmarking workbook

The workbook contains one sheet (Sheet1) with 4895 formulas and 1000 constants. Sheet2 and Sheet3 are empty. The workbook does not contain any Conditional Formatting or VBA.

You can down load the workbook from here

For Range Calculate timings select Sheet1!A1:BE89 and then View – Zoom to selection so that all the 5073 cells are visible.

Excel should be in Manual calculation mode.

Screen Updating times for XL 2010, XL 2013, and XL 2021

Why are screen updating times so different by Excel Version? Some possible explanations:

In early versions of Excel parts of Excel’s rendering engine were coded in Assembler so as to maximise performance. As more function was built-in to the engine and Excel got supported on multiple end-points (Windows, IOS, Android) the use of Assembler became impractical and it became necessary to migrate as far as possible to a common cross-platform code-base.

But these changes had a drastic impact on screen-updating time, and the team decided that the only way to minimise this slow-down was to minimise the number of times the screen was updated. This turns out to be a complex task with potentially unfortunate side effects. Long-running tasks (calculation, VBA etc) running on the main Excel thread tended to show crazy looking or empty screens: Excel had cleared screen components but not yet redrawn them.

Excel 2010 has not fully implemented the minimisation of the number of screen redraws: screen update time is large.

Excel 2013 and 2021 have implemented minimising screen redraws.

I think Excel 2021 has fixes for some of the screen updating glitches, and also some features require additional work in the screen refresh, so 2021 is slightly slower than 2013.

Benchmarking screen updating time

I use 2 different methods to isolate and measure screen updating time. Both methods require the use of the Windows high-resolution timer. See https://fastexcel.wordpress.com/2011/05/31/a-quantum-of-time/

The Range Calculate method

The VBA Range Calculate method does a single threaded calculate of a range. If your VBA code switches ScreenUpdating to false then calls a timed Range.Calculate the time taken will exclude screen refresh time. Subtracting that from the Range Calculate time with ScreenUpdating true gives you screen refresh time. (Note you should be in Manual calculation mode. to avoid including the automatic recalc after Range Calculate).

First select and make visible the range of cells for which you want to measure screen update time then use the timed Range Calculate on the selection.

The CalculateFull method

The VBA Calculate Full method forces Excel to calculate every formula in the active workbooks.
Activate the worksheet that contains the formulas you want measure screen update time for. Use the high-resolution timer to measure the full calction, then switch to a completely empty sheet and repeat the Calculate Full timing. The difference between the 2 timings is the screen refresh time.

With this method you do not need to be in manual calculation mode or switch ScreenUpdating to False, but you need to do several successive full calculations to allow the multi-threaded recalc to self optimise.