Excel filesize and calculation speed (slow)

**pixifaery** · 08-31-2010, 09:55 AM

Hi Everyone

I have a spreadsheet which is shared and approx 13MB. I thought I would attempt to reduce the file size and the calculation speed so have been doing a bit of research.

I've cleared all the formatting from blank cells and reduced the number of rows. I've also been looking at my formulas, some of which tended to use the whole column of data rather than a specific range. I changed this by using dynamic named ranges.

However, although the file size is approx 1MB smaller it's now calculating more slowly. Is this due to using OFFSET (i.e. volatile) in the named ranges - would I be better off just extending the range to a safe margin?

Has anyone got any better suggestions?

Thanks
P

**Richard Buttrey** · 08-31-2010, 10:37 AM

Hi,

Are you using any array formulae. e.g. SUMPRODUCT() or any formula which contains a range reference and where you've entered it with Ctrl Shift Enter?

How many OFFSET formulae do you have. You're right in that it is a volatile function and will always recalculate and maybe slow things down depending on the numbers. You can ameliorate this by holding the COUNTA() bit of the formula in a single cell somewhere and then refer to this cell in the dynamic range name.

HTH

**pixifaery** · 08-31-2010, 10:46 AM

Thanks for your response,

There's no SUMPRODUCT, just SUMIF, the other formulas (INDEX / MATCH / LOOKUP) only get used when the macro is run and then deleted because the file size was stupid.

I do have a fair few OFFSET formula, but they have been there from the start (for sub totals where the range is not static - is there another approach to this?), it's slowed down since I've started using dynamic named ranges instead of 'A:A' for example.

Just amended the named ranges from dynamic to static, deleted the dynamic ones and I'm running a lot quicker now.

Does that sound right? Had no idea that would have such a huge impact!