Excel: Too many different cell formats
The other day this well known Excel issue trickled down to me. If you are to trigger happy applying formating to your excel cells, you eventually hit some sort of hard coded limit. Excel can only handle 4k of formating annotations. I suppose this is an inherent flaw of their binary file format, but then again, who knows. The bottom line is that this has been an issue in every single Excel version up to, and including Excel 2003. Microsoft has
a long standing knowledge base article which gives no suggestions on what to do once you hit that forbidden threshold. Their advice is essentially “don’t use so many different formats”. That’s about it.
The problem is that once you hit the magical number that oscillates somewhere in the 4k range Excel will block you from doing any new formating adjustments by popping up the “Too many different cell formats” error message. In theory, simplifying your worksheet will make the error go away, but to do that you will need to either remove or modify formating on some of the cells or cell ranges. But you can’t do that because of the error. So you end up in an endless loop. To solve the problem, you have to do the precise thing that it prevents you from doing.
The poor guy who fell victim to this bug went through 3 support drones who all told them the same thing - don’t use so much formating. Fortunately he CC’d some managers on the support emails, and eventually it ended up in my mailbox. Which was a good thing since I’m not as immersed in the Microsoft monoculture as the lowly tech support drones. The truth is that there is a solution to this problem, and it’s very easy. But Microsoft won’t even tell you about it because it involves using a competing product.
If you ever run into this issue do the following:
- Open the document in Open Office
- Save in the Microsoft XLS file format
- ???
- Profit
80% of the time this is enough to solve the issue, because Open Office organizes the formating information slightly differently due to the differences in implementation. For the most part it is a very exact port, and it faithfully mirrors Excel functionality, but there might be few minor glitches when you do this. However, it probably beats re-creating the document from scratch via copying and pasting.
The problem here is that none of the low level guys knew that, or even considered trying it. Living immersed in the monoculture has blinded them to alternative solutions. This is the price we pay for letting Microsoft run our lives.
Related Posts:
April 2nd, 2008 at 12:21 pm (8661) [Quote]
Disagree with your conclusion. The problem is education - when we stop learning we let X run our lives. X could be Parents, Relgion, Microsoft, the Government, Wal-mart, …
The low level guys have a low priority on educating themselves, the price they pay is monoculture.
Posted usingApril 2nd, 2008 at 12:29 pm (8662) [Quote]
Good point. I think it actually goes a full circle - ignorance fuels monoculture, and it in turn fuels ignorance.
Posted usingApril 2nd, 2008 at 9:18 pm (8664) [Quote]
I don’t use excel and hell i seldom even use OO but this is hilarious. And the funniest part of it to me is that faced with this problem one of the first things I would think of would be to run it thru open office. I mean shit its obvious, one program can’t fix it try another, that is like grade school cs stuff to me. Anyway i suppose you’re right
A vicious cycle reminds me a bit of Eliezer Yudkowsky’s ideas of death spirals over at the overcoming biases web site.
Posted usingApril 3rd, 2008 at 9:09 am (8666) [Quote]
Agreed,
. Similarly employee turnover engenders turnover, poverty engenders poverty, …
Posted usingApril 3rd, 2008 at 10:46 am (8670) [Quote]
Same here. I use it at work because that’s what my coworkers use. When I write stuff for myself, I use plain text, or latex.
Posted usingApril 14th, 2008 at 12:30 pm (8771) [Quote]
This wouldn’t work if you have VBA in the original spreadsheet, would it?
Posted usingApril 14th, 2008 at 2:40 pm (8772) [Quote]
Good question. Probably wouldn’t, but then again I never really tried that.
Posted usingMay 9th, 2008 at 4:56 am (9002) [Quote]
There is a much simpler way of stripping all formatting. Save the original doc as a *.txt file and then re-open the *.txt file in Excel. ‘Techs’ do a job to a curtain level. They aren’t experts, but at least they have a job and some knowledge, which is fine for everyday problems. However, advice is just that. You can choose to take or leave it. How many ordinary workers suffer this specific problem? I would imagine not many. I found this site because I took my business’s bloated an unweildly sheet and wanted to improve it. I encountered the problem and looked for a solution. On reading the OO solution, it occurred to me to just save in *txt. So, we now have the solution and we can get back to our own problems. Do we really need to complain about someone elses employment and ability? I hope I don’t sound sanctimonious. I just think there is enough stress and pressure at work without projecting annoyances at innocent people. Now excuse me, I can now work on the pivot table that I originally set out to do before the problem arose. Thanks for reading.
Posted usingMay 9th, 2008 at 9:25 am (9004) [Quote]
Yeah, saving to .txt or even as a comma separated list would work. The problem is that you will lose all formating. With Oo.org you only lose some of it.
Also, I’m not complaining about the tech drones. They are just not trained and not expected to think this way. That’s just how it is.
Posted usingMay 23rd, 2008 at 7:02 am (9126) [Quote]
Thanks for providing information, i wants to know suggetion how we can minimise this error.
Posted usingJune 8th, 2008 at 5:43 am (9282) [Quote]
[…] April 2nd 2008 3:02pm [-] From: terminally-incoherent.com Related? […]
Posted using