Filter pivot table results / calculated field

C

coolhandphil

New Member
Joined
Sep 12, 2007
Messages
13
Hi,

I have a pivot table which show plus and minus values in one of the fields. How can I filter the table to only show negative values?

My pivot table appears as follows:

................................................Sales Area
Manager.......Data........................South
Al................Operating Profit.........-100
Al................Count of Stores.........10
Rick.............Operating Profit.........250
Rick.............Count of Stores.........11
Sally............Operating Profit.........-150
Sally............Count of Stores.........5
Bob..............Operating Profit.........60
Bob..............Count of Stores.........8

I want to only show Managers with a negative Operating Profit.

My first thought was to create a Calculated Field in the pivot table, however the field can't be placed anywhere other than in the data section so I can't filter by it.

Any ideas?

Thanks,

Phil
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
You could try the top 10 feature - show bottom x where x is the number of negatives. Or you could add a field to the source data with a formula that returns TRUE/FALSE depending on the sign of operating profit, add it to the pivot table and filter on that.
 
Upvote 0
Hi, Phil.

FYI, another approach is to filter out the unwanted records between the source data and the pivot table: using 'external data' either at the first step of the pivot table wizard or via menu data, import external data, new databse query. (This can be totally within the one file and need not be a separate file despite the 'external data' name.) When available take the option to go into MS Query and apply a criteria to the profit field for values < 0. So the SQL would be like below. Complete the PT.

HTH, Fazza

Code: 
SELECT *
FROM source
WHERE Profit < 0
 
Upvote 0
here's a small trick - you can't get a filter applied to a 'raw' pivot, but you can if you add a column of data to the column next to the pivot.

in the column immediately to the right of your pivot, add a column header (eg 'Filter') and some data or a formula - in this case something like sales<0 or similar

now, go & add a data | filter | autofilter to the new column - the filter options will also now turn up in the pivot & you can do what you want.
 
Upvote 0
so I do thiis column but is not included in the pivot range selected in advance?
 
Upvote 0
BTW, filtering CAN be done between the source data and final pivot table (without adding an extra field to the source data). See post #3 above.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Macro to Filter Pivot Table
Replies
2
Views
775
howard
H
S
  • Question Question
Filter in Pivot Table Field List Clearing when The Pivot Table Is Refreshed
Replies
1
Views
612
arun_eagertolearn
A
J
  • Question Question
Excel Pivot-Table calculated fields with if statement return wrong results
Replies
3
Views
309
RoryA
RoryA
T
  • Question Question
Clear FIlters of Pivot Table after Cell Value Changes
Replies
4
Views
443
Alex Blakenburg
A
M
  • Question Question
Pivot Table Calculated Field Average and Std Deviation (control chart)
Replies
4
Views
225
Sergius
S

Forum statistics

Threads
1,223,920
Messages
6,175,378
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top