Speeding up Excel 2007

[ajay_gajree]

Hi,

I have a large spreadsheet, which was initially of size 55KB plus, I have converted it to an xlsb format which has drastically reduced its size to under 3kb.

Regardless of this the spreadsheet is extremely unresponsive, probably due to the large number of calculations in it,

Can anyone give some tips on improving speed?

I have many vlookups for example, and they tend to have $A:$A references for example in them, would reducing the range make a difference?

Any general tips would be helpful!

 

[jeffreybrown]

Here are a couple of places to look...

Advanced Microsoft Excel Consultancy and Solution Development -Decision Models

Excel 2010 Performance: Improving Calculation Performance

Also for the ranges, take a look at dynamic named ranges...

Dynamic Excel Named List Grows Automatically « Contextures Blog

 

[ajay_gajree]

Thank you Jeff,

I am looking at FastExcel Sample files to see if that might help, I am hopeful!

 

[jeffreybrown]

Good luck, hope you find what you need

 

[ltpr1]

A couple of things i have noticed over the years.
The more live formulas the slower the sheet. Formulas looking in other workbooks/spreadsheets are alot worse.

one trick i have when i am finished compiling reports is to copy and paste all of the formulas and then copy the contents into a new open .xls file and then save as the file to an xlsx format. I have had 4.5MB file end up 32kb using this trick.

 

[ajay_gajree]

Thanks for the advice all,

I purchased FastExcel, its brilliant!