Formula impact on file size, memory etc.

[rdw72777]

I just had a thought as I'm trying to optimize an Excel file with hundreds of thousands of formulas that I need to trim down the file size on.

It was always my understanding that array formulas were the most file size/memory impacting, then Sumif/Countif type formulas, then standard formulas like Vlookups/Count and then simple formulas like "=A1+A2"

Does anyone know if my understanding is correct? I just want to have the most knowledge as I try to go through this. I'm thinking I could shrink the range on my SUMIF's (instead of using the whole column as a range, maybe just use the first 100,000 rows since the range should never be that big) but want to optimize this thing the best I can.

 

[GlennUK]

Shrink the range on your SUMIFs, and your VLOOKUPs and your MATCHes, and anything else that references ranges. 100,000 rows is still massive ... consider using named ranges or data tables so as not to reference any blank areas unnecessarily. Named ranges can be dynamic using a formula to calculate the referenced area, or even maintained by VBA worksheet events redefinining the named area when data changes.

 

[JazzSP8]

I just had a thought as I'm trying to optimize an Excel file with hundreds of thousands of formulas that I need to trim down the file size on.

Just to add to Glenn's comments;

It's not just formulas that can impact on the size of an Excel file, look at the formatting of the cells as well and make sure that only the cells that need to be formatted are.

Some people quite happily format the whole of a column or a row (or worse, both) with say a font or a colour when all they really need to do is the first three columns.