Pivot Table Filter

L

Lisa

Excel 2003 shows a filter arrow on data rows, to select/deselect single items.
Can I apply custom settings as you can on Autofilter?
I want to show data only of product codes containing "FG" not for all
product codes.
I can't use the Page Field as I have many product codes that inclued "FG" as
part of the product code, and this only allows one at a time, I want to see
all those containing "FG"
 
P

Portuga

Hi Lisa,

you can indeed use the page field.

Drag the product codes to the Page Field. Leave "all" selected

1- right click on top of product code bar.
2 - Select "Field Settings": at the botton you have the option to Hid
items.
3- Just select the ones that DONT contain FG on it.
4- Eureka
 
B

Bean123r

You could add a helper column to your source data that will indicate if the
product code contains "fg".

If A1 contains product codes i.e.:
123FG
FG123
1FG23
123

Add the formula =if(isnumber(search("fg",a1)),"yes","no") to the cell beside
your product code and copy down.

Your table will look like this
123FG yes
FG123 yes
1FG23 yes
123 no

If you include both columns in your pivot, then you can filter for the "yeses"
 
J

john anderson

You can type a single character to the right of your last pivot table column
(outside of the table). Then datafilter on that character and you will be
able to use the data filter on the pivot table fields. It has limited use
but it's an option to try.
 
R

Roger Govier

Hi Lisa

I would be inclined to create an extra column in my source data. Don't
forget to then extend the source range in your PT)
If the FG is the first 2 characters of the code, then just enter
=LEFT(A1,2) substituting the reference where your code appears for A1.
Include this field in the row area of your PT, double click on the field
and set Subtotals to None.
Use this field to filter for your FG items.

If it is not as simple as the first 2 characters, then use the MID()
function.

Don't forget to then extend the source range in your PT.
 
Top