Selecting Fields in Pivot Table

[Dan]

Hello everyone,

Is there any way to select fields within a pivot table that contain data, as
you can do with a filtered column within a spreadsheet? For example, only
show entries within a pivot table field that contain a particular word.

Thanks very much,

Dan

 

[Tom Hutchins]

One way...

You could add a field to your source data which checks the selected field
for the desired text. For example, this formula checks the current row in
column B for the word "Diet": =IF(ISERROR(MATCH("Diet",B6)),FALSE,TRUE)

If you then either recreate your pivot table or expand its range to include
the new column, you can add the new field to the table (perhaps as a page
field), then select to only display records for which that field is TRUE.

To view/change the range on which a pivot table is based, right-click on any
cell in the pivot table and select PivotTable Wizard. When the Wizard is
displayed. click the Back button. The range selection is displayed and may be
edited.

Debra Dalgleish's Contextures site has lots of great tips about pivot tables:
http://www.contextures.com/tiptech.html

Hope this helps,

Hutch

 

[smartgal]

I'm sure there's a better way than this but I make copy the pivot table and
paste it on another tab. Then I copy the 2nd one again and paste it as a
value (and I just paste it right on top of the one I just copied). Then you
can turn on the filters and do just about anything you want with it. A
couple of tricks, though. Anything you want to "bulk" format (like
highlighting all the totals, etc) should be done before you paste as a value.
It's certainly not impossible after, it just takes a little more work.