Case of Slow Opening Excel Files: Huge Number of Conditional Formats

You cleaned out all bad styles from your file, you removed all bad named ranges and checked for reasonable memory allocation on all sheets (even the ones that are very hidden), checked for “ghost” ActiveX controls, etc… but the Excel file that you are working with still takes long time to open. Don’t pull all your hair out, smash your computer to smithereens or consider leaving and never coming back to work again just yet. One of my colleagues just solved another mind boggling incident that involved slow opening file that is worth sharing.

The 166KB file contained huge number of conditional formats, 8344 to be exact. It took over 2 min to open it on my 2.4GHz dual core machine with 4GB of RAM. It looked like it may have been VBA code that was at least partially responsible for what happened to conditional formats. Excel UI doesn’t provide the count of format conditions. One way to check for excessive number of conditional formats in an Excel file is to write a short VBA macro or just run this line of code in the VB Editor in the Immediate window for each sheet:

?ActiveSheet.UsedRange.FormatConditions.Count

Please leave a comment if you had to deal with huge number of conditional formats in your slow opening files. I’m trying to get an idea of how widespread this may be. If it is high enough then I would put conditional formats count check in the tools distributed through this blog.

This entry was posted in Computers and Internet and tagged , , . Bookmark the permalink.

4 Responses to Case of Slow Opening Excel Files: Huge Number of Conditional Formats

  1. Scott Hay says:

    One of my more integrated reports (with lots of scorecard metrics split across many geographies) has 2018 conditional formats in it. I noticed this 2Mb workbook takes 15 sec or so to load (while most workbooks load in < 2 sec on this quadcore i7 PC with SDD drive). So it appears there definately is some performance hit (I was wondering why this workbook took longer to open…)

    • XLGeek says:

      Do you know why you have so many format conditions in this file? Are they code generated or simply accumulated during the normal workbook editing cycles, i.e., a result of multiple copy/paste operations? Thank you

  2. Guy says:

    I had the same issue on a worksheet that I needed 3 different columns with ranges 9 rows high, every 52 rows to be conditionally formatted based on if a cell was empty or not.

    I had copy pasted these 52 rows down 100 times (yielding 5200 rows) and something like 1000 conditional formats.

    so instead I wrote a vb macro to concatenate all of these ranges into one huge mass of ranges to select so that I could conditional format just once, … this did not work well because the variable passed into range would only pass 255 characters. ultimately it came down to this:

    Sub select_cells_to_format2()
    
       Application.ScreenUpdating = False
       Application.Calculation = xlCalculationManual
    
    ' until row 5200
    
    Dim c As Range, Yc As Range
    
    Dim rangeToSelect As String
        For g = 0 To 10000
    
          h = (j * 52) + 1
          i = (k * 10) + 1
          If h > 5200 Then
    
             Yc.Select
             Application.Calculation = xlCalculationAutomatic
             Application.ScreenUpdating = True
            Exit Sub
          End If
          
          rangeToSelect = "E" & h + 11 & ":H" & h + 28 & ",O" & h + 11 & ":R" & h + 28 & ",Y" & h + 11 & ":AB" & h + 28
          If Yc Is Nothing Then
          Set Yc = Range(rangeToSelect)
          Else
          Set c = Range(rangeToSelect)
          Set Yc = Application.Union(Yc, c)
          End If
    
           j = j + 1
       Next g
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    End Sub
    

    This allowed me to union together many smaller ranges into the huge range I needed and select it. once that macro was run, I applied the conditional formatting to it and turned 1000 conditional formats into 2 (there were 2 different selection macros I had).

    Hope this helps.
    ~Guy

    • XLGeek says:

      You are absolutely correct. Conditional formats can explode if applied to individual cells one at a time. I don’t think that there is a limit to the number of format conditions that can be stored in the file (i.e, available RAM becomes the limit), so there is a potential to create a ton of them and not notice. I saw this issue popup primarily when users ran VBA on the worksheets with format conditions. Thanks for the tip. Many people are not aware of the Union function that concatinates adjacent cells and ranges.

Leave a Comment or Ask a Question

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