General discussion

Locked

Excel format problem

By jereg ·
My boss has created a rather large workbook in Lotus 1-2-3 with 10 or so worksheets. I am converting the company to MS Office, so he loaded his workbooks into Excel. He now gets an error that says "Too many different cell formats" and won't let him change anything. He has put in a very wide array of colors, font types, font sizes, borders etc. It's basicly used as a charting and forcasting tool for the business. The fonts and colors allow him to quickly identify the data that he wants to see.
Checking MS web site, the is one document. It says:
This problem occurs when the workbook contains more than approximately 4,000 different combinations of cell formats. A combination is defined as a unique set of formatting elements that are applied to a cell. A combination includes all font formatting (for example: typeface, font size, italic, bold, and underline), borders (for example: location, weight, and color), cell patterns, number formatting, alignment, and cell protection.

So, I told him to simply his worksheet. Because of the complexity, neither one of knows how to identify where all the formating is taking place.

Does anyone, Excell expert have any ideas about what to do?

This conversation is currently closed to new comments.

15 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel format problem

by jereg In reply to Excel format problem

Point value changed by question poster.

Collapse -

Excel format problem

by nikki96 In reply to Excel format problem

Is there any reason you can't simply export it as a CSV file with no formatting and re-import it and reformat it?

Collapse -

Excel format problem

by jereg In reply to Excel format problem

I hadn's thought of this at first, but trying it didn't seem to help. A good try though.

Collapse -

Excel format problem

by jereg In reply to Excel format problem

I hadn't thought of the export, but I'm not sure it's possible with the complexity of the workbook.

Collapse -

Excel format problem

by Jim Phelps In reply to Excel format problem

Save it in a format which Excel can recognize. For example, perhaps 1-2-3 will let you "Save As" an Excel spreadsheet. If so, it may do a better job of converting the document than Excel would. Or pick some other file format which Excel recognizes, if 1-2-3 can't save in Excel format.

Collapse -

Excel format problem

by jereg In reply to Excel format problem

We had already brought the into Excel as an .xls file. That's when the problem started. Good thought, though.

Collapse -

Excel format problem

by DKlippert In reply to Excel format problem

All of the suggestions in Error Message: Too Many Different Cell Formats 213904 are good.

http://tinyurl.com/99ex

Also see the following. It works with later versions as well. One of the problems can be formatting an entire row or column when only a few cells are needed.

Large File Size After Saving WK4 File as Excel Workbook 123269

http://tinyurl.com/99fr

And John Walkenbach's macro
Automatically Resetting the Last Cell

http://tinyurl.com/99fy

Collapse -

Excel format problem

by DKlippert In reply to Excel format problem
Collapse -

Excel format problem

by jereg In reply to Excel format problem

You've hit the nail on the head. I had missed the large file size after converting from 1-2-3. I downloaded the xclean macro and it cleaned the workbook up. Not that you really need the points,(grin) but you've earned them!

Collapse -

Excel format problem

by FirstPeter In reply to Excel format problem

The first thing I'd try to simplify that is to make the fonts standard. Highlight all the cells on all the sheets and make the fonts the same, say Times New Roman 8, not bold, not italicized, not underlined.

If that doesn't work, move on to cellcoloring, then alignment (eliminate all coloring, justify all cells left/right/center). So, at the end of this step you'd have all the fonts at Times New Roman 8, not bold, not italicized, not underlined, no coloring, all left justified.

You mayalso make sure that your number formatting is consistent. For example, Excel sees the format #,### and #,##0 as different, even though they appear the same for all non-zero numbers. Also make sure that all negatives are formatted the same.

Worst case, start unprotecting cells. Obviously there's a reason they were protected, but that may fix it for you.

Back to Software Forum
15 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums