Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Reducing file size (2008)

    I have a spreadsheet that has become very large. The spreadsheet has mainly vlookup's and conditional formatting and the usual column heading and cell lists. There is not a great deal of text. Each copied tab add about 3mb to the file size. So 7 tabs the spreadsheet I have has gone to around 21mg.

    Any suggestions on how (or if) I can reduce the file size.

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

    Re: Reducing file size (2008)

    Press Ctrl+End in a worksheet to check whether what Excel thinks is the "used range" is larger than the actually used range. If so, you might delete superfluous rows and/or columns.

    If you have many identical formulas (except for the cell addresses of course, take a look at Use named formulas to reduce workbook size (all).

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reducing file size (2008)

    There are various options you can try, see <!post=Exploding' file size,689,429>Exploding' file size<!/post>

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Reducing file size (2008)

    Hans/Tony,

    Thanks for the quick replies.

    Will try the naming options - why do the formulas add so muck to the file size when the naming options don't

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

    Re: Reducing file size (2008)

    Let's say you have 1,000 virtually identical formulas of 200 characters each. That's a total of 200,000 characters.
    Now define a name of 9 characters long that stores the formula. The name and formula take up about 210 character, and now you can replace the 1,000 long formulas with a much shorter one referring to the name, for a total of 1,000 * (9+1) = 10,000 characters. Together that makes 10,210 characters - just over 1/20 of the original space.
    In reality, it's more complicated than this description, but you get the idea.

  6. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Reducing file size (2008)

    Thanks Hans - Yes that make good sense.

    I understand - cheers

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Hans....can you elaborate on the use of named ranges, as you mentioned in your last post that 'it was complicated'..........for example, 24 columns x 275 rows = 6,600 formula on 1 sheet (the formulae are the same, altho there is a change as you move throughout the range to conform with the cell address)......each formula is probably 200 characters......if I use Insert-Name-Define and create "Avail", how can I incorporate the formula into 'Avail' and then simply have each of the 6,600 cells whihc currently contain the whole formula point to 'Avail' and run the formula from there?...if I could do this throughout the 7 sheet workbook, I imagine it would reduce the file size considerably????

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    By "In reality, it's more complicated than this description", I meant that formulas do not take up exactly the space indicated by the number of characters that you see in the formula bar. Excel stores references to built-in functions in a tokenized format, so that users with a different language version of Excel will see the functions in their own language. Also, the result of the formula is stored along with the formula itself.

    If you set up the name "Avail" correctly, you should be able to use the simple formula =Avail in each of the cells.

    You'll have to set up a named formula for each of the sheets.

  9. #9
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    I was trying to do a sample so that I could understand how it works, but I can't make up a sample that works.....do you have a small sample file that I could open and look at in order to educate myself on the set-up?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I have attached a very simple example. Look at the cells highlighted in yellow, and look at Insert | Name | Define...

    Note: the formula that you will see in the Define Name dialog depends on the row of the active cell. This is by intention!
    Attached Files Attached Files

  11. #11
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you....I will look at it...ps: seeing as how I already have my workbook done, and some of these formulas are quite long, is there a quick way to convert a formula to a 'name' (and then I can fill in to the other cells) or will I have to do a INSERT-NAME-DEFINE and then retype the formula into the Source box?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See Pieterse's star post Post 581649.

Posting Permissions

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