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

This is the third in a series of Posts on Conditional Formats (see part 1 and Part2).

This post looks at the effects  on the performance of Conditional Formats of:

  • Application.Screenupdating
  • Application.EnableConditionalFormatsCalculation
  • Application.Calculation
  • 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:

CFEx3_1So thats 5.7 million 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
 Set osht = Worksheets("Formats")
 Application.ScreenUpdating = True
 osht.EnableFormatConditionsCalculation = True
 dtime = MicroTimer
 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.

So whats your experience with Conditional Formats?

This entry was posted in Calculation, Formatting, UDF, VBA and tagged , . Bookmark the permalink.

9 Responses to Exploring Conditional Format Performance Part 3: What’s slow, whats buggy and whats faster!

  1. Jeff Weir says:

    Charles: This is a great series. I recently put together a Project Costing Model in Excel 2010 that used Conditional Formatting to visually distinquish fte-based resources (mainly people) from non fte-based ones (mainly IT spend) and ended up stripping out the formatting because it really seemed to slow things down.

  2. fastexcel says:

    Thanks: I think the moral of the story is to be careful with CF – small amounts work well unless each rule is very heavy duty.

  3. Robert says:

    Hi, I downloaded your sheets and did all the experiments and it was excellent. Thanks!
    All in all I’m finding CF quite good in 2013 and I am using it extremely heavily: 40 rules on one sheet with 70 columns by roughly 1800 rows and it works good.

    I have a question though: are you aware of any problems with refreshing CF?
    I have some that is driven by a named range which has it’s “refers to” updated by a macro.
    I have a recurring timer that monitors the Window scroll row and updates a named range if the scroll row changes. The named range acts on an Or statement in one of the CF terms.
    I find that it updates nicely if I feed the named range through cells in a helper column (where I manage all of my CF decisions) and it works brilliantly. I was hoping to avoid recalcs however by inserting the named range directly into the CF formula but I cant get it to update.
    I tried, Calculate, Screenupdating (“turning it off and then turning it back on again” – IT Crowd Style) and the same with CalculateConditionalFormating but nothing works.

    Are you aware of this? And if so, do you have any ideas how to fix it?

    Thanks again!

  4. Harold Petrich says:

    Another problem in Excel 2013 (Likely an easy fix):
    Condition: Using Freeze Panes for the first 12 rows the rows information disappear or do not refresh properly (e.g. Formulas > Trace Dependents and then Remove arrows (arrows remain!)
    Temporary solution: Remove Freeze panes and re-apply!

    Couldn’t find any refresh options with conditional formatting. Likely a refresh issue in 2013.


  5. cost engineer says:

    Thank you. Work spreadsheets with 3500 rows and 62 calculation columns, referencing two more database calc sheets, were requiring 4 to 12 seconds to just insert a row. Deleting the conditional formatting (only in two columns) sped that up to a second or so. (not accurately measured.)

  6. jmb says:

    an interesting way to see if conditional format is active is to use the DisplayFormat property

    If oCell.DisplayFormat.Interior.ColorIndex = oCell.Interior.ColorIndex

    I am having many problems with display formats using UDFs

  7. jmb says:

    DoEvents seems to consistently refresh the conditional formats in my case (Excel 2016).
    can even be placed inside the UDF at some cost in time

  8. fastexcel says:

    That’s interesting – I have never tried DoEvents inside a UDF. I wonder which events it will Do and which not (presumably won’t trigger a calc event!)

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