Excel performance - prevent filtering triggering recalculations

Question

Use case:

I have a large Excel file, with millions of formulae cells, all of it essential for its built-in logic. To make it work, all Excel performance aspects have been well researched and applied. And, the macro enabled Excel file (74MB xlsb) does work in doing its job. ... till... i try to filter something on the sheet.

General Excel performance issue:

Excel filter apply, row un/hide, cell resizing, etc. trigger volatile actions. Column un/hide no longer triggers recalculation.

For excel files of industrial applications, changing the view has no logical effect on values, except: subtotal.

Request:

We'd like a way to tell excel to stop causing-volatile-action/dirtying-all-cells/recalculation on view changes. This will make filtering really 'interactive'.

Suggestion:

option 1. Make subtotal as a volatile function. This will be a negligible performance hit compared to making view changes volatile action. For trade-off purposes, filtering is a very common usage (esp on large sheets) and needs to be interactive, compared to row hide, or subtotal. Subtotals even when used are limited and donot trigger downchain dependency.

option 2. Create 3 formulae types: non-volatile, view-dependent, fully volatile data, and 3 action types: input data change (recalc all 3), view change (recalc last 2), any event (recalc only fully volatile data). Could be more complex to implement, and may not be far better than option 1.

For any further details, contact Atif.Hussain on gmail

Answer 1

Completely agree, both with the problem and the proposed solutions.  It is incomprehensible to me why MS preferred to ruin the entire calculation tree (unnecessarily, I might add) over making a very rarely used function like SUBTOTAL volatile (as a kludge).  As Atif writes, the perfect solution would of course be to mark SUBTOTAL as dirty only when the filter changes.