Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Size of Excel file (Excel 2003)

    Here's a hot one -- I have an Excel file that I use to schedule staff. The 2006 schedule file is about 10MB. I took 2006, changed the dates to 2007.....vritually everything else about the file remains the same as 2006 (b/c the only thing that changed were the dates). HOWEVER, the 2007 file is now almost 29 MB.......what happened ? There are some macros, but the same macros are in both workbooks (2006 & 2007). I even removed about 30 modules from 2007 and it is still huge....any ideas, anyone ???

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Size of Excel file (Excel 2003)

    Check whether links to the 2006 file have been left, ofr example by inspecting Edit | Links...

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Size of Excel file (Excel 2003)

    <UL><LI>I would bet that you have applied row and column formatting to an entire sheet(s) rather than just the used (or needed) cells. As an example, you may have clicked a column header ("A") and applied bold or some type of numeric format to the column. Doing this would apply formats to more than 65,000 cells. This would certainly increase the file size. You should only apply the formats to the cells you will use.

    <LI>Check the scroll bars at the bottom and right of your Excel screen. When you mouse the scroll bar all the way to the bottom or right, it should only move the screen to the far right or bottom of your actual work area. If the scroll bar carries you to unused areas of the worksheet, then that would account for some of the bulk. You can select all of the rows that you are not using and then click Edit|Delete, repeat this with the unused columns and then immediately save your workbook. This alone will almost always reduce your file size.

    <LI>If your actual work area occupies a block of cells (say, A1:G40) and you've hidden a formula in an "out-of-the-way" cell like IV4000, then every cell between G40 and IV4000 is being unnecessarily used.

    <LI>One trick to reduce file size is to save your file as an HTML file and then use excel to re-open the HTML file, this time saving it as an XLS file (roundtripping through HTML). Some folks would use this as a first resort as it is a good cure for a corrupted file. I personally would try other remedies first, as the HTML route will undo a bunch of formatting that you would normally intend to keep.

    <LI>This subject has been covered several times before and others have offered very useful information. Please take a look at their postings...
    1. <LI><!post=Compacting Excel Workbooks,133048>Compacting Excel Workbooks<!/post> <LI><!post=Reduce the size of an XLS file,501067>Reduce the size of an XLS file<!/post> <LI><!post=Crazy file size behaviour,482586>Crazy file size behaviour<!/post>
    [/list]
    - Ricky

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Size of Excel file (Excel 2003)

    I disagree with your first point. Applying formatting to entire rows and/or columns is relatively efficient.

    - an empty workbook (3 sheets): 14 Kb
    - entire column A with a yellow fill: 14 Kb
    - 500 rows in column A filled: 29 Kb
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Size of Excel file (Excel 2003)

    I tried a "test" of my own, similar to yours, except that I added actual data to the 500 cells...

    -an empty workbook with three sheets: 13.5kb
    -500 rows in column A filled with yellow, % format, with 500 numeric entries (data): 32kb
    -same as above, but with entire column A formatted yellow, %, same 500 entries: 32kb

    So, in conclusion, your point is confirmed. It is just as efficient to format the entire column.

    Obviously, I did not run the test prior to giving advice. But I'm sure I remember learning years ago that formatting entire rows and columns was very inefficient. Perhaps the newer versions of Excel are better in that regard than the older versions? Or maybe I'm just losing my mind. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Size of Excel file (Excel 2003)

    <hr>Perhaps the newer versions of Excel are better in that regard than the older versions? Or maybe I'm just losing my mind<hr>

    You do realize that these are not mutually exclusive - it could be both of them <img src=/S/devil.gif border=0 alt=devil width=15 height=15>

    Steve

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Size of Excel file (Excel 2003)

    The second, I'm sure of. It's the first one I entered with trepidation. Come to think of it, I could have abbreviated my entire thought by saying that "I was probably sharper in my earlier edition!" <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    - Ricky

Posting Permissions

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