I just spent about three hours this morning trying to debug something at work. It turns out the issue shows up in Excel 2003, which our customer is using, but not Excel 2007, which I have on my laptop. This just goes to show you how important it is to mimic the customer’s environment as closely as possible. Anyway, I figured that part out, so now I had to decipher the error message when I tried to open the file in Excel. XML Spreadsheet warning in Table Reason: Bad Value Okay, sounds simple enough. I just have to find the bad value, and fix it. Excel didn’t offer me any advice, so I figured I’d try MSDN, Microsoft’s developer reference. You’d think that, since I was using a Microsoft product on a Microsoft operating system, maybe Microsoft might have some idea what the error message meant. As an aside, can I tell you how frustrating it is to have Excel tell me that the error has been printed to a log file hidden deep inside some Windows hidden temporary directory? Not only does it fail to provide me with a link to the file, but because it’s hidden, I can’t navigate through Windows Explorer, and I have to actually type the file address in manually. I can’t even copy the text from the error window! It’s like someone intentionally made it as hard as possible to look at the log. Anyway, there were ZERO results on MSDN. Zero. I’m forced to conclude that I am the only person who has ever had this problem. There isn’t any other explanation. It’s inconceivable that Microsoft could possibly have just ignored this error message, never once mentioning it anywhere that their vaunted search engine was able to look. It turns out the error was reasonably simple – Excel 2003 only allows 30 values in a sum. It will handle more if you use the range (A1:A40), but you can’t list the cells individually (A1, A2, A3 . . .). Excel 2007 doesn’t have a problem. Interestingly, if you use plus signs instead of the sum function, the limit of 30 goes away. Which is probably how I’m going to work around this. But couldn’t that have been in the error message? “We’re sorry, you can only have 30 cells in a formula”. Look, that was really easy. So, I hope the next person who has trouble deciphering an Excel error message find this post and saves some time. And I hope the people responsible for the idiocy on which I wasted the last three hours are all hit by a bus. Well, not really. Not a real bus. A three hour bus of mental anguish. That sound perfect.
Skip to content
