Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Down Under
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am hoping for some help in using formatting in Excel - my problem is that I have to use a spreadsheet on two different pc that run different versions of Excel.

    On my my pc I am running Office Prof 2007 - the other one has only 2003.
    I have a rather large spreadsheet with 2000 rows and perhaps 100 columns. The data relates to inventory and sales figures and is constantly updated. Not all stock items are active, so to make it visually easier to work with the spreadsheet I have used highlighting (fill in colours) to distinguish between active items, new items and inactive items. Otherwise I am using some generic formatting like (centred) or set some colums as numbers. For two columns I am using conditional formatting. Overall I don't think that my spreadsheet is very complicated or demanding.

    This all worked fine until recently when all of a sudden all formatting was gone. It appears that I have gone beyond the limit of what excel can handle with regards to formatting. I tried to use styles instead and recreated my spreadsheet with highlighting via styles but after I closed it, it again lost all formatting.
    As my secondary workstation is running Office 2003 I have to save my worksheet as .xls instead of xlsx.

    How should I handle this issue? I did not have any problems with this prior to upgrading to 2007. How can I use my worksheet with visual pointers and ensure that
    • formatting is not lost
    • the worksheet can be opened and ideally modified on both systems I am using

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Have you checked how many styles are in your workbook?
    They have a tendency to multiple when using xl2007.
    I have seen reports of workbooks with thousands of styles that magically appeared.

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Down Under
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Jim, not sure how to check that exactly.

    The issue first appeared when I did not use styles but just chose the formatting details (colour, highlighting etc) by choosing the format cell functions - most of it were just simple colour highlights.

    When i lost all formatting the first time, I switched to styles thinking this would solve the issue. I have created myself perhaps 8 new styles - the rest are all the default styles. Not sure why MS actually set these up as I find it awkward to scroll through this long list of styles I am not using. It's easy enough to create a style when I need it, so why offer 200 odd default styles?

    E.g. why do I have 23 buttons for Neutral named 2 to 21, highlighted in yellow and all apparently the same?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I just counted on a plain new workbook and there are no more than 42 predefined styles. ONE of them is Neutral. So your workbook is definitely "dirty" with styles.
    You can right click entries in the styles dropdown to delete them.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Beethoven,

    Just a couple of thoughts.

    1. How are you saving the file? I would suggest always saving the file as an .xls file rather than converting it back and forth between .xls & .xlsx (2003 - 2007)

    2. Is there something in the data that indicates the status, i.e. new, inactive, etc. If so you could simply apply a conditional format to the entire worksheet to do the row highlighting.

    Hope this helps some.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    My free Excel add-in "Formats & Styles" will list/remove unused Styles in a workbook.
    It can also handle custom number formats and conditional formats.
    Some have been quite pleased with the results.
    Download from... http://excelusergroup.org/media/p/4861.aspx

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Down Under
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Jim,

    I downloaded and installed your add-in but am a bit unsure how to use it. It does indeed show me more than 4000 unused custom styles but clicking the delete function, no unused custom files can be found.
    What am I doing wrong?

  8. #8
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Maybe...
    The list of unused styles is displayed in a new workbook.
    You have to switch back to your original workbook before running the program again.

    If that is not the problem, I would very much like to get a copy of your workbook to see what is going on.
    '--
    Jim Cone
    ( 30 + ways to sort )

  9. #9
    New Lounger
    Join Date
    Dec 2009
    Location
    Down Under
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Jim,
    thanks again - I did not realise that the result is shown in a fresh independant spreadsheet. Now, doing it correctly, 4317 unused custom styles were deleted and only 54 styles were left.

    This is much clearer for me and easier to choose when working with my spreadsheets. I am not sure why MS decided to include to so many options given that it is easy enough to create your own on demand.

  10. #10
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    You are welcome. Glad it helped.
    For others interested, the link again is: Formats and Styles
    (no registration required)
    '--
    Jim Cone

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Beethoven View Post
    I am not sure why MS decided to include to so many options given that it is easy enough to create your own on demand.
    MS did not include all of the styles you have deleted, they were added to your workbook during copy/paste actions pasting in information from other workbooks.

    Copying a worksheet from workbook A to workbook B can have a similar effect.

    The fact that your workbook had so many styles proves that it has a very long editing history.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    New Lounger
    Join Date
    Dec 2009
    Location
    Down Under
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by pieterse View Post
    Copying a worksheet from workbook A to workbook B can have a similar effect.

    The fact that your workbook had so many styles proves that it has a very long editing history.
    Pieter, you are quite right - this particular worksheet has indeed a long history and I guess for some amateur like me it's sometimes not obvious what effect highlighting one cell here or using a bold there may have for the overall sheet. I am glad that with Jim's add-in I was able to cut through that.

Posting Permissions

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