Too Many Different Cell Formats

Question

I have a massive file with 10 sheets recreated from scratch, 12 sheets updated, 5 sheets loaded with raw data, and 7 sheets that are used by the macros for the report. I have recently added a new sheet and am running into the Excel "Too many different cell formats" problem.
I have read some of the google search results and they say I should simplify the formatting, but I don't even know how I got up to 4000 distinct cell formats, let alone how much I'm using which ones, so I can remove some. It is also popping up some times the file is run, but not all, until such time as it comes up, and then it happens every time it is run. Since the macro is doing so much of the work, including creating the 10 sheets from scratch, I am not sure what to do.
Does anyone know of a Macro I could run to get a listing of all the cell formats and how many cells are using them?
Does anyone know of a program they trust to help remove excess cell formats?
Thanks,
Craig

Answer 1

SpreadsheetGear for .NET will coallesce non-unique formats which may help with your situation.

You can download the free trial here if you want to see whether it helps. Just load the workbook into the "SpreadsheetGear 2009 for Windows" application which is installed with the evaluation software and then save the workbook.

If you actually have that many unique formats, you will have to simplify. Every unique combination of font / cell color (Interior), number format, horizontal and vertical alignment, borders, indent level, and probably a few things I'm not thinking of will cause a unique entry in the table.

Another option is to switch to Excel 2007 which has the limit on unique cell formats increased from 4,000 to 64K.

Disclaimer: I own SpreadsheetGear LLC

Answer 2

"Cell formats" are complicated. Cells do not really have a "format". They have a font (which itself has a name and a size), a NumberFormat, Height, Width, Orientation, etc.

So you need to define what you mean by "format".

Below is code to get the Font Name and Size. You can substitute whatever attributes you like.

The code below assumes you have created a Worksheet named "Formats" in the workbook. After you run the macro, the Font Names and sizes will be listed in that worksheet.

Public Sub GetFormats()

Dim CurrentSheet As Integer
Dim UsedRange As Range
Dim CurrentCell As Range
Dim rw As Long

Sheets("Formats").Cells.ClearContents
rw = 1
For CurrentSheet = 1 To Sheets.Count
    Set UsedRange = Range(Sheets(CurrentSheet).Range("A1"), Sheets(CurrentSheet).Range("A1").SpecialCells(xlLastCell))
    For Each CurrentCell In UsedRange
        FontUsed = CurrentCell.Font.Name + ":" + CStr(CurrentCell.Font.Size)
        If Sheets("Formats").Cells.Find(FontUsed) Is Nothing Then
            Sheets("Formats").Cells(rw, 1).Value = FontUsed
            rw = rw + 1
        End If
    Next
Next CurrentSheet

End Sub

Answer 3

No programming required solution?

Your statement of the issue didn't indicate whether keeping the formats is important to you - does that mean that they are not? If not, then perhaps select all the worksheets in the workbook and then do a select all, and then bulk change the format by removing all formatting?

C!

Answer 4

Lots of people seem to run into this problem. Most often the issue is related to the excessive number of unused and often corrupted styles and not so much the total count of the 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. The post has a ReadMe file to go with the app.

No need to run the risk of further corrupting your file by using Open Office like it is suggested on some other forums