Why does Excel recalculate when I make a different filter selectio
 

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.

Why does Excel recalculate when I make a different filter selectio
 

Are there any formulas or functions which return
different results depending on whether a table is filtered?

Yes, most commonly the SUBTOTAL function. It's specifically made for
calculations on filtered ranges.

--
Biff
Microsoft Excel MVP


"Eric_NY" wrote in message
...
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.

Why does Excel recalculate when I make a different filter selectio
 

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.


--
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"T. Valko" wrote in message
...
Are there any formulas or functions which return
different results depending on whether a table is filtered?

Yes, most commonly the SUBTOTAL function. It's specifically made for
calculations on filtered ranges.

--
Biff
Microsoft Excel MVP


"Eric_NY" wrote in message
...
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.