Why does Excel recalculate when I make a different filter selectio

  • Thread starter Thread starter Eric_NY
  • Start date Start date
E

Eric_NY

I'm using Excel 2003. I have a spreadsheet with a lot of formulas.

When I make a new selection using an existing Auto-Filter, Excel
recalculates the sheet. I don't know why this shoudl happen. Are there any
formulas or functions which return different results depending on whether a
table is filtered?

Thanks.
 
Are there any formulas or functions which return
different results depending on whether a table is filtered?
Click to expand...

Yes, most commonly the SUBTOTAL function. It's specifically made for
calculations on filtered ranges.
 
Biff is correct that from Excel 2003 the SUBTOTAL function can be used with
hidden or filtered ranges.
But the recalculation behaviour you observe does not depend on SUBTOTAL and
in fact previous Excel versions do the same thing with Autofilter.
Excel 2003 is the first version to also recalculate when rows are hidden or
unhidden (presumably because of the change to SUBTOTAL).

Selecting any filtering criteria when using Autofilter will flag ALL the
formulae in the autofilter range as uncalculated, even if none of their
precedents have changed and even if you select exactly the same filter
criteria as before. This can cause Autofilter calculation to be extremely
slow in Automatic mode: better to use Manual calculation mode.
 
Back
Top