C
ChristopherW
I'm working with quite large datasets at the moment using Excel 2010 an
I have a big ol' PivotTable configured to display this informatio
summed by several nested criteria.
For the most part this works great and allows me to wrangle th
information I need into a form I can use. However, attempting to displa
only a subset of this data - by using a "less than or equal to" filte
(<=0) in the Filter box didn't work. It seems all the regular Filte
dialogs allow you to do is alphanumeric search, alphanumeric search wit
wildcards or exact matching.
Searching the docs reveals that there's a "Value Field" availabl
designed to achieve this exact functionality with numerous operators, i
you wish to display only a subset of value columns. However, given jus
how many values I need to filter this still results in a hugely unwield
PivotTable about a mile wide... and it seems like quite an inefficien
way to do it. You also have to disable the value filter before modifyin
or applying it again, and I hate unnecessary mouseclicks.
http://tinyurl.com/aw2hzzz
http://www.excelbanter.com/showthread.php?t=157595
http://tinyurl.com/a5gdqtg
I also understand that you can create a cell which references a valu
and contains an equation which you can apply as a new column to th
PivotTable, but you need to manually update the PivotTable every tim
you adjust the cell. You also have to go edit the cell manually ever
time which becomes onerous.
e.g. a hack in VB: http://archive.msdn.microsoft.com/CellFilterPivot
http://www.excelbanter.com/showthread.php?t=157595 (see second post b
Debra Dalgleish which includes the equatio
"=AND(A2>=StartDate,A2<=EndDate)")
However, there is light at the end of the tunnel! In Excel 2010 you ca
use Slices, which 99.5% accomplishes what I'm trying to do - you ca
enable slices on any field, select multiple slices with click & drag (o
shift-drag)... and hey presto: intended result! Neatly formatted
updating in realtime, super. However it's still bugging me that
couldn't accomplish this with a standard filter by just typing in <=
(or something more complex like ">-.5<0.25" (probably not exactl
correct syntax, I've hacked together more ugly code when doing thi
inside cells).
Anybody got a neat solution which allows you to apply an operator-styl
range of values in a normal PivotTable field Filter by inferring the
with a "less than" and "more than" value set? I'm half expecting Slice
to already be Microsoft's official solution to this otherwise missin
feature, but I'll still be happy to learn of any workable alternatives
I have a big ol' PivotTable configured to display this informatio
summed by several nested criteria.
For the most part this works great and allows me to wrangle th
information I need into a form I can use. However, attempting to displa
only a subset of this data - by using a "less than or equal to" filte
(<=0) in the Filter box didn't work. It seems all the regular Filte
dialogs allow you to do is alphanumeric search, alphanumeric search wit
wildcards or exact matching.
Searching the docs reveals that there's a "Value Field" availabl
designed to achieve this exact functionality with numerous operators, i
you wish to display only a subset of value columns. However, given jus
how many values I need to filter this still results in a hugely unwield
PivotTable about a mile wide... and it seems like quite an inefficien
way to do it. You also have to disable the value filter before modifyin
or applying it again, and I hate unnecessary mouseclicks.
http://tinyurl.com/aw2hzzz
http://www.excelbanter.com/showthread.php?t=157595
http://tinyurl.com/a5gdqtg
I also understand that you can create a cell which references a valu
and contains an equation which you can apply as a new column to th
PivotTable, but you need to manually update the PivotTable every tim
you adjust the cell. You also have to go edit the cell manually ever
time which becomes onerous.
e.g. a hack in VB: http://archive.msdn.microsoft.com/CellFilterPivot
http://www.excelbanter.com/showthread.php?t=157595 (see second post b
Debra Dalgleish which includes the equatio
"=AND(A2>=StartDate,A2<=EndDate)")
However, there is light at the end of the tunnel! In Excel 2010 you ca
use Slices, which 99.5% accomplishes what I'm trying to do - you ca
enable slices on any field, select multiple slices with click & drag (o
shift-drag)... and hey presto: intended result! Neatly formatted
updating in realtime, super. However it's still bugging me that
couldn't accomplish this with a standard filter by just typing in <=
(or something more complex like ">-.5<0.25" (probably not exactl
correct syntax, I've hacked together more ugly code when doing thi
inside cells).
Anybody got a neat solution which allows you to apply an operator-styl
range of values in a normal PivotTable field Filter by inferring the
with a "less than" and "more than" value set? I'm half expecting Slice
to already be Microsoft's official solution to this otherwise missin
feature, but I'll still be happy to learn of any workable alternatives