Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    I have a very large workbook. I got the dreaded too many cell formats error, even when copying an existing format to another cell which MUST be properly formatted (for a presentation). I went to many (apx 20) sheets where the formatting isn't important and simply cleared the formatting (Edit / Clear / Formats). I can now format want I want. My question is -- am I playing with fire, temjpting fate etc to continue to work in this apparently ok workbook? If not, what should I do? Is this messeage a precursor to file corruption? .Very sorry, I know my emergencies are not your problem, but in this case I could really be up the creek -- any advice would be greatly appreciated.

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Lingyai,

    To answer your question: I do not believe the "Too many cell formats" is a forwarning to a corrupt workbook. If you think of it, you can have a small single sheet workbook with say - 4001 different cell formats, and you may just get the same warning coming up!

    I think that your workbook may be more prone to corruption due to its enormous size more that the fact that the cell formatting limitation is being displayed.

    Below in the links, there is some advice on how to "unregister" some of those cell formats, forcing excel to not see the old cell formats used. Try follow the advice and see if it works. (Just make a backup copy of the file first)

    Hope some of the advice below assists you!

    Article explaining how Excel registers cell formats:
    http://support.microsoft.com/kb/213904

    Quoted from:
    http://excel.tips.net/Pages/T002133_No_Mor...n_Be_Added.html

    Every time you change the format of a cell to a "custom format" you are adding formats to Excel. According to Excel's specifications, the number of custom formats you can use is only limited by memory, but you must remember that limitation is theoretical--it is very possible that you will run out of space for custom formats long before you run out of memory.

    Unfortunately, there is no easy way to correct this problem. Once a custom format is created, Excel "remembers" it, and you cannot get rid of it easily. There are a few different things you can try, however.

    > Use File | Save As to save the workbook under a different name.
    > Save the worksheets as HTML files, and then load them from HTML back into Excel. (You may loose some formatting during the round trip, but it should help clear things up.)
    > Copy the contents of each worksheet to a new, blank workbook. Don't use Edit | Move or Copy Sheet, as this can also move or copy your custom formats. You should instead select all the cells in a worksheet, press Ctrl+C, go to the new worksheet, and press Ctrl+V.

    An ADD-IN tool to help reduce cell style limitation...
    See: http://office.microsoft.com/en-us/marketpl...1938821033.aspx
    Regards,
    Rudi

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I wrote an article about styles, which may help you prevent the issue in future:
    www.jkp-ads.com/articles/styles00.asp
    Also published here:
    http://msdn.microsoft.com/en-us/library/dd537556.aspx
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •