Connection between size and complexity

[emilija]

One general question, I have an excel file, now is about 13 MB, the whole
file is with formulas, nested formulas, using, IF, ISNA, ISDIV etc, ... than
SUMPRODUCT, mainly, SUMIF, VLOOKUP etc, so the file is looking from a side,
a bit complex,

So my question is does the formulas as SUMPRODUCT increases the file more
than let say the ordinary functions as SUM, or "+", or "*",... is it the
problem in too long formulas...

Also, the file is linked with few other files, does the number of links
increases the file,

And at the end how can I decrease the file, let say by deleting necessary
formats, .. anything, any idea will help me

Tx in advance

Emilija

 

[dq]

Emilija,

It doesn't matter what functions you use, it is the total length of
the 'text' that counts. What could help
- Tools/Options/Calculation uncheck 'save external link values'
- Select the empty rows on the bottom of your sheet and delete them,
same goes for the columns (they may appear empty, but still contain
formatting info and the likes)
- Applying a formatting to an entire row or column often takes less
space then applying it to 100's of cells (Excel needs to remember only
once)
- Copying your sheets to a new workbook might also help because all
the internal clutter isn't copied with the sheets.

DQ

 

[LT]

Emilija,

It doesn't matter what functions you use, it is the total length of
the 'text' that counts. What could help
- Tools/Options/Calculation uncheck 'save external link values'
- Select the empty rows on the bottom of your sheet and delete them,
same goes for the columns (they may appear empty, but still contain
formatting info and the likes)
- Applying a formatting to an entire row or column often takes less
space then applying it to 100's of cells (Excel needs to remember only
once)
- Copying your sheets to a new workbook might also help because all
the internal clutter isn't copied with the sheets.

DQ

I do believe DQ gave you some excellent advice. I myself had this
issue with VBA files just recently and to get over the 64k limit I had
to place alot of Call statements to external references.

Other then what DQ advised you, yes, eliminating graphics, solid
colors, borders etc should all help to reduce file size.

I do agree though, once a file seems to get to a certain size, it does
tend to behave "odd" at times. *Wonders if any Excel user in Excel
history has ever used more then 60,000 rows and still had a working
worksheet*

-LT

 

[dq]

I once got an Excel of around 150MB (and that's no typo). A full
calculation took about 3 hours...
That's when I started to learn Access

DQ