MB xls file size best treatment

Re: MB xls file size best treatment

I think the only way to open & close that file size is slowly

Why's it so big ?

Re: MB xls file size best treatment

What are the formulas ?

VLOOKUPS & array formulas can seriously bulk out a file....

Can you explain a bit about the file. 15mb isn't that big... I have plenty that size that I have no problems with - to avoid corruption, make sure you take plenty of backups.

If I knew more about what the file does, & the sort of functions you are using, I might be able to offer more specific advice.

With my files that use alot of VLOOKUPS / INDEX type or array formulas, I usually nowadays convert to database such as Access or SQL and use the additional power to do the lookups etc, just returning summary value-only recordsets to Excel.

EG, recently my boss built a 72mb file in Excel to forecast SWAP cashflows :yikes: It worked OK but very slow.

What we have now is an Access database with ~56,000 records (or more) at 3.6mb and a summary report in Excel that weighs in at 456kb.

The scary thing is that the boss wanted the 72mb file reproduced daily :roll: we would soon have used up our server space!!! but the Access db is just one file that keeps filling up.... probably won't get to 72mb for 6 years.....

Re: MB xls file size best treatment

A couple of other ideas about design:

1. Be sure that all your data sheets are listed alphabetically first. I usually have A0_Data0, A1_Data1, A2_Data2, etc. Then have all your other worksheets base on this data and if others use some of the secondary formula sheets, make sure they always look back, never ahead, i.e. B3_Formulas can look back to any data sheet (A's), as well as B0_Formulas, B1_Formulas, and B2_Formulas. But B3_Formulas should never point to D4_Secondary Formulas. The calculation sequence is critical, especially with larger workbooks.

2. Make sure the formulas point the right way (Top-down, left-right) is the best sequence. Don't leave gaps in the worksheet; it is better to have two tables on different worksheets than to have two tables with excess empty cells between them.

Re: MB xls file size best treatment

Try and not use to many sheets in the workbook as this can increase the size of the file.

Also check that the end of the used ranges within the worksheets does not exceed the area where you have data/formulae.

Only apply number formats to area that need it as this can also increase the size of the workbook.

HtH