Filters vs Calculation?

[Ken]

Excel2003 ...

When Template has various "Filter" Switches activated ... It seems to take
sooooo much longer to "Calculate"???

Any reason for this ... or ... anything I can do to prevent???

Thanks ... Kha

 

[Dave Peterson]

xl2003 enhanced the =subtotal() function.

You can use
=subtotal(1,a2:a100)
This will look at the cells in A2:A100. It'll ignore the cells/rows that were
hidden by the autofilter.

=subtotal(101,a2:a100)
will look at the cells in A2:A100. But it'll ignore any cell/row that was
hidden by a filter or by hiding the row.

xl2003 always expects/assumes that you'll have some manually hidden rows and
that takes longer to recalc.

You can change calculation to manual, do the filtering, and then back to
automatic.

Tools|options|calculation tab
is where you'd toggle this setting.

 

[Ken]

Dave ...

I am already using =subtotal ... as you suggest ... So I am thinking issue
is size of my Template (just under 40,000 records) & "Conditional Formatting"
.... When File is not "Filtered" calculation works fine (does take a while,
but works fine) ... However, when I Filter ... the calculation takes much
much longer than when the File is not Filtered???

Hope this helps to clarify ... Thanks for supporting these boards ... Kha

 

[Dave Peterson]

I'm sure that the amount of data and conditional formatting impact the speed of
recalculation. You could remove the conditional formatting to see if that was
significant. But my bet is that you want to keep all the data.

I think the solutions are that you have to wait or change to manual calculation.