Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Excel keeps increasing in size

    Our work excel file which is shared between 4 people keeps creeping up in size in one day. It's been doing this for several months now. We even upgraded the version and continues to increase. Formatting is not extensive, the active cells is limited. There are only 6 tabs. The file does link to numerous other excel files though for updates. We have to go back to a backup sometimes just to get the file size down. The reason the file size is important is that it takes longer and longer to save the file.

    Any suggestions on how to fix this?

    Thanks!
    KZ

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    Do you have track changes turned on?

    cheers, Paul

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    This issue has been discussed in the forum many times. Ruling out any obvious reasons, if I remember correctly, the general consensus was that the spreadsheet was corrupted and the workaround was that the user had to copy/paste the data into a new workbook.

    Take a look at this thread for a possible solution
    http://windowssecrets.com/forums/sho...highlight=size

    Maud
    Last edited by Maudibe; 2015-03-04 at 06:07.

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks. I'll take a look at that today.

    KZ

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi KZ

    If you have tracking changes turned ON (as Paul T mentions in post#2), this can cause considerable bloating with shared workbooks.

    If this is the case, you can 'clear' this by re-setting the file to 'exclusive mode', and then switching back to 'shared mode'.

    The act of switching back to non-shared (i.e. 'exclusive' mode) causes the workbook to be re-saved, with the changed history removed.

    You can then set the file back to shared mode.
    NOTE: You should only do this when all other Users have 'logged' out.

    I created a workbook to help me reset such 'bloated' files.
    See attached. It lists all Users who have a specified shared workbook open.

    Note: to test this, make a copy of your shared file, move it to a different folder, then use my file to select that copy. Then check the file sizes before and after.

    If your shared workbook is not in binary format (i.e. xxxxx.xlsb ), saving in this .xlsb format can also considerably reduce the file size (generally by 50% or more).
    There are things other than cell-formats and excessive used-range that can also cause file size bloat.

    zeddy
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Maud,
    The indication of that thread looks like it was saying to download a MS product that basically clears the unused cells. We have already done that manually and it didn't change anything.
    zeddy,
    We do not have tracking changes turned on. We have tried saving to binary. Today the file went from 7mb to 9mb, then I logged out and the file dropped back to 7. Last week the file size was only 4mb.
    One thing we haven't tried yet was to turn off the sharing, save, then turn it back on.

    KZ

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi KZ

    Does your file contain multiple pivot tables?
    If your workbook contains multiple PivotTables, all based on a single data source, Excel may create an intermediate dataset for each PivotTable, instead of using just one intermediate dataset. This could increase the size of your workbook very rapidly.
    If so, clear the Save Source Data with File check box on the pivot options, and choose Refresh on Open check box.

    zeddy

  8. #8
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts
    There are actually no pivot tables in the spreadsheet.
    What my boss ended up doing yesterday was to split up the spreadsheet into multiple ones then creating links to the other spreadsheets and that has kept that size down considerably. We'll see how that works.

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi KZ

    Do you have a lot of array formulas in the file????

    zeddy

  10. #10
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts
    What are array formulas??

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi KZ

    ..you may want to google that.

    zeddy

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    kzkz,

    If you made several copies of your workbook but deleted a different tab from each one, note the change in the size of each workbook before and after. It may clue you into which sheet is problematic. Once determined, copy/paste the data from that sheet on to a new sheet then delete the one that is causing the issues.

  13. #13
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts
    We actually did that awhile back and copy/pasted starting a new spreadsheet. It too grew in size.
    What we did now (which has been working for a week +) is simply referencing out to get data from another spreadsheet instead of having all the formulas in the spreadsheet. This has kept the file size real tiny.

Posting Permissions

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