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 usingDecember 2nd, 2008 at 9:13 pm (10860) [Quote]
I am a user of Excel and encountered this problem today and it virtually shut me down. I’ve got some deadlines I’ve got to meet and my spreadsheet it huge. There is no way to rebuild it and meet my deadline. I downloaded Open office, tried your fix, but it keep locking up my computer (not responding). I came back to this page and started reading the responses. About saving as txt and then re-saving… when you say lose ALL you formatting… what happens to the worksheet. I have formulas galore and cells pulling information everywhere. Sorry, I’m not “tech” enough to know exactly what formatting is. I do know it is the color in the cell, outlining the cell, font of the text in the cell. What else would I lose. I really would like to get this fixed now, but, since that is probably not possible, I’ll still be ok if it is fixed in the next day or two. Hope you read this and answer soon. THANKS
Posted usingGlenna, a 57 year old woman just trying to make a living in accounting.
Photography is my hobby. :>)
December 2nd, 2008 at 9:16 pm (10861) [Quote]
Oh, one more thing. On another site someone suggested some soft ware that analyzed your spreadsheet and then removed duplications… and some other stuff.
I downloaded the trail and when my workbook was analyzed by the software it showed only 1500 different cells. The max is approximately 4000. So, it seems I should not even be getting this error message.
Posted usingDecember 3rd, 2008 at 9:50 am (10864) [Quote]
@Glenna: “Formating” is essentially everything that sets one cell apart from the others - color, borders, width, height and also the details such as how many decimal places are to be displayed, or whether the value is supposed to be currency, percentage and etc. Anything you do when you right-click and go “Format Cells” or what you pick from the “Format” menu.
The way Excel apparently stores all this information is on a per-selection basis. So if you, for example click on cell A1, add a border to it and set it to be a percentage it will store this information once in the file. If you click on A2 and do the same exact thing, it will store it another time. And etc.. The problem is that it only has enough space to do it around 4,000 times. It is a design flaw.
Conversely if you highlight the whole row, and apply the formating to all the cells in it, it will only use one of the 4,000 “slots”.
But if what you did to the whole row is slightly different to what you did to A1 and A2 it will keep the two other formating “slots”. Or something like that. So it is easy to ramp up this number - especially if a lot of people collaborate on the document and they all keep adding their formating.
Have you tried this: Ctrl+A to select all the cells on a worksheet, then go to Edit->Clear->Formating. See if you can save after that. If you can, redo the formating. Try to apply format to whole rows, columns or blocks of cells at a time, rather than individual cells.
If that doesn’t work, move a worksheet to a blank workbook. See if you can save. If you can, clear the formating on the worksheet you moved, and then move it back in.
I hope one of these things might work for you.
Posted usingDecember 4th, 2008 at 11:14 am (10878) [Quote]
Thanks you for your quick response. Last night before I when to bed I decided to let the oo.org “not respond” to it’s heart’s content. (At one point yesterday I had let it set for about two hours, but apparently that was not long enough) When I got up this morning it had done it’s work and I have my workbook saved back in Excel from oo.org. I did go ahead and try the clear formatting in a copy of my worksheet. It will work but i hope I don’t have to go to that extreme. I’m going to start working in my newly saved excel workbook and hope for the best there. At least, now I know I have some options and I can continue on. Thanks again.
Posted using