I ran into an interesting (which, under the circumstances means aggravating and frustrating) problem this week with an Excel 2007 spreadsheet. I ran into an apparent bug in Excel 2007 which results in a "Too Many Cell Formats" and prevents pasting into the spreadsheet!
My spreadsheet was about 40 MB in size (yes, that’s Megabytes!), has 10 or 11 sheets in it (mostly used in vlookup functions, but some providing supporting calculations for the main spreadsheet), has over 22,000 rows and about 100 columns. I’ve developed over a few years and use it to format and massage a huge amount of data.
If this Excel bug simply prevented changing or setting formats on cells, that would be bad enough. However, it also prevents pasting into the spreadsheet.
Even more interesting, this apparent bug has been long known and unfixed by Microsoft.
Fortunately, when I started searching Google for the exact error message, I got a lot of links to help. Some were blocked by the nanny filter (category “computer/internet” was filtered) at the office, but some weren’t.
One was a Microsoft Support document at http://support.microsoft.com/kb/213904 , but it really didn’t have a clue as to the real problem. I’m sure that’s a way to get the problem, but it’s not the one that people complained about.
The most helpful information link was to series of postings on the Microsoft Developers Network (MSDN) forum. First, someone posted the problem. Then, a Microsoftie tried to close down the discussion, telling the user that he was posting in the wrong place, and pointing to a useless link. Fortunately for everyone, the user posted again and others responded.
If you want to read the MSDN link, here it is: http://social.msdn.microsoft.com/Forums/en-US/innovateonoffice/thread/20169ffc-2196-4528-9266-4f5a476d17d6
Of course, the important part is that one of the people posting posted the solution. Others tried it and posted about their success and appreciation.
The bottom line is that I was able to solve my problem.
It seems that Excel 2007 has a bug that occurs when you paste cells into the spreadsheet from another spreadsheet. Pasting within an Excel file doesn’t seem to cause the problem, nor does pasting between worksheets within the same Excel file, but posting from another Excel spreadsheet causes troubles. It creates phantom "cell formats" and styles, or at least corrupts the count.
Of course, that’s exactly what I was doing…
A nice guy, who posted using the nickname XLGeek, wrote a utility and posted about it on January 26, 2010, with a link to his WordPress blog. The utility requires .Net 3.5 and MS Excel 2007, and is said to fix xlsx and xlsm files (mine was an xlsm file – a macro-enabled xlsx file). His link was/is http://sergeig888.wordpress.com/2009/10/13/sharing-useful-utilities/
Tip: The article refers to the utility as "XLStylesTool" but the file to download is called XLCleaner. It’s a zip file and includes the repair tool and the ReadMe file for it. I used the .Net 3.5 version, although there’s also a .Net 4 version.
Of course, fixing the problem wasn’t quite that easy. I could get to wordpress.com page, but was blocked from accessing the download link.
At that point, I decided I could download and test the program from home, since I knew that, if there was a problem, I had the tools to solve the results on my own computers.
I was very happy to solve the Excel spreadsheet problem. The utility cleared it up, I could paste again and I could change cell formats. All was well with the world…