Optimise Worksheet Sequence

Optimise Worksheet Sequence finds an optimised calculation sequence by minimising the forward worksheet cross-references. These occur when a formula on one worksheet refers to another worksheet.

For example the formula below refers to CityCalc and LaneCalc worksheets, and refers to 210 cells on CityCalc, and 2730 cells on LaneCalc.

=SUM(CityCalc!B1:B210,LaneCalc!D1:D2730)

Excel97 and Excel2000 calculate Sheets in alphabetic Sheet Name sequence.

When a worksheet cross-references a sheet that is calculated later than the current sheet, it is a forward cross-reference and can cause additional calculation time.

Reduce workbook overhead using the Optimise Worksheet Sequence command.

Use the Optimise Worksheet Sequence command when your workbook profile shows a significant workbook overhead.The Optimise Worksheet Sequence command can be very slow to execute, and can take several minutes or more. Click the Esc key to stop if necessary.

The output is two tables of worksheet cross-reference counts, with forward cross-references highlighted in orange:
The first table shows forward cross-reference counts using the current worksheet calculation sequence.
The second table shows the optimised worksheet calculation sequence, and its effect on theforward cross-reference counts.

The Coverage row shows the results of the example formula. You can see, for example, that moving the Coverage sheet from before to after the LaneCalc sheet has eliminated the 2730 forward references caused by the example formula.

=SUM(CityCalc!B1:B210,LaneCalc!D1:D2730)

Using the optimised calculation sequence on the example workbook eliminates the 12 seconds of workbook overhead.

Rows show Refers-To counts.

Reading a row across the table shows you the Refers-To counts:
The Coverage row shows that the Coverage sheet Refers-To CityCalc 210 times and Refers-To LaneCalc 2730 times.

Columns show Referenced-By counts.

Reading a column down the table shows you the Referenced-By counts:
The Coverage column shows that the Coverage sheet is Referenced-By CityCalc 530 times, CityCtryContVols 3570 times, CtryCalc 106 times and Summary 110 times.

Orange cells show problem areas.

Orange highlighted cells show forward worksheet references. These are the most common cause of excessive workbook overhead.

Bright green cells on the diagonal are empty because FastExcel does not count references within a sheet.
Light green columns show worksheets that are not Referenced-By any other sheet. These are typically report sheets: for example the Summary sheet.
Light green rows show worksheets that do not Refers-To any other sheet. These are typically sheets containing the input data.

Changing worksheet names changes the worksheet calculation sequence in Excel97 and Excel 2000.

It’s very easy in Excel 97 and 2000 to try the optimised sheet calculation sequence: just double-click each worksheet name tab in turn and change the name.
Then do a Full Recalculation to see the difference in timing.

NOTE: In Excel97 and Excel2000 Excel’s worksheet calculation sequence is NOT the sequence of the sheets in the workbook: it’s the alphabetically sorted sheet name sequence. In Excel 2002 the worksheet calculation sequence is automatically determined.

If FastExcel can’t find a worksheet calculation sequence with zero forward cross-references it’s probably because the workbook contains circular cross-reference paths.

Try to avoid Circular Worksheet Cross-reference paths.

The Optimise Worksheet Sequence command shows you a table of the shorter circular worksheet cross-reference paths.

For example:
CityCalc has formulas referring to CityCtryContVols, which in turn has formulas referring to different cells on CityCalc.
Note that these are not (usually) what Excel considers as circular ceferences.

Circular cross-reference paths can consume significant calculation time, which shows up in the workbook overhead.

The Path Length column shows the number of worksheets involved in the circular path.
In complex workbooks there may be thousands of unique circular paths.
FastExcel is designed show you the shorter paths first, and will not attempt to find paths containing more than 6 sheets.
The example workbook has a number of circular paths, but there is no workbook overhead after changing the worksheet calculation sequence, so it’s probably not worth trying to merge any sheets.

If you still have a significant workbook overhead after changing the worksheet calculation sequence to the optimised sequence, then you probably need to remove some of the circular worksheet cross-reference paths.
Look for sheets with large numbers of forward cross-references that appear often in the circular paths.

Don’t remove circular paths unless you still have a workbook overhead.

You can remove circular paths by a combination of:

• Moving the formulas that do the forward cross-referencing to a worksheet that is calculated later.
• Merging the sheet that does the forward cross-referencing with the sheet that is referenced.