Results 1 to 12 of 12
Thread: Reducing file size (2008)

20090220, 21:29 #1
 Join Date
 Dec 2003
 Location
 Perth, Western Australia, Australia
 Posts
 473
 Thanks
 66
 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.

20090220, 21:37 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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).

20090220, 21:37 #3
 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>

20090220, 21:52 #4
 Join Date
 Dec 2003
 Location
 Perth, Western Australia, Australia
 Posts
 473
 Thanks
 66
 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

20090220, 22:10 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20090220, 23:46 #6
 Join Date
 Dec 2003
 Location
 Perth, Western Australia, Australia
 Posts
 473
 Thanks
 66
 Thanked 2 Times in 1 Post
Re: Reducing file size (2008)
Thanks Hans  Yes that make good sense.
I understand  cheers

20090611, 12:52 #7
 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 InsertNameDefine 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????

20090611, 13:25 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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 builtin 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.

20090611, 14:12 #9
 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 setup?

20090611, 15:39 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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!

20090611, 16:58 #11
 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 INSERTNAMEDEFINE and then retype the formula into the Source box?

20090611, 17:09 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
See Pieterse's star post Post 581649.