File growing by megs at a time?

Topic

New Reply

I’m working on an Excel 2000 (SR1a) spreadsheet. The file is about 198K. I define a short formula, fill the formula down about 700 rows, save the file, and the file becomes over 3meg! Subsequent formula additions continue to cause the file size to balloon. Of course I expect the file to grow, but it should not be anywhere near that much… Is there a known bug with formula autofill or something like that? I couldn’t find anything in the KB, but If there is such a bug, is there a known workaround so that I can fill the formulas I need without having my file balloon to over 50meg?

I’ve tried several different formulas added to the 198K file and they all seem to cause problems. One example is

=IF(($B2=0)*($A20), IF(ISNUMBER($I1), $I1 + 1, 1), “”)

which as you can see is not very long at all… I haven’t tried placing each formula individually or doing fills of less than 700 rows. Would either of these be helpful?

Thanks

Andy

Reply | Quote

Replies

I have never seen this before – however have you tried just selecting the data and copying and pasting it into a new workbook, then adding your formula again. Alternatively have you tried ctrl+end to see if excel thinks there is something else in later cells?

Is this data linked to any pivot tables in the same workbook? If each pivot table is created separately then this can cause balooning.

Finally, I really hope not, have you checked for viruses and also that their are no macros attached which you don’t know about?

Reply | Quote

Try copying the formula, then using Paste Special/Formulas to put the formula into your other cells. This will tell you if Fill is misbehaving.

Do you have any fancy formatting in the cell? Filling pastes formats and these can take up lots of space.

Reply | Quote

Thank you both for your help… I had to leave the spreadsheet for a bit while other things called. Now I’ve redone the spreadsheet using dynamic named ranges and array formulas, and the problem seems to have gone away (in fact, the file is a tenth the size of the original before I ever started modifying it, with a lot more functionality.

Thanks again — sorry it took so long for me to get back to thank you.

Andy

Reply | Quote