PivotTable question

[Kent McPherson]

I have a table that has these 3 fields I'm trying to use in a pivot table.
1. Industry name - e.g. retail, automotive, healthcare, etc.
2. Project name
3. Total Contract Value (TCV)

I drop Industry name in the Drop Row Fields Here. I drop Project Name in the
Drop Data Items Here. And I drop Total Contract Value in the Drop Column
Fields Here. Great, that works fine. But I want to filter the the table
such that I only get projects with a TCV of less than 50 in the table. I
haven't found a way to do this in the pivot table yet but I'm sure there
must be a way. Any help would be greatly appreciated.

 

[Bob Phillips]

Simplest way is to add a helper column and a formula to check if amount <
50, and add that to the pivot.

 

[Shig]

I have a table that has these 3 fields I'm trying to use in a pivot table..
1. Industry name - e.g. retail, automotive, healthcare, etc.
2. Project name
3. Total Contract Value (TCV)

I drop Industry name in the Drop Row Fields Here. I drop Project Name in the
Drop Data Items Here.  And I drop Total Contract Value in the Drop Column
Fields Here.  Great, that works fine.  But I want to filter the the table
such that I only get projects with a TCV of less than 50 in the table.  I
haven't found a way to do this in the pivot table yet but I'm sure there
must be a way.  Any help would be greatly appreciated.

I haven't tried it yet, but there is a filter that you can be applied
to fields. Also, if you could post the version of Excel that you're
using, that will help people answer your question better.

 

[Kent McPherson]

I'm using Excel 2003. Do you mean add a column on the data worksheet
itself to check if the TCV is less than some number? Or is there a helper
column feature in the pivottable?

 

[Bob Phillips]

No, in the data worksheet.

 

[Roger Govier]

Hi Kent

One way, using the dropdown arrow on TCV, deselect those values greater than
50
Alternatively, click on a value in the TGV row>Group and Show
Detail>Group>Ending at 50>Group by 1

All values above 50 will be in a single column, which can be deselected as
outlined above if required.