I'm working with quite large datasets at the moment using Excel 2010 and I have a big ol' PivotTable configured to display this information summed by several nested criteria.

For the most part this works great and allows me to wrangle the information I need into a form I can use. However, attempting to display only a subset of this data - by using a "less than or equal to" filter (<=0) in the Filter box didn't work. It seems all the regular Filter dialogs allow you to do is alphanumeric search, alphanumeric search with wildcards or exact matching.

Searching the docs reveals that there's a "Value Field" available designed to achieve this exact functionality with numerous operators, if you wish to display only a subset of value columns. However, given just how many values I need to filter this still results in a hugely unwieldy PivotTable about a mile wide... and it seems like quite an inefficient way to do it. You also have to disable the value filter before modifying or applying it again, and I hate unnecessary mouseclicks.

http://www.mrexcel.com/forum/excel-q...vot-table.html
http://www.excelbanter.com/showthread.php?t=157595
http://office.microsoft.com/en-gb/ex...010167795.aspx

I also understand that you can create a cell which references a value and contains an equation which you can apply as a new column to the PivotTable, but you need to manually update the PivotTable every time you adjust the cell. You also have to go edit the cell manually every 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 by Debra Dalgleish which includes the equation "=AND(A2=StartDate,A2<=EndDate)")


However, there is light at the end of the tunnel! In Excel 2010 you can use Slices, which 99.5% accomplishes what I'm trying to do - you can enable slices on any field, select multiple slices with click & drag (or shift-drag)... and hey presto: intended result! Neatly formatted, updating in realtime, super. However it's still bugging me that I couldn't accomplish this with a standard filter by just typing in <=0 (or something more complex like "-.5<0.25" (probably not exactly correct syntax, I've hacked together more ugly code when doing this inside cells).

Anybody got a neat solution which allows you to apply an operator-style range of values in a normal PivotTable field Filter by inferring them with a "less than" and "more than" value set? I'm half expecting Slices to already be Microsoft's official solution to this otherwise missing feature, but I'll still be happy to learn of any workable alternatives.