Excel: Pivot Table Does Not Filter

Question

I have a spreadsheet that has similar data to (except I have 256K rows):

Company $
A       10
A       20
A       30
B       20
B       30
C       40
C       60
D       10
D       0
D       10

I created a Pivot Table where the Rows are grouped by Company and the Values show the sum of $.

I am trying to have it filter for values where the sum of $ is >= 50. So I go to Filter for $ and do greater than or equal to 50

When I do this, nothing is changed. I know I can always manually select every value where the value is >= 50 but that would take a long time because my data has too many rows.

I tried adding a column in my data to return the sum of $ by comparing Company name (=SUMIF(A:A,"="&A2,B:B))but that would take a very long time for 256K rows.

What am I doing wrong with my filtering? Why isn't it actually filtering based on value?

Answer 1

Thanks for a good question.

The short answer that you need to apply the filter to a non-Value field, Company in this case. If you filter on the Value field itself nothing happens (something I didn't even know you could do until you showed me.)

The only other thing I'd note on this topic is that it matters which non-Value field you pick. For instance if you'd had a Region field and applied the filter to that it also might have no effect if all the regions subtotal to more than 50. This seems obvious, but since that whole thing is rather unintuitive it's probably worth pointing out.

For a really good explanation of Value Filters, see this SuperUser answer.