Excel help: sorting and filtering a pivot table

Factual Questions
1

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?

2

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.

3

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.)

4

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.

5

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.

6

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.

7

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.

8

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.