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.
Update 9/29/09:
I got this in my inbox today – a very interesting read regarding this issue. I figured I’ll share it here for reference as it directly pertains to the problem at hand:
Dear Luke
I was Googling the phrase “Too many different cell formats” today, to check that my web site (which offers a solution) was still sitting high up Google’s list, and came across your blog at http://www.terminally-incoherent.com/blog/2008/04/02/excel-too-many-di fferent-cell-formats/, with the suggestion of using Open Office to get rid of the error.
I was aware of the Open Office solution, but am also aware of its disadvantages:
The main disadvantage is that once you have transferred the defective Excel workbook to an Open Office environment, you have to repair the problem there (which is obviously easier, since OO doesn’t keep on reiterating the error message every time you try to do anything). Most people are in a situation where they cannot call for a complete switch to OO in their organisation, and they want to be able to put the mended workbook back into Excel. How long it is before they hit the error again is almost impossible to forecast, and they are right to worry.
The second is that there is reason behind the formatting difference that makes OO cope when Excel can’t. I think this error turned up in Excel 97, which was a fairly substantial re-write of the object model; but perhaps it came earlier. The intention seems likely to have been a need to speed up the rate at which Excel could repaint the display when it changes. That rate could be painfully slow if running Excel across the sort of telephone line that was common at the end of the 80s. (I remember having to rejig the arrangements in a Delphi database application I had programmed, when I discovered that pressing the down arrow key once while looking at a display caused the server to retransmit the whole of the database’s contents over the ‘phone line to my desktop and then repaint the visible display about 2 minutes after the arrow had been pressed). I have never compared the rate at which OO and Excel perform large operations of that sort, but would not be surprised if Excel wins the race, as a result of enhancements like this one.
So, though the OO solution is effective, not everyone is going to want to commit themselves to using it. A rather more painful way of getting rid of the error message, so that slow repair work can start, is to identify a row or column which must contain a uniquely formatted cell (which might be a row or column containing the cell[s] most recently changed, that caused the crash), and delete it completely. It will be worthwhile to note all its contents. It could be sensible to delete it by Cutting it (with Ctrl-X) and then paste it from the clipboard into an empty workbook in a separate instance of Excel. Then the deleted range can eventually be pasted back into the problem workbook after it has been repaired.
Why did Microsoft invent the format combinations array? Someone probably worked out that it would save time if every cell in the UsedRange had a pointer to an array of format combinations which could be applied directly. I expect they gave the programming job to a Seattle high school pupil on vacation job experience, and then went off to the West Indies rather than supervise the work. Certainly his/her implementation is woefully short of any logic to recognise in advance that the array is nearly full, issue warnings of the need to clear things up, and finally refuse to accept any more formats, but leave enough array space to allow the user to make changes which will reduce the number of format combinations, and not crash the workbook.
You may not realise that the reason why the limit is said to be “approximately 4000” is that it seems to vary. Some workbooks crash when the number of different format combinations is as low as 2500. I have one with over 4400 different format combinations which only crashes when the user tries to add one more. The reason why it varies is that the list that eventually gets filled up in practice nearly always seems to contain duplicate entries, so that it has more formats in it than one can actually count.
I think that duplication occurs because the Excel object model must be programmed to carry out list housekeeping as a background task. If I format a cell using a font (eg Monotype Corsiva) which in fact is previously unused, the basic task for Excel must be to determine whether the cell has a new format combination, and if so add it to the list, then insert the relevant pointer value in the cell data. When the list has several thousand entries in it, and I have just reformatted a cell, the repetitive searching could take ages, even with some clever logic. Easier to add the new format to the list, and later search in background to see if it is a duplicate and remove it if so. If the workbook is saved and closed at the end of a session, any de-duplication that has not yet been done just gets lost. The duplicates problem is another indication that this whole aspect is not the finest portion of the Excel object model. If the error had ever hit Bill Gates in person, the Chief Software Architect would have spent billions to rebuild this facility completely.There is a lot of misguided advice available on the internet (eg Leo Heuser’s regularly-published VBA routine to DeleteUnusedCustomNumberFormats which may be a solution to some problem, but definitely not to this problem). But there are really only two solutions to the formats problem.
One is provided by the tool at http://xlsgenreduction.arstdesign.com/index_en.html which actually manipulates and rewrites the contents of the workbook file, to eliminate duplicated format combinations and, if required, to reduce the number of combinations further by imposing the replacement of formats which are very similar to others in the list. (Interestingly, that web site uses the same terminology as the official Microsoft contribution, by talking of Styles. The Style object is a completely different aspect of the Excel object model, and both papers confuse the reader). The aspect of Xlsgen that my customers criticise is that it gives the user no control of the automated reduction process; it can be very frustrating to find that it has wiped out all cases of a format which differs only slightly from its replacement but was created just to deliver the difference required.
The other solution is my much more laborious tool, QAid at http://www.total.rowing.org.uk/quarrell/QAid/ which, being itself an Excel 97 workbook, ploughs through all the problem workbook’s UsedRange, identifying, counting and listing the format combinations. It can take ages, but produces a good list. Then it offers a user-controlled elimination process. That presents the user with one of the cells that each have a unique format combination, and a list of the 11 cells which have the fewest format differences from it. The user chooses one of the 11 (or skips if none are suitable), and its format is applied to the unique cell. A few hours’ work reduces the number of formats away from the danger level, and a second run of the analysis confirms what the safety margin is.
However, QAid does not attack the problem of duplicates. None of my users has ever complained about this. I think that is because most Excel spreadsheets large enough to hit the error have several hundred unique formats in them (the record is 1250 unique cells), and eliminating those gives a large safety margin. Everyone who has ever hit the limit develops a more defensive approach, and few see it again.
I don’t know whether any of this is of interest to you – but it’s all knowledge.
Best wishes
Peter Quarrell
Sheffield, England
Thanks Peter! OO.org is indeed a quick and dirty fix here. It worked for me, but as you said it may not resolve the issue completely. Thanks for elaborating on this.
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.
Good point. I think it actually goes a full circle – ignorance fuels monoculture, and it in turn fuels ignorance. :)
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
[quote]ignorance fuels monoculture, and it in turn fuels ignorance. )[/quote]
A vicious cycle reminds me a bit of Eliezer Yudkowsky’s ideas of death spirals over at the overcoming biases web site.
Agreed, [quote post=”2372″]ignorance fuels monoculture, and it in turn fuels ignorance[/quote]. Similarly employee turnover engenders turnover, poverty engenders poverty, …
[quote post=”2372″]I don’t use excel and hell i seldom even use OO[/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. :)
This wouldn’t work if you have VBA in the original spreadsheet, would it?
Good question. Probably wouldn’t, but then again I never really tried that.
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.
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. :P
Thanks for providing information, i wants to know suggetion how we can minimise this error.
Pingback: Excel: Too many different cell formats [ Terminally Incoherent ]
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
Glenna, a 57 year old woman just trying to make a living in accounting.
Photography is my hobby. :>)
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.
@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.
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.
This is a crazy error, I recently started rolling out Office 2007 in my enviroment. We use generally small documents that many people open and save. When I say small I mean one workbook with maybe 30 columns and 50 rows. But this crazy error has started appearing, of course my co-workers are blaming Office 2007. I am not sure that my small documents ever get to using 4k cells.
That is not how you avoid a Too Many Formats error. I’m an Excel programmer. You just clear some formats with – Edit Clear Formatting, save, close and reopen. Excel will recount how may formats you are using. For example, clear 500, should give you 500 more if you save and reopen.
Just because Microsoft are fucktards doesn’t mean you have to be as well.
thanks Jim….you are the smart one in this bunch… your solution worked
No problem. Edit Clear All or Clear Formats on a selection works well, but sometimes I even delete extra colomns and rows to make extra sure, then save it. People go crazy with formatting and on large documents it an make them almost unworkable. You can read it, add text or numbers but not format anything at that point without clearing preexisting formats. What happens when they grab a column or row and color the whole thing? Excel has to remember all 65,000 cells in that column being a particular cell color, font, bold, etc. It isn’t an issue until someone maxes it, and then you realize you’ve increased file size, maxed formatting and made the workbook dangerously close to unworkable.
I have one rogue excel workbook that has a problem and I don’t know how to fix it. I need to copy part of a worksheet from one workbook to another. In the past all the formulas copied too. However, I have discovered that on my toolbar the paste icon dropdown is greyed out. Everything but past link and paste special. There is no option to paste formulas. How do I turn the paste formulas back on? Back in December you helped me with another problem. I hope you can solve this one for me too. (I’m the 57 year old accountant.) Thanks
@ Glenna James:
When you say its greyed out do you mean always or when you have something on this sheet copied? Are you copying anying unsual like pivot tables, charts, graphs, pictures, or just numbers? Are there hidden rows or columns?
I am not copying anything odd. It is only numbers. In this particular workbook it is always greyed out. I looked at my other workbooks and they are never greyed out.
But amazingly, overnight a miracle occurred and it was magically fixed. I don’t know how you did that but you are awesome.
Seriously, I don’t know what is different but it works today. Thanks for responding to my post.
Hi all,
Can I re-open the “too many different formats” discussion? I tried the solution Jim gave (Jan 13) on an excel 2007 worksheet. The worksheet works just great – in 2007. Colleagues using 2003 get the error message. First I thought to get around it by rebuilding the workbook from scratch. No dice. That’s when I got to this page.
So, anyway, I removed all formatting by selecting all cells on all tabs and clearing the formatting. But still the same error message comes for my friendly co-workers. How come?
@ Jasper de Jonge:
2007 allows something like a million rows instead of 65,000 so it probably allows a lot more formatting as well. They might want to delete extra rows and columns by going past the last column with data selecting it hitting Ctr right and delete, going past the last row, selecting it, then ctrl down and delete. But they should probably do it until it works for them. Have they tried clearing formatting in 03? Again they have to remove some formats, close and reopen to regain memory.
Lots of people seem to run into this problem. Most often the issue is related to the exessive number of unused often corrupted styles and not so much cell unique cell format combos. I wrote a utility to fix XL2007 OOXML files that can be saved down to XL2003. Here is the link to the blog post:
http://sergeig888.spaces.live.com/blog/cns!53E1D37F76F69444!534.entry
Requres .Net3.5 and MS Excel 2007. Will fix xlsx or xlsm files.
No need to run the risk of further corrupting your file by using Open Office.
After using Excel (and loving it!) for nearly 25 years, I ran into this error for the first time a couple of weeks ago and to say that it wreaked havoc on my productivity is an understatement! The problem is, I KNEW it was not a problem with my file having too much formatting. For a change, I was actually working on a file that had less than 10 tabs, and only one of those was heavily formatted.
I finally broke down today and decided to copy everything into a new file, remove all the formatting, and then try to reformat my sheets using Custom Styles, which is what all the sources that I reached out to recommended. That is when I figured out the problem–which was that out of nowhere I suddenly had over a hundred custom styles that I had never defined. Excel wouldn’t let me define any more, and even though I could see the path of the custom formats, the directory that they were supposedly in does not exist on my computer (and yes, I looked in hidden folders/files).
I tried to delete these random custom styles one by one by right clicking them and selecting delete, but they would not go away. A co-worker is trying to find out if there’s somewhere in the program files where custom styles are saved so we can all do a mass delete of these nuisance files, but in the meantime if this is your problem too, here is a workaround:
*Close all your open Excel files
*Open a new file (you have to do this first; don’t try to open a new file with the “infected” file still open or the new file will inherit the custom styles from the old one)
*Open the “infected” file and copy the contents of each sheet and paste them into the new workbook. Do not do a “move or copy sheet” as you will copy the custom styles along with the sheet.
The good news is, you can paste everything from your old file, including formulas and formatting, into the new book. You’ll have to rename your tabs and fix your links to point to the new file, but the problem will be resolved.
Hope this helps!!
Beverly
Its Excel not Powerpoint, why do you need custom styles anyway?
If you want to list them you can use this
Sub ListStyles()
Dim i As Integer, SC As Integer, S1 As String
S1 = “”
SC = ActiveWorkbook.Styles.Count
For i = 1 To SC
S1 = S1 & vbCr & i & Chr(9) & ActiveWorkbook.Styles(i).Name
Next i
MsgBox S1
End Sub
””””””””””””””’
I show only 6 regular styles in my workbook. If you wanted to delete ones beyond the normal style count, you could run this. If you want to keep the first 10 for example, set SN as 11
”””””””””””””””
Sub KillStyles()
Dim i As Integer, SN As Integer, SC As Integer
SC = ActiveWorkbook.Styles.Count ‘style count
””””””””””””””””
SN = 7 ‘start number to delete
””””””””””
For i = SN To SC
ActiveWorkbook.Styles(i).Delete
Next i
End Sub
or you could put the stlyles into a sheet with something like this
Sub ListStyles2()
Dim i As Integer, g As Integer
Dim WB1 As String, WB2 As String, SC As Integer, S1 As String
WB1 = ActiveWorkbook.Name
Workbooks.Add
WB2 = ActiveWorkbook.Name
Sheets(1).Range(“A1”).Value = “#”
Sheets(1).Range(“B1”).Value = “Name”
‘Sheets(1).Range(“C1”).style=
SC = ActiveWorkbook.Styles.Count
For i = 1 To SC
g = i + 1
Sheets(1).Range(“A” & g).Value = i
Sheets(1).Range(“B” & g).Value = Workbooks(WB1).Styles(i).Name
Next i
End Sub
I didn’t need the custom styles. I “inherited” them from an infected file and once they were there, I couldn’t get rid of them! Thanks for the code to delete them. I didn’t even think to poke around in VBA for the solution. Duh!
@ JStiene:
Thanks a lot JStiene!! This is the best solution to get rid of this problem!
Regards,
Manpreet
@ Jasper de Jonge:
The most extreme is delete the extra rows and columns then save.
Usually clear all and save will work. If you close and reopen it.
But I think 03 might be a bit buggy. Ive noticed strange things with it that never happend in 97-XP or even 07. Theyve done a good job of keeping their versions bug free comsidering the complexity and flexibility of Excel, but it is huge. Maybe a million lines of code.
I noticed a totalling procedure that was storing huge file size I cant explain. LIke adding 20 megabtyes to a sheet with 10 rows and 5 columns. Even deleting the columns around and rows benearth it was still two megabytes. So clearinf the formatting on the remaining 50 cells dropped the extra two megabytes. No sense. System bug, App bug, installation bug. Something was wrong. I also noticed forms having trouble saving properly, so it may have been an install process. Some glitch in the disc imaging.
@ Beverly Brandt:
I was fooling around with them after reading it, created one, and watched all the numbers and letters in row and column headings go into a strange font. Interesting. Everything has collections. Figure out the right ones, see if recording a macro exposes the object model, then experiment with adding and deleting them. A walk in the park.
I had a workbook that crashed and lost all formating, i thought, because of this bs. I opened it with OO and everything was there. I had been working on it for about 6 hours today and i lost about the last hours worth of work. The only thing i lost prior to my last save was a few datedif formulas. Thanks for the heads-up.
You are not simply quite a gifted individual but very generous of spirit – XLStyles Tool is an excellent utiility that does exactly what is needed, very quickly, very efficiently. Thank you for the effort in writing it and making it available to users who are ‘just trying to get through the day’ and have no concept of what is going on beneath their mice.
You rock, Peter! The XLSgen tool solved my problem, which had caused countless extra hours on work because of a particularly complex workbook.
Thanks!
I still don’t know why people are using so many styles in Excel. It is more for lists than fancy displays. I guess people like charts and graphs, and use it for presentation, but personally, I’ve never even used styles in Word. Maybe I should. I still think of Powerpoint for presentation, word for documents, Access for Databases, Excel for calculation, lists, etc. And MS keeps coming up with new things. Sharepoint, Project, any number of ways of managing all of it. I suppose companies like keeping everything on servers, well manged and backed up, but who doesn’t back up their own work regularly, and in multiple versions? Better to be a pack rat than lose data.
Jim
I had a file today where this was happening and didn’t have more than a half dozen different types of cell formatting. I personally think it will happen when a file gets updated month after month and year after year. Since the spreadsheet was only 2 pages, we opened a new file and did the copy and paste-special from old to new. Not elegant but efficient.
@ Cathy Moore:
I used code when I want to duplicate something, like
Selection.Copy
Workbooks.Add
WB2 = ActiveWorkbook.Name
Range(“A1”).Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range(“A1”).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
The paste values at the bottom is to prevent formulas from pasting and linking back. What it does is copy something that looks exactly like the original. The other method is when you right click on a tab and do a move or copy. Of course that can only copy 255 characters in the cells so if any have more characters they wont copy the full cell.
That messed up the formatting and assumed something was selected.
This copies a sheet so it looks the same
Sub testmac()
Cells.Select
Selection.Copy
Workbooks.Add
WB2 = ActiveWorkbook.Name
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
But you can also copy using one line of code
You can copy a sheet with one line using
ActiveSheet.Copy
Which is like Move or Copy into a new workbook.
Word of caution: ActiveSheet.Copy will also move all custom styles from the source workbook to the destination. In case of Office 2007 custom system generated styles are the main reason for the file format related corruption and the “Too Many Cell Formats” message. In case of Excel 2003 it is also major contributor. You don’t want to bring custom styles into the new workbook – it defeats the purpose of the intended file clean up.
I got really positive feedback on my Excel Files cleaning tool so far: . If you are using Office 2007 OOXML formats don’t mess with manual copy/paste or VBA. I added file drag&drop driven by popular demand since last post. That was a request from a person that wanted to increase his file cleansing productivity!!! It is crazy how widespread this issue really is among the Excel file users.
I was desperate. Nearly lost weeks and weeks of work over several years. Before it crashed. i had no idea why as i didn’t know how many formats i had used and couldn’t not delete and formulas ( all of two) thankyou sooooo much
my managers should be okay now.
“Is there a course we are able to learn how to use open office?”
BYe for now
thankyou
joh
Dear XLGeek, I tried your utility, and downloaded Net3.5 as well. I ran my XL 2007 thru your tool, but it didn’t solve the problem. I saved the new 2007 to 2003 and emailed it to my user who has 2003. We still get the same error. I went back to 2007 and took out most of my merged cells, as this seemed to be the biggest hangup, but still got the same error. I’m exhausted of funds. Not sure what to do next?@ XLGeek:
@ Karie: Hi Karie, I’d be happy to look at your file. You can add me as a contact or send me a private message on Windows Live on how to connect with you, so that we can get in touch. Excel 2003 has lower boundary for unique format combinations count (about 4000) than Excel 2007. Typically even in Excel 2003 that upper limit is “eaten up” by the huge number of bad styles when you downgrade 2007 file to 2003. I will definetly be able to tell you where the extra format count that gives you the “too many formats” message is coming from.
@jStiene: THANK YOU!!!!
I tried everything, cleared all formats, opened in different excel versions, etc.
Perfect solution!
@ Marcus Lieder:
Marcus Lieder wrote:
No problem. I believe every cell that has unique formatting counts for one format. For example, four aqua cells with a border around all, would be four formats. One for aqua and a top line, one for a right line, etc. One thing to look at in trying to shrink size is fonts. Have you used any unsual fonts? If you select all and set almost everything back to Arial 10 it will probably reduce formats.
@ XLGeek:
does this problem stil exist if upgrade to excel 2007?
@ tracy:
It does and even was getting worse before these fixes came out: http://sergeig888.wordpress.com/2011/05/06/msft-released-hot-fix-for-e xcel-2007-custom-styles-duplication/,/a>. Thank you
Fixing url: http://sergeig888.wordpress.com/2011/05/06/msft-released-hot-fix-for-e xcel-2007-custom-styles-duplication/
Hey Luke,
Thanks for your help with this crazy “too many cell formats” problem. I followed your advise, and downloaded Open Office. I have to say that I like it better than excel, and I was able to recover my file. My question is, will I eventually encounter the same problems with too many cell formats, or can I format endlessly with Open Office?
John