# Excel 2007 "Too many different cell formats"



## LG2 (Jan 25, 2010)

I have searched the forum ... there is an old post, that is now closed, about the same issue. But, no answers were posted. So, I am hoping somebody can help.

I have multiple, very involved, excel sheets. Usually, they have different highlighting colors, several different fonts, can have 7 to 12 tabs, multiple formulas (some a bit involved), with up to several thousand rows of data and up to 10 to 15 columns. 

I frequently receive the error message "Too many different cell formats" when I am attempting to copy and/or paste data. It even happens if I try to change a font or borders within a workbook. I know the reason why this happens .... Excel is just a program, with limits. For example, some maximum limits are (I am listing a few, in case someone wants a quick reference):
1,048,576 rows
16,384 columns
64,000 unique cell formats/cell styles
32 fill styles
512 unique font types per workbook
200 to 250 (depending on version) numbers of formats (I think this is the one that gets me)

You can get the whole list here: Excel specifications and limits - Excel - Office.com

My question is: how can I have the "cell formats" listed, so I can see where I have over-run the limits, so I can get rid of a few?

Thank you for your help!

:banghead:


----------



## Corday (Mar 3, 2010)

Excel 2010 expanded the capacity over previous versions and might be your solution. The URL you gave: Excel specifications and limits - Excel - Office.com shows the 2010 specs.
Quattro Pro had greater total limits, but I think Excel caught up in 2007. Corel released a new version this year. I don't know it's limitations. As far as measuring where you could save some space, I'm unfamiliar with any device to do that.


----------



## LG2 (Jan 25, 2010)

Thank you. I upgraded to 2010 after I fought this problem for a bit and it seems as if that problem has been negated, for now. But, as I send many reports to others, who do not yet have 2010, I would still like to see if there is a way to have excel show a list of the different formats in a specific worksheet, so I can clean it up, if possible.

Also, I clicked on the link I posted, to verify which version is referenced in it. The link I posted did reference the 2007 limitations, whereas your link points to 2010's limits. Thank you, I always like to have that information at hand.


----------



## Corday (Mar 3, 2010)

Sorry mate, I wish I knew of a way to do what you want to. Maybe someone else will post. I do have a very impractical workaround. If a recipient ever tells you the sheet was unusable, they could download the trial version of Excel 2010. They should put it in a separate directory and it has a set number of re-arms. 2013 will be out shortly and preview versions are already available.


----------



## LG2 (Jan 25, 2010)

:flowers:

Thanks, Corday! I appreciate your help.


----------



## LG2 (Jan 25, 2010)

I am still hoping someone can help me try to figure out how to display a list of formats being used in an excel workbook &/or sheet. I have several reports that have multiple years data, with each year on a separate tab. As I am updating information, for the current year, I am still receiving "too many formats" and excel cannot paste the data. Excel is also "exploding" on me, going into a non-recoverable workbook message and I have lost a lot of work and time. I really don't want to build a new workbook, with minimal formatting, for data back to 2009 as it will be very time consuming. I would prefer to go into the workbook and clean it up, if possible. I do need to keep all of this data in one workbook as past trends are looked at, frequently. These problems are still happening, even though I have upgraded to excel 2010. 

Does anyone have any ideas?


----------



## Corday (Mar 3, 2010)

The 64 bit version of Excel 2010 allows much larger Workbooks.


----------



## LG2 (Jan 25, 2010)

Thank you, Corday. I appreciate the time and attention you have given me, trying to help with this problem.

Unfortunately, my company does not have any other programs/versions available to me. So, I am trying to make this work. 

My biggest "problem child" is a workbook created in 2008 or 2009 by another user, it still has a password protect on it. I think it was created by copying another report and just had some formatting changed. Throughout the years, as more data has been added, it has become rather unwieldy. I cringe to think of the time it will take to completely rebuild it, from scratch, typing in all of the data. So, I am hoping there may be another way around it. 

I started by copying a page to a fresh workbook, then copying it again and just pasting the values and deleting the original, copied, page. But, I will still have to format it so it appears the same as it does now. I stopped doing this, until I checked it out, because I do not want to bring over any of the corrupt data, formatting or whatever is causing it to explode (my term). I am also encountering this problem, sporadically, on other workbooks. I do a lot of formulas for calculating totals, averages and quarterly data, with charts, as well as formatting for presentation and readability, to print for meetings. But, it is not as frustrating as this one workbook is. Any ideas?


----------



## Corday (Mar 3, 2010)

I'm posting this to staff as someone might have done this in the past.


----------



## Elkar (Mar 17, 2008)

I've run into this problem a couple times before. From what I could find, it has more to do with corrupted Styles being used in the workbook rather than an actual excess of formats. A workaround that seemed to work for one of the workbooks was to save the file as an html file, then re-save it back to a xlsx file. This seems to strip away the corrupted Style, but may have other consequences as well. Might be worth a try though.


----------

