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:
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.