+ Reply to Thread
Results 1 to 5 of 5

Too many cell formats

  1. #1
    Registered User
    Join Date
    02-21-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2

    Too many cell formats

    Hi

    I have a huge spreadsheet that has evolved over a while and which also included worksheets copied in from other workbooks. As a result, we keep getting the "Too Many Different Cell Formats" message meaning we are unable to apply any additional, new formats to cells without formatting.

    I have downloaded XLCleanerDotNET4.0 which will remove ALL formatting, but as that would mean about a day's work to apply the formatting we do want to keep, I am loathe to use it (but will if needed).

    Is there any way to remove any unused cell formatting / style which doesn't affect the formats/styles that I am using?

    cheers!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Too many cell formats

    I hate to say this, but I think you need to bite the bullet, clear it out and start again with what is essential.

    Is it just simple formatting? Background colours, font colours, fonts? Is there a lot of Conditional Formatting? Do you just format the cells in use, or all of the cells on each sheet?

    Can you post a sample workbook?

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Too many cell formats

    Excel 2013 and above also have the inquire addin that can help with cleanup of access formatting and does not clean all formatting.

    https://support.office.com/en-us/art...9-4874f7474738

    you can also go sheet by sheet. thus seeing intermediate results.

    Please also read this article on how and why it happens.
    It will explain different methods and refers to (other) third party tools they recommend
    https://support.microsoft.com/en-ca/...ssage-in-excel

  4. #4
    Registered User
    Join Date
    02-21-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2

    Re: Too many cell formats

    thanks, Trevor - unfortunately its quite confidential so unable to upload...

  5. #5
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Too many cell formats

    https://support.microsoft.com/en-us/...ssage-in-excel

    Go to Method 3 of the article. I used to use the XLStyles Tool to clean up all my companies workbooks that were plagued by the "Too many cell Styles" warning.

    You can first see the issue in person if you open your workbook > go to HOME ribbon > and then select the Cell Styles Drop down. There probably is a ridiculous amount of styles located there.

    I have only used the XLstyles tool on windows 7. You should be able to download it from the Microsoft store for windows 8 and up. It was created by a russian "Sergey" if i remember correctly. Worked like a charm for me. It removed all unnecessary Named ranges/and cell styles. While leaving all your formatting and formulas.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 10-01-2014, 03:18 PM
  2. [SOLVED] PasteSpecial with number formats but without conditional formats or borders
    By bodhi808 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-18-2013, 09:58 AM
  3. Problem copying Page formats and print formats to another workbook
    By Chrisgeni in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-27-2013, 08:05 AM
  4. Copying formats - column widths, formats, outlining to worksheets
    By DavidBr318 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-17-2005, 09:05 AM
  5. [SOLVED] Too many different cell formats
    By Craig in forum Excel General
    Replies: 4
    Last Post: 04-19-2005, 06:06 PM
  6. Formats: Too many different cell formats error message
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-31-2005, 10:06 PM
  7. CELL FORMATS
    By Speedy in forum Excel General
    Replies: 0
    Last Post: 01-18-2005, 01:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1