# Excel help: sorting and filtering a pivot table **URL:** \ **Category:** Factual Questions\ **Created:** [April 29, 2014, 9:06pm UTC](https://boards.straightdope.com/t/excel-help-sorting-and-filtering-a-pivot-table/687313 "2014-04-29T21:06:59Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [April 29, 2014, 9:06pm UTC](https://boards.straightdope.com/t/excel-help-sorting-and-filtering-a-pivot-table/687313/1 "2014-04-29T21:06:59Z")
I swear, it’s faster and easier to ask you guys than to search through online help. Excel 2010 I have a very simple table: 2 columns of descriptors (columns A and B) and 2 columns of numeric info (columns C and D). I want to build a pivot table off this table, that shows me the rows for the top 10 values of column C, but only where column D is \> 100. I can easily do the Top 10 thing, but can’t add the additional requirement of a filter on column D. I can filter on column A or B, but that’s not what I want. I can filter on specific values of column D (like, precisely 148) but that’s pretty useless. Any ideas? ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [April 29, 2014, 10:10pm UTC](https://boards.straightdope.com/t/excel-help-sorting-and-filtering-a-pivot-table/687313/2 "2014-04-29T22:10:22Z")
This actually looks pretty difficult, from what I can see. Can you not just filter the source data in place, copy to another sheet and base the pivot table off that? That might just be the simplest way to do this. The one workaround I’ve found is to insert col D both as a summed total, and as a row header. Then filter on the row header, which you already know how to do, and collapse the field so D is invisible. Clumsy, but it works. Right-clicking the table and using “Expand / Collapse Entire Field” is your friend here. I can email you a spreadsheet where I’ve done something along these lines, if that would be helpful. PM me your email address. ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [April 29, 2014, 10:32pm UTC](https://boards.straightdope.com/t/excel-help-sorting-and-filtering-a-pivot-table/687313/3 "2014-04-29T22:32:17Z")
The only problem is that I have two pivot tables off the same source, and the other one doesn’t care about filtering column D. I’d prefer to only pull the source data once, and be able to manipulate it two different ways. But if my worst case work-around is to pull it twice (once filtered on column D, and one unfiltered)…I can deal with that. (And gee, I really thought this was a simple thing that I just couldn’t figure out.) ---
**Author:** ![Blunt](https://avatars.discourse-cdn.com/v4/letter/b/ccd318/32.png) [@Blunt](https://boards.straightdope.com/u/Blunt)\ **Post date:** [April 30, 2014, 1:12am UTC](https://boards.straightdope.com/t/excel-help-sorting-and-filtering-a-pivot-table/687313/4 "2014-04-30T01:12:28Z")
This works in Excel 2013, I can only assume it will also apply to 2010. Go to PivotTable Options (you can get there by right clicking the pivot table), then Totals & Filters tab, and check the box for “Allow multiple filters per field”. You should then be able to set a top 10 filter for C and then a greater than filter for D. ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [April 30, 2014, 2:52pm UTC](https://boards.straightdope.com/t/excel-help-sorting-and-filtering-a-pivot-table/687313/5 "2014-04-30T14:52:49Z")
> [@Blunt](#): > > This works in Excel 2013, I can only assume it will also apply to 2010. > > Go to PivotTable Options (you can get there by right clicking the pivot table), then Totals & Filters tab, and check the box for “Allow multiple filters per field”. > > You should then be able to set a top 10 filter for C and then a greater than filter for D. Odd…those options are there, but the “Allow multiple filters per field” is greyed out. But checked. And (not coincidentally, I’m sure) on the ribbon bar, Pivot Table Tools, the “Filter” icon is also greyed out. ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [April 30, 2014, 3:39pm UTC](https://boards.straightdope.com/t/excel-help-sorting-and-filtering-a-pivot-table/687313/6 "2014-04-30T15:39:26Z")
> [@Blunt](#): > > This works in Excel 2013, I can only assume it will also apply to 2010. > > Go to PivotTable Options (you can get there by right clicking the pivot table), then Totals & Filters tab, and check the box for “Allow multiple filters per field”. > > You should then be able to set a top 10 filter for C and then a greater than filter for D. I don’t think you can set range filters for anything in the filter area of a PT, regardless of what type of field it is; you can only do that for fields in the row and column areas. Even with this box checked, I can’t get a range filter for D unless I add it into the row or col areas. I might be missing something here, but everything I’ve read suggests what you’re describing isn’t possible. ---
**Author:** ![ashtayk](https://avatars.discourse-cdn.com/v4/letter/a/e68b1a/32.png) [@ashtayk](https://boards.straightdope.com/u/ashtayk)\ **Post date:** [April 30, 2014, 7:47pm UTC](https://boards.straightdope.com/t/excel-help-sorting-and-filtering-a-pivot-table/687313/7 "2014-04-30T19:47:00Z")
Perhaps add another column E that picks up values from Col C only when Col D \>100…an IF statement =IF(D1\>100, C1, “”). Now put Col E in the page filter and filter for non-blanks. ---
**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\ **Post date:** [May 1, 2014, 11:16am UTC](https://boards.straightdope.com/t/excel-help-sorting-and-filtering-a-pivot-table/687313/8 "2014-05-01T11:16:24Z")
Move your row labels up to be Report Filters. Move column D over to make it the row label. Sort by it. Right click and group them as “0 - 100” and “Over 100”. This will create a group with two values that you can use as a filter.