Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Feb 2001
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Too Many Different Cell Formats

    Has anyone seen this error message when using Excel? I have found a Microsoft Article Q163678 that recognizes it as a problem, but tells you to change the number of formats you have used. It indicates that we have used over 4000 variations of formats in this one workbook, but there's no way to see what formats have been used. I found a routine online that will display all the different formats that were utilized per tab on the workbook. Even running this utility, I'm no where close to using 4000 different formats. Can anyone help us understand what's going on? Thanks in advance for your help.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Too Many Different Cell Formats

    Hi,
    If you select a cell, choose Format-Cells from the menu bar and then select Custom on the Number tab, are there thousands of entries there? I believe that Excel will store every format that has ever been applied to cells in that workbook even if the format's not in use anymore. Furthermore, if you move/copy a sheet to another workbook, it seems to carry all the custom formats of the original workbook with it, which can add to the problem.
    I'd suggest copy and pasting all your data into a new workbook and seeing if that helps or going through the list of custom formats and deleting some (very tedious).
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Too Many Different Cell Formats

    Carango, I frequently see the message, but I have never bothered with research you have done. My guess is that custom formats are a combination of active sheet formats and available default formats, because I'm certain I have never gotten anywhere near 4,000 total formats.

    My answer has been to delete from ...xlstartbook.xlt all the foreign currency formats I will not need. This is not much of a solution for anyone who works with multiple foreign currencies.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Too Many Different Cell Formats

    Rory,

    If I understood your post, it seemed to suggest many formats for the custom format list. If this is what you intended, then I'm not sure I agree.

    I had a spreadsheet where I got the number of custom formats up to about 270. I don't remember but it was in the 200-300 range when I would get a no more custom formats allowed msg; Laura Stewart had an article in WOW some time last year in her series on custom formating. It depends on the number of characters used by the formats, not the number of formats itself. You can delete 1 or 2 formats with lots of characters and squeeze in 3 or 4 short ones.

    The limit is on a per workbook basis.

    Fred

  5. #5
    New Lounger
    Join Date
    Mar 2001
    Location
    Blue Mountains, New South Wales, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too Many Different Cell Formats

    Rory,

    I found a subroutine that supposedly deletes unused custom number formats. It's in a newsletter called the Excel Experts E-letter, and archives are available on John Walkenbach Spreadsheet website www.j-walk.com.

    It's in newsletter No. 7 (eee007.txt) I sent it to Laura last year and she commented it didn't work quite right. I've found it deletes custom number formats that you've added, but not the list that seems to appear in a default new workbook.

    I'm using Excel 97 SR-2

    good luck

    pt
    Attached Files Attached Files

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Too Many Different Cell Formats

    Hi Fred,
    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> yes I did mean too many formats rather than too many characters in the formats! Thanks for setting me straight - it's not something I've ever actually run into as I try to avoid using too many different formats. My apologies to all for the misinformation! <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Too Many Different Cell Formats

    Rory,

    It was actually Laura who set you straight - I just conveyed the msg and my own experience.

    I recall now that I was preparing a set of notes for my computer club's Excel group on custom formatting. So I was playing around with custom formats - just adding one after the other. I just looked back at my notes - they say the limit is 180-260. And this does seem right looking at the notes. And you cannot delete the ones that come predefined (about 36) - the Delete button is grayed out.

    Fred

Posting Permissions

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