AutoFilter a PivotTable

We haven’t had a PivotTable trick an a while.  So today’s post is for all you nerds who are jonesing for your PivotTable fix.

The conventional wisdom is that you can’t apply an Autofilter to a PivotTable.  Technically, that’s true.  But there is a way to trick Excel into making it happen.

.

The trick is to place your cursor directly adjacent to the last title in the PivotTable.  Once you have it there, you can go to the application menu and select Data–>AutoFilter

pvttblfilter1

.

.

As soon as you do that, you’ll have AutoFilters on your PivotTable!

pvttblfilter2

.

.

At this point, you can do something cool like apply a Custom AutoFilter to find all values less than $400.

pvttblfilter3

.

.

As the French say: Voilà. I think the direct translation in English is:  BooYah!

pvttblfilter4

.

.

I actually learned that trick at our Power Analyst Bootcamp in Chicago last year – which brings me to a crass commercial message.

As the sidebar will tell you, we now have less than a few weeks before our Power Analyst Bootcamp in Dallas.  Eveyone cool will be there.  You’ll learn a slew of new techniques that will push your skills to a new level of genius.  Techniques, by the way, I will never cover in this blog because they’re just that good.

It’s really a fun three days too.  Bill Jelen is mildly interesting, but my presentations Rock.  Plus, you can see how much weight I’ve gained since my last bootcamp.  See you all in Dallas!

