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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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

A
  • Question
Calculated Pivot Table Field
Replies
1
Views
494
RoryA
RoryA
S
  • Question
Pivot table - calculate percentages with filters
Replies
0
Views
60
ShadowRider
S
B
  • Question
Calculated Field in pivot table difficulty
Replies
2
Views
355
bluefish44
B
C
  • Question
Confused over pivot table calculations :-(
Replies
1
Views
292
number kruncher
N
N
  • Question
Pivot table - Difference between two '% of Column Total' Columns
Replies
1
Views
492
Kaper
Kaper

Forum statistics

Threads
1,215,467
Messages
6,124,984
Members
449,201
Latest member
Lunzwe73

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