F9, Shift+F9, Ctrl+Alt+F9 etc

[john1978]

Hi.

I'm a heavy Excel user at work. We often create quite large spreadsheet that
use complex worksheet functions provided by 3rd party addins. Typically we
may use functions to draw in data from external sources and then process
those data using a combination of standard Excel worksheet functions and
worksheet functions provided in the 3rd party addins. The spreadsheets
generally take ~10 mins to calculate so we have then on 'manual calculation'.

My question is about how best to arrange and calculate large spreadsheets.

People at work fall into two categories:

1.) Arrange the workbook so that its worksheets have a reasonably clear
dependency order. Then use Shift+F9 to calculate the worksheet one at a time
until you've calculated everything you need.

2.) Use Ctrl+Alt(+Shift)+F9 when you really have to start the whole
calculation from scratch. Otherwise just use F9 to calculate cells who's
dependents have changed. NEVER use Shift+F9. Leave it all to the Excel
dependency tree.

Please can someone suggest some reading material for me related to this.
I've looked in books and on the web but all I've found is answers to very
specific issues. I'm looking for general suggestions for best practice.

Cheers.

 

[Luke M]

For topics regarding calc speed and decision choice, I find decisionmodels to
be a fairly good source of info. They deal a lot with how to "speed up"
Excel, and have several other references on their site.

http://www.decisionmodels.com/calcsecretsc.htm

http://www.decisionmodels.com/calcsecretse.htm

 

[Teethless mama]

http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm#Speeding up Re-calculations

 

[john1978]

Thanks for the responses. I followed one of them and a few links later I
found this:

http://msdn.microsoft.com/en-au/lib...xcelPerf_UnderstandingCalculationMethodsExcel

Of particular interest to me was the "Third Golden Rule: Make Good Use of
Smart Recalculation" which I guess suggests use of Ctrl+Alt+F9 and F9. Thanks
again. I'm still very interested to hear other peoples opinions on this. Does
anyone want to defend use of "Shift+F9 on every sheet"?