Worksheet Links

Minimise worksheet links

Referencing cells on another worksheet is nearly always slower than referencing cells on the same worksheet. This overhead is larger when referencing formulae than when referencing data.

Make sure you have a logical flow of calculations across your worksheets.

Forward worksheet cross-references and circular worksheet cross-references (Sheet1 references Sheet2 references Sheet1) can be extremely slow. You really need to eliminate them:

• Rename your worksheets to optimise the Excel’s worksheet calculation sequence.
• Move formulae between sheets.
• Merge entire sheets.

The FastExcel Map Crossrefs command will find forward worksheet cross-references, and determine a near-optimum worksheet calculation sequence that minimises the number of forward cross-references. It will also list any circular worksheet cross-references it finds.

Minimise workbook links, and make sure you have a logical flow of calculations across your workbooks.

Because Excel 97 and 2000 calculate workbooks in the sequence of their alphabetic names, make sure that the names of linked workbooks that you will have open at the same time sort into a sequence that gives an orderly flow of information through the links:

Avoid forward links:

For example a workbook named Answers linked to a workbook named Calculations (Answers cannot be fully calculated before Calculations).

Avoid circular links between workbooks:

For example Book1 links to Book2, Book2 links to Book3, and Book3 links to Book1.