Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    192
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Good Morning, I have a user that has a very involved Excel 2007 spreadsheet with lots of internal links, etc. This morning the file size increased from 2 mg to 45 mg and is taking forever to load, move betwen sheets, etc. From Googling I figured out it might be because some worksheet increased in size. A suggestion ws to do a CTRL + End on every worksheet. We are doing that, and I did find one that appears to have grown very large.

    My question, how do you bring a worksheet back to it's "real" size if there's nothing in the cells? Do you have to delete them? Any other suggestions for the reasons why the file has increased in size are welcome. Thanks much, Judy
    Thanks much, Judy Crawford

  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
    Select the first blank row below the last row of data, then Ctrl+Shift+End and delete the entire rows. Repeat for columns, then save the file and see if the size has shrunk.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    192
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks, Rory. That likely would have worked. However, we'd found the answer late Friday afternoon, and I hadn't had time to post it here. There is a Microsoft Add-In that when run cleanses out excess formatting on Excel spreadsheets.. We downloaded it and it worked great. In fact we've already shared it with our heavy Excel users just in case they need it in the future. Here's the link:
    http://www.microsoft.com/downloads/e...displaylang=en

    I'd recommend that everyone put this in their arsenal of quick fixes. With this fix we didn't even need to know the worksheet that had the problem, and that made it a timesaver because this spreadsheet had at least 24 worksheets! Thanks much, all.
    Thanks much, Judy Crawford

  4. #4
    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
    Good to know - thanks. They seem to have kept that one pretty quiet, as I don;'t think I've seen it mentioned before.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Good find, thank you.

    I wish there were something similar to remove duplicate formatting - to avoid the dreaded "you have too many formats" in Excel 2003 !

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    I just want to make sure I understand what's going on here.

    If I select a column and format it a certain way (e.g., some form of date) but I only enter, say, 100 rows, the resulting file size is bigger than if I had only formatted those 100 rows.

    The MS add-in will unformat (do the equivalent of a "Clear All") rows 101 thru whatever (depending on version), resulting in a smaller file.

    Now if I come back to that file later and add an entry in row 101 for that col, it will be formated as General so I'll have to extend the formats.

    Thanks.

    Fred

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    192
    Thanks
    2
    Thanked 0 Times in 0 Posts
    That is my understanding. In our case something, that the user was unaware of doing. Cause some kind of format in the entire worksheet, that is all the way to the last possible row and column. This was a dramatic increase in size and hence an immediate impact on response time. Likely if you only increased on column there would unlikely be much of an impact. Maybe I'll try it just for fun. Thanks for your post and reinteration of the problem.
    Thanks much, Judy Crawford

  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
    For what it is worth, the Microsoft xlsclean.exe download that was recommended only does a partial clean. It removes all content from the unused rows at the bottom of a sheet, but does not clear the columns to the right of the data.
    I took a peak at the code and for those interested the "clear" line for the columns was omitted...
    '---
    Set ur = wksWks.Rows(r + 1 & ":" & wksWks.Rows.Count)
    ur.Clear
    'Reset row height which can also cause the lastcell to be innacurate
    ur.EntireRow.RowHeight = wksWks.StandardHeight

    Set ur = wksWks.Range(wksWks.Cells(1, c + 1), wksWks.Cells(1, 256)).EntireColumn
    'Reset column width which can also cause the lastcell to be innacurate
    ur.EntireColumn.ColumnWidth = wksWks.StandardWidth
    '---

    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (Extras for Excel is worth a look)

  9. #9
    New Lounger
    Join Date
    Jun 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ahhhhh, bless you for sharing that Judy! I had the same thing happen with one of my files - I saved a new copy of the file to start a new month's worth of data, and -removed- a whole bunch of data, and yet my file size got 7 times bigger! Downloaded the add-in and it's back down to a size I would expect. Relief! ^_^

Posting Permissions

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