By Zack Barresse
A new bug was found by my friend Tracy Syrstad, an expert Office consultant and author, when working with Excel tables and using conditional formatting. This is specific only to Excel 2007 and isn’t reproducible in any subsequent versions. I’ve reproduced this several times, and while it’s a pain in the butt, it’s something which is completely avoidable. We’ll get to that in a bit. First, let’s go over the conditions in which you might see this fatal crash of Excel.
1. Create a table in Excel, it can be any number of columns wide
2. Ensure there is only a single row in the table
3. Select any single column (not including header or total rows)
4. Apply a custom formula conditional formatting using full absolute referencing
As soon as you add new rows to the table, BOOM! – Excel crashes.
The key is using full absolute referencing in a 1-row table. If the reference is not fully absolute, for example if you take away the dollar sign ($) in front of the row (which would actually be the proper thing to do) Excel won’t crash. And if you have more than one row in the table a crash will be prevented as well.
Is there any good news here?
Well, yes, there is good news. First the remaining bad news. Since this is in 2007 and fairly obscure, there probably won’t be a fix coming for this. Okay, that’s the bad news. The good news is since this is so obscure, and affects so few people, you’ll probably never have to worry about this happening to you. Here at ExcelTables.com, since we’re all about tables all the time, we feel it’s our duty to report on even the most obscure bugs.