16 thoughts on “AutoFilter a PivotTable

  1. Jeff Weir

    Hi Mike. Jon Peltier also covered this some time back at http://peltiertech.com/WordPress/autofilter-tricks/

    I wrote some comments on Jon’s post that I think are worthwhile repeating here, because there’s a cool additional trick involved. Here goes:

    It’s worth noting that if you have your selection filtered by this auto filter method, and then at the same time apply a filter on one of the actual pivot table row fields, you can sometimes get some nonsensical results because there’s some conflict between the autofiltered fields and the pivot table row fields. I’ll see if I can reproduce an example, and post a screenshot here to illustrate what I mean.

    One way around this is to use the pivottable as a datasource for another pivot table – One way around this is to use the pivottable as a datasource for another pivot table – then you can use the pivottable data fields from the original (master) table as pivottable row fields in the new (slave) pivot table, meaning you can not only filter them, but you can sort them as well (which you can’t do with the autofilter hack above).

    One bonus of this method is that you can effectively add ad-hoc fields beside your original (Master) pivottable using formulas, and have them show up fully intergrated in the new (Slave) pivottable. This allows you to far surpass what a calculated field will allow you to do, or introduce new data altogether (perhaps from a seperate data source). This can then be dragged and dropped into the new pivot table as row fields or data fields, just like the original data can. So you can happily filter away, as well as sort the Slave pivot table by the new fields.

    For instance, say you want your dates to coincide with a fiscal year that runs from 1 July. Pivot tables can only group by calander year, not by some arbitrary fiscal year, so the usual solution here is to get at the original data source to add a new ‘financial year’ field.

    But using my trick above, you could put a new column to the left of your original pivottable (rather than the right, where it would get overwritten if the Master Pivot Table has any more row or data fields added to it) and use the formula to return a financial year field, using something like:

    =IF(MONTH(B2)>6,YEAR(B2)+1,YEAR(B12)) …assuming this formula is in A2 and copied down, and the pivottable has dates in row B2, and you put ‘financial year’ as a header in A1

    Then when you selet the data source for the Slave pivot table, just be sure to include row A through to the last row that has pivot table data, and you’ve solved your problem – the slave table has this row included.

    Probably best to define a dynamic range for this in case the Master pivot table grows or shrinks.

    This might be a convenient way to bring new data into a pivotchart too…if pivot charts are your thing.

  2. jeff weir

    re “Bill Jelen is mildly interesting, but my presentations Rock.”…you’re not just saying that because you’re jealous that Jelen gets listed before Alexander on the spine of ‘Pivot Table Data Crunching’, are you?

    That whole Lennon/McArtney thing…

  3. sam

    Not Required in 2007/2010 as it comes with built in “Label filter” or “Value Field” (enabled only of you are not in compatibility mode)

  4. Jeff Weir

    Sam: are you talking about the fact that you can filter fields in the Row or Column area by value in Excel 2007?

    If so, it’s worth noting that Mike’s post above allows you to filter data fields – which is not built into Excel 2007 pivottables as far as I know.

    For instance, check out the screenshot of this ‘default functionality’ 2007 pivottable at http://screencast.com/t/5aPnGHsvKMo

    Notice that while there’s filters available on the Row field, there’s no filter on the ‘Sum of Cost’ field.

    If we use Mike’s trick, then now we have one, as you can see at http://screencast.com/t/PZeeMQsMK

    Now you can filter out specific totals

    Furthermore, my trick posted in the comments above allows you to effectively put these data fields in a subsequent pivot table as row or column fields…or to even add new data into a pivottable from an entirely different source. This means you can now sort the order of pivot fields based on things in the data summary area.

  5. Colin Banfield

    This is a neat pre-Excel 2007 trick. Not good if you need to display visual totals or subtotals in the PT, but useful if you create a normal chart from PT and you need this kind of filter. Can be problematic if you’re dragging fields in and out of the PT or if you’re applying PT filters in addition to the AutoFilter.

  6. Colin Banfield

    “For instance, say you want your dates to coincide with a fiscal year that runs from 1 July. Pivot tables can only group by calander year, not by some arbitrary fiscal year, so the usual solution here is to get at the original data source to add a new ‘financial year’ field.”

    Lack of a fiscal year grouping option in PivotTables is something I’ve never understood. My current preference is to create calculated fields in the data source for fiscal dates (year, month, quarter, etc.) . Not an issue for OLAP sources.

  7. Jeff Weir

    Sam: are you talking about the fact that you can filter fields in the Row or Column area by value in Excel 2007?

    If so, it’s worth noting that Mike’s post above allows you to filter data fields – which is not built into Excel 2007 pivottables as far as I know. I.e. while there’s filters available on Row and Column fields, there’s no filter on the ‘Sum of …..’ field.

    But with Mike’s tip, now you can filter out specific totals from this field.

    Furthermore, my tip posted in the comments above allows you to effectively put these data fields in a subsequent pivot table as row or column fields…or to even add new data into a pivottable from an entirely different source. This means you can now effectively sort the order of pivot fields based on things in the data summary area, or things outside of the original pivot table altogether.

    [Note: I originally posted this comment with some links to screenshots, but I think this went to a ‘moderation’ queue because of the links. Given Mike’s busy enough changing diapers at the moment, I’ve reposted without the links. Sorry if this ends up coming through twice as a consequence]

  8. Colin Banfield

    “If so, it’s worth noting that Mike’s post above allows you to filter data fields – which is not built into Excel 2007 pivottables as far as I know. I.e. while there’s filters available on Row and Column fields, there’s no filter on the ‘Sum of …..’ field.”

    In Excel 2007, if you use a value filter on the row field at the lowest level of the hierarchy, you get the same results as Mik’e data field filter.

  9. Jeff Weir

    Colin…I’m not sure you do get the same result for a data field under 2007 by filtering at the lowest level as you do with this trick. I understand that you can filter a field on a specific value, but that you can’t filter that same field based on aggregated totals.

    That is, say you wanted to filter your ‘sum of price’ field so that any revenue over $1000 from various items was excluded. I don’t think you can do this by filtering a row field. Yes, you can filter out things in say a ‘price’ row that might individually over $1000 . But that’s different from filtering out those aggregate totals (i.e. revenue) from your ‘sum of price’ field of any items that didn’t return more than $1000 in total.

    Could be that I’m misunderstanding your scenario.

  10. Colin Banfield

    Jeff, try something like the following:

    Create a column labeled “Dates” and fill down several dates in multiple months. Create a second column labeled “Revenue” and fill down with random numbers, say =ROUND(RAND()*1000000,0).

    Now create a pivot table from the data. Group the dates in the row field by month and year. Right-click in the Date field (which has the dates grouped in months) and select Filter–>Value Filters… Select a “Greater Than” value filter, based on the aggregated numbers you have in the data area. You will notice that the pivot table is filtered based on the aggregated numbers, and not on the underlying numbers that make up the totals.

  11. Jeff Weir

    Ahhhh. Of course. What an idiot I am…I use this functionalityall the time in the form of the ‘Top 10’ filter.

    Thanks Colin and Sam.

    It’s kinda weird that Microsoft would bury this functionality in a right click menu, rather than have this option available right off the filter menu at the top of each pivot column (i.e. the menu that comes up if you click on say the “Dates” filter icon). But then, it’s also weird that you can’t access ‘More sort options’ in the same way.

  12. Mike

    Many thanks for the autofilter trick. My question is, once you apply the auto filters, is there a way to get to the source data for only the values you’ve filtered on?

  13. Paul Saltis

    Cool techniques in Excel…..but how can you group by week in Access 2007? Used a query looking at sales figures to create expressions listing week number, month and also year. Used this data in a pivot table, all works well until you get a week that spans across months then the data is split. If you take the month out of the pivot table the weeks are run 1, 10, 11, 12 etc. If anyone knows how to group by week as in Excel please let me know.

  14. Pingback: Advanced Topics in Excel Part-1 – Excel2Python

Leave a Reply

Your email address will not be published. Required fields are marked *