Too many different cell formats

[lex007]

Hi, i am running Excel 2007 and i am getting this error: "Too many different cell formats". It is bizarre, it only occurs to one work book. I am trying to insert a sheet from another work book and i get this error. I've googled it but no luck. I've removed all formats and still the same error. Do any of you know what may be causing it and know how to fix it? Thank you for your time.

 

[Tom Urtis]

It *might* be due to excessive names in the workbook if you are sure all formatting is removed, including conditional formatting.

Look to see how many, if any, named ranges are in the workbook. With the workbook open, from your keyboard press Alt+F11 (careful, that's ALT + F11) then press Ctrl+G.

Type or paste this in:

? ActiveWorkbook.Names.Count

and hit Enter.

Press Alt+Q to return to the worksheet.

Post back with the number that gets produced by doing that.

 

[smiler]

try this

http://support.microsoft.com/kb/213904

 

[Superstar31]

Hi, i am running Excel 2007 and i am getting this error: "Too many different cell formats". It is bizarre, it only occurs to one work book. I am trying to insert a sheet from another work book and i get this error. I've googled it but no luck. I've removed all formats and still the same error. Do any of you know what may be causing it and know how to fix it? Thank you for your time.

I know this is an old thread, but I just came across this problem and found a easy fix

Copy and paste the data into a new excel sheet as text (no formatting)

 

[brichards1]

You get the error because the workbook contains more than 64,000 different combinations of cell formats. A combination is defined as a unique set of formatting elements that are added to a cell. A combination includes all font formatting (typeface, font size, italic, bold, and underline), borders (for example: location, weight, and colour), cell patterns, number formatting, alignment, and cell protection.

You can copy the whole sheet and paste it as text in another sheet and only add needed formatting.

To see more about this and other topics, click here: 'Too many different cell formats' Error | Forum post | 35845
​

 

[BrewMAJ]

I realize this is an old post...
I am using Excel from Office professional Plus 2013. I only have 12 active workbooks, but my workbook with issues has 215 sheets, after I removed a few to see if that would resolve the issue. My workbook size is a little over 5.5M. This workbook has evolved over the years since around 2001. So the workbook has seen several versions of Excel. There are likely other issues than the "Too many different cell formats", but I'm not sure how to troubleshoot Excel. I like the idea of using the Immediate window. I'd like to pursue troubleshooting my issue via the Immediate window for now, but I may have to start a new workbook and port some of the old sheet over. I've also thought about creating a new workbook and linking to the old for some of the data and just not updating the old workbook anymore. One other thing, what impact would my Personal.xlsb and addons have?

 

[Terabapuno1]

but my workbook with issues has 215 sheets, after I removed a few to see if that would resolve the issue. My workbook size is a little over 5.5M. This workbook has evolved over the years since around 2001. So the workbook has seen several versions of Excel. There are likely other issues than the "Too many different cell formats", but I'm not sure how to troubleshoot Excel. I like the idea of using the Immediate window.????



Ali

 

[sts66]

but my workbook with issues has 215 sheets, after I removed a few to see if that would resolve the issue. My workbook size is a little over 5.5M. This workbook has evolved over the years since around 2001.i

I've had similar issues of workbooks growing to enormous sizes, along with the dreaded "too many cell formats" error, which plagued me for years on older workbooks I've used for many years - tried all of the add-ins and tricks to remove excess styles/formats with no joy - learned a couple key things recently I thought I'd share:

1. Deleting sheets from a book may decrease it's size, but rarely will it resolve the excess format problem.

2. Autosave recovery files in various MS Office programs can be very bloated - best to copy stuff to a new file and delete the recovered one.

3. Excel 2003 charts with large numbers of data points increase file sizes enormously - one workbook I have is 3MB w/o the chart, 8MB w/chart (!?!).

4. Recently ran into some odd problem that gave me the "file validation error", saved it in Excel95/2003 format, and file size DOUBLED from 6MB to 12MB. However, saving as Excel 2003, then reopening it, gives me the "created in newer version of Excel" error, which can be ignored, but is a PITA, something I still haven't figured out how to fix.

Lastly, I finally resolved the excess formatting problem, and it was in a place I've never seen mentioned anywhere on the net (I've been looking for a solutions for years) - it was in the "Custom Styles":

a) put cursor/focus on a cell that has a value and a format applied
b) in the menu dropdown list, choose "format/cells"
c) at the bottom of the number format list, find "custom", click on it
d) scroll down to the bottom of the list - upper items are default formats, bottom of list will be custom formats you've created over time, perhaps from every workbook you've ever created
e) starting at the bottom, highlight a custom format you know you don't need, put a checkmark in the "Delete" box, then click ok. Repeat for all unused custom formats - I found dozens, including % and $ formats to 5 decimal points, and some very odd date/time formats - I know these are carryovers from old work files I brought home, recognized the weird formats we had to create to input data from instrumentation systems that had time/date codes that needed to be converted in order to be plotted in Excel.

Voila! Excess formatting problem solved!

Still looking for solutions to items in #4 above, but they don't prevent me from modifying workbooks, are more of an annoyance than anything else - they started occurring after I downloaded an Excel 2007 file from a stock broker website, believe something got corrupted in my Excel installation during the conversion process, but I'm not about to uninstall/reinstall Office to fix it, have years of customizations that would be lost, not worth it.

 

[Terabapuno1]

but my workbook with issues has 215 sheets, after I removed a few to see if that would resolve the issue. My workbook size is a little over 5.5M. This workbook has evolved over the years since around 2001. So the workbook has seen several versions of Excel. There are likely other issues than the "Too many different cell formats", but I'm not sure how to troubleshoot Excel. I like the idea of using the Immediate window.????



== Crossword Champ - Free Online Crossword Puzzles ==

 

[Yatocm]

I've had a similar problem and my only way to fix it was to reset to factory default