Excel 2003 - Subtotal forces global recalculations

[Guest]

We have a user that encounters a problem every time he uses subtotaling in
Excel 2003 (w/SP2). Even though he has Calculation set to “Manualâ€, every
time he attempts to subtotal on a worksheet, all other unrelated books
recalculate (and he has quite a few, that go many layers deep). and Excel
freezes up. MS KB324557 has been tried, but to no avail. The unrelated files
include circulated references, with iterations set at 50. There are also user
defined functions contained in an Excel add-in. This problem began after
upgrading from Excel 2000 to Excel 2003.

Any ideas of where to start?

 

[Guest]

What happens if calculation is set to automatic?

 

[Charles Williams]

Hi 3John,

There is a change in Excel 2003 relating to subtotals (because of the extra
subtotal options) which flags the workbook as uncalculated whenever rows are
hidden/unhidden.

Also a calculation in Excel is at application level (all open workbooks)
rather than just the active workbook.

But if application is set to manual I would not expect hiding/unhiding rows
to trigger a recalc: exactly what action is the user doing that triggers a
recalculation?

regards

 

[Guest]

Working with a model that includes 16 interlinked files, calculation is set
to manual to avoid the interruption of calculation time after each change.

 

[Guest]

The trigger is using the menu command Data | Subtotals, either inserting or
removing subtotals.

Thanks

 

[Charles Williams]

Hi 3John,

yes I can replicate that in XL 2003: I am surprised that Data -->Subtotals
triggers a recalc even in Manual calc mode, but it seems that it does.
Personally I would classify this behaviour as a bug, but Msoft may have a
different opinion.

regards
Charles Williams
Decision Models