Excel 2007 SP2 stripping formats

Anyone having even worse format loss issues in 2007 since SP2?

It seems at one client, files I fixed a while ago (and left the fix – deleting non builtin styles), are now dropping bits a pieces of formatting at random. And redefining the normal style number format to date, resetting to number doesn’t fix things, and/or breaks other stuff.

Anyone else seeing this

cheers

Simon

Advertisements

14 Responses to “Excel 2007 SP2 stripping formats”

  1. dougaj4 Says:

    No, but my formatting tends to be pretty basic.

    Are you saving as xls or one of the new formats?

  2. Simon Says:

    Doug – both xls and xlsm
    mine too, this is a clients, and there is plenty of formatting

  3. Adam Vero Says:

    I’ve never noticed this happening.

    Is this on cells with a combination of styles and other (conflicting) formatting applied afterwards? Or even non-conflicting?
    Are the files being round-tripped to different versions or even service packs?
    Is it predictable / reproducable in any way? if you restore a lost format, will it get dropped again or is it much more random than that?

  4. Tony Says:

    It started happening with a single user and then it started happening to her colleague. The files have been in use for a long time. We have also have a number of files where the styles count has exploded to the tens of thousands. We get the problem saving to xlsx, xlsm and xls versions. Excel gives no indication that it has dropped the formatting either when saved or opened.

    The users who are suffering this the most do not use the styles area and format their cells using the cell format box so they are not aware that they have so many styles on their worksheet.

  5. Harlan Grove Says:

    This should be Microdsoft’s job, but have any of you looked into the XLSX files? That is, unzip it as a directory, then parse the worksheet files to check how many distinct formats there are in the workbook. This may indicate whether the problem due to Excel saving lots of styles or due to Excel misreading files when loading them into memory.

    It may be easier to test this by loading the problem files into OpenOffice Calc.

  6. Jim Cone Says:

    RE: “We have also have a number of files where the styles count has exploded to the tens of thousands.”
    ‘–

    That probably explains why I have been getting requests for my free “Formats & Styles” Excel add-in. It removes unused custom number formats and styles.

    These requests are coming from people searching the web for help. (I offered the program in a couple of newsgroup posts).
    The last request I received stated…
    “I too am struggling with the growing number custom styles in Excel 2007.”
    He reported back removing over 1300 styles.

  7. Harlan Grove Says:

    Another thought. Does this happen in files where conditional formatting is used or in any sort of file. I recall reading about copy/paste with conditional formatting combining conditional formats. Would every combination of conditional formats be treated as a different style? Or is this mostly restricted to number formats?

    FWIW re number formats, Excel 2003 oh-so-helpfully autoformats some formulas as #.####… from time to time. And it always adds @#$% date formats like

    [$-409]dddd, mmmm d, yyyy

    whenever I accidentally click on Date in the category list in the Number tab in the format dialog.

    Tangent: it’d be REALLY USEFUL to have a way to TURN OFF automatic number formatting (yes, including NOT converting entries that look like dates into dates). As with most such things in Excel, it’ll never happen. Then again, MSFT did finally fix MOD, so I suppose there’s some hope.

  8. Simon Says:

    Thanks for the input guys – let us know if it fixes it Tony.
    Harlan – yes being able to turn off the the auto formatting would be very good – I’ve seen lots data loss through stuff being converted to the wrong data type. I read of one study that lost tons of fractional data because half of it got converted to dates, but not all of it.
    Jim yep formatting isn’t quite finished in 2007.
    There is a format table in the xml which list each one – it seems to get upset over 40,000 or so – but that doesn’t explain how they keep getting in there.
    I think it will be connected to wbs created in different locales somehow.

  9. Bob Phillips Says:

    I have a styles editing app, and I have noticed that in 2007 if you edit a style using the old style dialog (which my app uses) the name gets changed to Normal. So you strat witha style called Number2Dec and if you edit it, it becomes another Normal style, giving you 2!

  10. Tony Says:

    Well we have made a bit of progress. What we have found is that if you run 2 instances in Excel, any cut and paste between the 2 instances will make any styles held in one to be replicated in the other. In this way the styles count can go through the roof. Eventually you will hit the limit in Excel and it will cause the drop out in the formatting which we have seen here. At this site all the users have dual monitors so this kind of encourages the use of 2 seperate Excel sessions as it makes it easy for cutting and pasting.

    We are also finding that the builtin property on the style is not correctly set. We have a number of files where the count of the builtin styles is not the 47 Excel installs with. Last night I looked at a file with 250 styles set (a small amount for this place!) and when I removed all the non built in styles this count droped to 242. When checking the style names it was obvious that these were all based on the names of the default Excel styles – style name and a number appended to the end. Is it possible that a file with non default styles migrated from an earlier version of Excel would cause these non default styles to be seen as builtin ones in 2007?

  11. Tony Says:

    Thanks for all the info so far folks

  12. Tony Says:

    The [$-409]dddd, mmmm d, yyyy probelm has come back!

    I rebuilt the spreadsheet in a completely new and clean instance of Excel and after a couple of days use it has opened up with the normal style causing all numbers to be seen as dates. If I change the number format associated with Normal it does not correct the format on the cells even though if you check the cells in question they are still showing as having the normal format.

    If you delete the [$-409………. custom format, everything using it reverts to general, unfortunately this includes those cells which are formatted using the item in the date format section of the dialog box.

    It’s truly rubbish!

  13. XLGeek Says:

    Custom styles count explodes when people copy and paste between 2 or more Excel instances, i.e., not between the workbooks.

    Lots of people seem to run into this problem. Most often the issue is related to the exessive number of unused often corrupted styles and not so much cell unique cell format combos. I wrote a utility to fix XL2007 OOXML files that can be saved down to XL2003. Here is the link to the blog post:
    http://sergeig888.spaces.live.com/blog/cns!53E1D37F76F69444!534.entry

    Requres .Net3.5 and MS Excel 2007. Will fix xlsx or xlsm files.

  14. AnonW Says:

    Since updating to Excel 2007 SP2, I’ve lost all of the dates on the X-axis of my charts. As I have one machine which I don’t update that still has SP1, I can still run the charts perfectly there. But I’d like to be able to run them on my main computer.

    Any ideas?

    James

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: