Software

Ask something
Discussion Locked

Excel format problem

0 helpful
15 replies

by jereg 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

15 replies

by nikki96

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

by jereg

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

by jereg

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

by jim phelps

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.

by jereg

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

by dklippert

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

by jereg

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!

by firstpeter

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.

by firstpeter

Incidentally, I forgot to mention that you probably want to have him do those formatting changes in the original workbook before he imports it into Excel, since you mentioned that he couldn't change anything once in Excel.

by jereg

My first suggestion was to clean up the workbook and reduce the style settings. We tried that, but kept having the same problems. Seems to be a problem converting from 1-2-3.
Thanks for giving it a shot.

by ananthap

Another possibility to try.

It might happen that the problem is since the export (from Lotus) process doesn't preserve the default setting and each instance of the default clashes with your Excel default (Dont remember off hand where its stored. XLA?, XLT?). This might be un-necessarily ramping up the number of distinct formats and crossing the limit. So, you might like to change the excle default for the boss.

(2) On similar lines, will using MS-Binder help?
HTH

End

by jereg

Seems the problem was excess format when conmverting. See #3. Thanks for your help.

This discussion is closed to new replies.