CODEWITHSUNDEEP
excel
Fox
Fox

Reputation: 33

Pivot tables: Filtering values using Greater than

I am trying to create a pivot table to show values greater than 126. From the pivot table fields list, I have tried using the label filter to show only values that are greater than 126. Though applied, the filter does not seem to work.

Please help recommend a way to achieve this?I have attached a picture to add clarity to how I run my filter. Please advise.

[Excel Spreadsheet replication of problem]1. Please open in excel for pivot table field lists to show.

Problem Showing values greater than 126

Upvotes: 0

Views: 12131

Answers (1)

Tim Nevins
Tim Nevins

Reputation: 341

I downloaded your sheet and there are two potential resolutions. They are both kind of kludgey, but they do what you need. Power Pivot may have functionality to filter values.

First, you can create a helper column that determines if the value meets your criteria. Have the pivot count this helper column, Then exclude the FALSE column from your table. This is probably the approach I'd take.

Or, you can drop the Count on Waiting List in the columns box. Right click on the Column Labels, click Group, in the Grouping dialog - Starting at = 126, By: = 2000. Click OK. Click the down arrow on the counted field in Columns, click Move to Report Filter. Click the down arrow on the filter and exclude <126. You should be left with one column which counts values above or equal to 126 by year.

Excel pivot tables filter on the values in rows and columns in a pivot table, not the value grid. The value grid is filtered by the selections for columns and rows.

It's kind of a kludge, but it does what you need and should accommodate new data assuming your pivot source range gets updated. Excel Tables functionality will adjust your pivot source for you automatically. Format your data as an Excel Table before creating your pivot.

Upvotes: 1

Related Questions