H
Harlan Grove
I don't use autofilters often, so I just came across this. Apologies
if it's been discussed before.
It looks like Excel applies autofilter criteria to the TEXT
representation of values within columns. I have a table with most
numeric values formatted as 0 (not General). I copied a cell outside
the autofiltered range as a value with number formatting (my habit
rather than copying just values) into a cell in the autofiltered
range. That cell had number format 0.0000 and value 1 (exactly). When
I tried applying an autofilter in the column, it showed both 1.0000
and 1 as possible values.
Can anyone suggest a GOOD reason Excel applies autofilters to TEXT
representations of numeric values rather than to the numeric values
themselves? Seems extremely counterintuitive to me, especially at that
means autofilter and advanced filters produce different results since
advanced filters work on cell values rather than what those cells
display.
if it's been discussed before.
It looks like Excel applies autofilter criteria to the TEXT
representation of values within columns. I have a table with most
numeric values formatted as 0 (not General). I copied a cell outside
the autofiltered range as a value with number formatting (my habit
rather than copying just values) into a cell in the autofiltered
range. That cell had number format 0.0000 and value 1 (exactly). When
I tried applying an autofilter in the column, it showed both 1.0000
and 1 as possible values.
Can anyone suggest a GOOD reason Excel applies autofilters to TEXT
representations of numeric values rather than to the numeric values
themselves? Seems extremely counterintuitive to me, especially at that
means autofilter and advanced filters produce different results since
advanced filters work on cell values rather than what those cells
display.