Exploring Conditional Format Performance Part 1: What’s slow, whats buggy and whats faster!

Patrick wanted to know if I had any information on Conditional Format calculation and performance, and I have  not looked at it for several years, so here goes!

I have done a series of experiments, using Excel 2007, 2010 and 2013, to try and get some insight on what Excel is doing under the covers. Because there is a lot to cover I have split the post into 3 parts.

This first part covers a simple experiment to see when Conditional formats get executed.

Formatting versus Calculation.

What Excel shows you on the screen or in a printout is the formatted (rendered) version of the results of a calculation.
And because formatting/rendering is such a cpu-intensive process Excel has a lot of tricks to try and minimise the time used (and thats why using Application.Screenupdating=False should be used everywhere to speedup your VBA).

Conditional Formats often do both calculation and formatting, so you have got double the chance of things being slow!

Excel does not generally allow formatting to be part of the calculation chain because formatting occurs after the calculation has finished.
This is also true for conditional formatting, although it its not clear to what extent there is a separate calculation-of-conditional-formats step before the formatting step.

Excel dynamically formats (re-paints) only what you see on the screen.

To save time Excel only does final formatting for the part of the results you can see on the screen. (so large screens are slower than small ones, and zooming out a long way is slower!). When you have a lot of conditional formats this can cause very noticeable delays in scrolling a page up or down,

Conditional Formats can be Super-Volatile

Because of this dynamic repainting conditional formats are often executed even when no calculation occurs (for instance when you scoll up or down). So its not usually a good idea to embed a heavy calculation into a Conditional Format formula!

Lets start by looking at a very simple example that allows you to track when a condtional format gets executed. You can download the workbook FormatConditionsA.xlsb from SkyDrive. Note it contains VBA so will not run properly in the Excel Web App.

Test workbook FormatCondtionsA.xlsb

The workbook uses 3 cells and 2 VBA UDFs:

• Cell B2 contains a formula =D21 and has two conditional format rules – colour orange if =signal1(b2) and colour green if =signal2(b2).
  Signal1 and Signal2 are VBA UDFs that increment a calculation counter and show it in the immediate window. Signal1 returns TRUE if B2 is an odd number and Signal2 returns TRUE if B2 is an even number.
• Cell E2 contains 2 conditional format rules that directly check cell D21 for odd (orange) or even (green).
• Cell D21 contains a number which you can change to either odd or even to see the effect on the conditional formats.

To run the experiments open the workbook and press F11 to see the VBIDE, then press Ctrl G to View the immediate window.
Then arrange the Excel window and the VBE window so that you can see both of them, and make sure that you can see Row 2 through 21 of the Excel window.

Experiment 1: Automatic calculation mode, User-interface driven

Switch to Automatic Calculation mode.
Clear the VBE immediate window.
Select Cell D21 and increment the number by 1.

Both cell E2 and B2 should change colour, and the Immediate Window shows how many times the UDFs have been calculated.

With Excel 2013 I get a total of 10 executions of the UDFs! (5 of each) !!! (No, I have absolutely no idea why, thats got to be a bug.)

Excel 2007 and 2010 only do 4 executions (2 for each UDF).

Experiment 2: Manual calculation mode, User-interface driven

Now switch to Manual calculation mode, clear the immediate window and select D21.

Increment D21 by 1: the result is

• The UDFs are not executed (nothing in the Immediate Window).
• B2 and E2 stay the same colour.

Page Down and then Page Up (to refresh the Excel window):

• The UDFs are executed once.
• Cell E2 changes colour because it directly refers to cell D21 which is now Odd.
• Cell B2 has correctly NOT changed colour because the conditional format is driven by cell B2 itself, which has not yet changed because it has not yet recalculated.

Now press F9:

The UDFs are executed once and cell B2 changes colour.

Experiment 3: The effect of refreshing the screen with Page Up and Page Down

Now increment Cell D21 again so that the status bar shows Calculate.

Press page Up Page Down repeatedly: the immediate window shows that the UDFs execute each time the screen gets refreshed with Page Up.

Now Press F9 to recalculate:

• Excel 2013 Page Down Page Up does not execute the UDFs
• Excel 2010 and 2007 does execute the UDFs once for each Page Down Page Up, even though it does not need to.

Experiment 4: Recalculating but with conditional formats scrolled out of sight.

• Clear the immediate window.
• Scroll the Excel window so that row 15 is the first row showing
• Increment cell D21 by 1
• Press F9 to recalculate, or Ctrl/Alt/F9 to Full Calculate

The immediate window shows nothing: the conditional formats have NOT been executed and will not be until you Page Up to make them visible.

(Note: if you only scroll so that the first row is row 3 the conditional formats DO get exceuted: looks like Excel is using about a 12 row buffer!)

Conclusions from Experiment 1.

• Conditional formats are executed when the cell containing the conditional format gets repainted.
• Conditional Formats are not executed at a calculation unless they are on the visible prtion of the screen.
• Excel 2013 looks a bit over-enthusiastic in Automatic Calculation mode, but smarter in Manual Calculation mode than Excel 2007/2010.

In the next post I will explore what the performance impact of conditional formats is, and what is the impact of setting Application.Screenupdating=False and Worksheet.EnableFormatCondtionsCalculation=False.