This post looks at the effects on the performance of Conditional Formats of:
- Whether the cells containing the conditional formats are visible or not
- Screen Refresh
- Excel 2007, Excel 2010 and Excel 2013
The workbook I am using is called (with stunning originality) FormatConditionsB.xlsb, and you can download it from my Skydrive.
It contains 1.9 million Rand() formula in A1:Z72858, and each of these cells has 3 conditional format rules:
There are 2 worksheets: Formats and Empty
The workbook also contains the MicroTimer api code for high resolution timing and 5 subs, Testing 1 through 4 and testscroll1.
The subs typically set calculation mode, screenupdating and enableformatconditionscalculation, time a calculation and then time a screen update.
For example here is the code for Testing1:
Sub testing1() Dim osht As Worksheet Dim dtime As Double Application.Calculation = xlCalculationManual Worksheets("Formats").Activate Set osht = Worksheets("Formats") Application.ScreenUpdating = True osht.EnableFormatConditionsCalculation = True dtime = MicroTimer Application.Calculate dtime = MicroTimer - dtime Debug.Print dtime dtime = MicroTimer Application.ScreenUpdating = True dtime = MicroTimer - dtime Debug.Print dtime End Sub
Timings with different sheets visible.
The workbook opens with the formats sheet visible.
If you click the Empty tab you instantly see the empty sheet.
But if you then switch back to the Formats sheet there is a noticeable delay of about a second before the screen refreshes. Similarily pressing Page Up takes just over a second before the screen refreshes.
This is because Excel re-evaluates the conditional formats for the visible cells on the active sheet at each screen refresh.
Prssing F9 to recalculate the 1.9 million RAND() formulas with the Formats sheet visible takes 2.8 seconds, but with the Empty sheet visible it takes 0.2 seconds- again its the evaluation of the visible conditional formats that takes the time.
Conditional Formats are not directly evaluated by a calculation.
Running the Testing Subroutines
Here are the timings in seconds for running Test1 through Test4, with the Formats sheet visible.
The conclusions of this test are:
Excel 2010 and Excel 2013 are noticeably faster than Excel 2007.
- Turning off screen updating is the big winner
- Switching off EnableFormatConditionsCalculation is only worthwhile if ScreenUpdating is true
- Switching off EnableFormatConditionsCalculation is much less effective than switching off ScreenUpdating
- Although Refresh looks very fast in Excel 2013 it actually just postpones the refresh to after the VBA has finished, so in fact its not faster.
I then repeated the tests, but with the Empty sheet visible rather than the Formats sheet:
This completely avoids the refresh evaluation of the conditional formats and the times are comparable to the first set of tests with Screen Updating False.
I also tried repeating the tests with the Formats sheet active but hidden behing the VBE window.
The timings were virtually the same as with the Formats sheet visible.
So its the refresh of the conditional format cells within the activesheet window that uses the time, even if its hidden behind some other window.
I also ran TestScroll1. This times the effect of a complete scroll of the conditional formats window.
As you can see the scroll times are comparable to the refresh times in the first set of tests, except for Excel 2013.
But the Excel 2013 refresh timings in the first test are cheating because the refresh actually takes place after the VBA sub has ended.
Range.Calculate and Range.CalculateRowMajorOrder
If you use Range.CalculateRowMajorOrder on a single cell (or a large block of cells) it takes about 1.4 seconds – the same time as a scroll/screen refresh.
But Range.Calculate takes almost exactly twice as long – looks like it causes 2 screen refreshes not one!
Seriously Slow Conditional Formats
If you want to play with a workbook containing some seriously heavyweight conditional formats you can download ConditionalFormatsC.xlsb
This has 132K formulas =INT(RAND()*1000) in A1:V6000 and each cell has a single formatting rule to colour orange duplicated values in A1:V6000. (well of course they all turn orange).
With the Formats sheet visible pressing F9 to recalculate takes about 40 seconds.
And it looks like evaluating the conditional formats is all single-threaded: no advantage from multiple cores!
But with the Empty sheet visible F9 takes 0.03 seconds.
- Heavy conditional formatting can be slow
- Conditional Format evaluation is single-threaded
- EnableFormatCondtionsCalculation is not very useful
- Evaluation of conditional format rules takes place at screen refresh time rather than calculation time
- Only the conditional format rules for cells that are shown on the active window(s) get evaluated
(large screens will be slower than small screens and zoom out slows you down!)
- ScreenUpdating=false works well, but the final refresh time will occur when the Sub is exited.
- Using UDFs in conditional formats is probably not a good idea
- The interaction of VBA and conditional formats looks buggy
- Excel 2013 and 2010 are faster than 2007 for Conditional Formats
2 other bugs with conditional formats have been reported, but I don’t know if they have been fixed in Excel 2013:
- Opening a file created in Excel 2003 with Excel 2007 could make the conditional formats fail to refresh unless you manually set EnableFormatConditionsCalculation=true
- Repeated copy-pasting Conditional formats in Excel 2007 duplicated the conditional formatting rules so that large numbers of rules were created.