How to show data greater than 10 in pivot table

[Angus]

I want to show the data greater than a specific number, say 10, in a pivot
table, like what can be done by autofilter in raw data.

I know I can use "autofilter" at pivot table, but it doesn't apply to the
new pivot tables created by "show pages". How to keep showing data greater
than 10 at the new pivot tables created by "show pages".

[Debra Dalgleish]

A Pivot table has a Top 10 feature that you can use to show only the top
values.

Or, you could add a column to the source data, and use the SUMIF or
SUMPRODUCT function to calculate if the total for an item is over your
set limit. Then, add that field to the page area, and use it as a filter.

Angus wrote:
> I want to show the data greater than a specific number, say 10, in a pivot
> table, like what can be done by autofilter in raw data.
>
> I know I can use "autofilter" at pivot table, but it doesn't apply to the
> new pivot tables created by "show pages". How to keep showing data greater
> than 10 at the new pivot tables created by "show pages".


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[GILESCIS]

Debra,
I have a similar request.
I have a colum that has gross sales in a pivot table along with other data.

I want to do a query (or custome lookup) if the gross sales is greater
then 50,000.00 and less then 100,000
then i only want those records to appear in the pivot table.
Is this possible and how,

Thanks
Dean Castaldo
Giles Chemical Corp



"Debra Dalgleish" wrote:

> A Pivot table has a Top 10 feature that you can use to show only the top
> values.
>
> Or, you could add a column to the source data, and use the SUMIF or
> SUMPRODUCT function to calculate if the total for an item is over your
> set limit. Then, add that field to the page area, and use it as a filter.
>
> Angus wrote:
> > I want to show the data greater than a specific number, say 10, in a pivot
> > table, like what can be done by autofilter in raw data.
> >
> > I know I can use "autofilter" at pivot table, but it doesn't apply to the
> > new pivot tables created by "show pages". How to keep showing data greater
> > than 10 at the new pivot tables created by "show pages".
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[Debra Dalgleish]

You could add a column to your source table, and use a SUMIF formula to
calculate the total for each customer. For example, with customer names
in column C, sales in column F, and your gross sales target in cell K2:

=IF(SUMIF($C$2:$C$500,C2,$F$2:$F$500)>$K$2,"Top","Bottom")

Add this field to the page area of the pivot table, and select Top from
its dropdown list.

GILESCIS wrote:
> Debra,
> I have a similar request.
> I have a colum that has gross sales in a pivot table along with other data.
>
> I want to do a query (or custome lookup) if the gross sales is greater
> then 50,000.00 and less then 100,000
> then i only want those records to appear in the pivot table.
> Is this possible and how,
>
> Thanks
> Dean Castaldo
> Giles Chemical Corp
>
>
>
> "Debra Dalgleish" wrote:
>
>
>>A Pivot table has a Top 10 feature that you can use to show only the top
>>values.
>>
>>Or, you could add a column to the source data, and use the SUMIF or
>>SUMPRODUCT function to calculate if the total for an item is over your
>>set limit. Then, add that field to the page area, and use it as a filter.
>>
>>Angus wrote:
>>
>>>I want to show the data greater than a specific number, say 10, in a pivot
>>>table, like what can be done by autofilter in raw data.
>>>
>>>I know I can use "autofilter" at pivot table, but it doesn't apply to the
>>>new pivot tables created by "show pages". How to keep showing data greater
>>>than 10 at the new pivot tables created by "show pages".
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Gilescis]

Well this is sort of what I wanted but i never know what my query range is
going to be, today it could be >50,000 & < 100,000 but next week i may
want to search for sales > 65000 but < 85000

so my query could change at anytime

thanks
Dean Castaldo
Giles Chemical Corp
[email protected]



"Debra Dalgleish" wrote:

> You could add a column to your source table, and use a SUMIF formula to
> calculate the total for each customer. For example, with customer names
> in column C, sales in column F, and your gross sales target in cell K2:
>
> =IF(SUMIF($C$2:$C$500,C2,$F$2:$F$500)>$K$2,"Top","Bottom")
>
> Add this field to the page area of the pivot table, and select Top from
> its dropdown list.
>
> GILESCIS wrote:
> > Debra,
> > I have a similar request.
> > I have a colum that has gross sales in a pivot table along with other data.
> >
> > I want to do a query (or custome lookup) if the gross sales is greater
> > then 50,000.00 and less then 100,000
> > then i only want those records to appear in the pivot table.
> > Is this possible and how,
> >
> > Thanks
> > Dean Castaldo
> > Giles Chemical Corp
> >
> >
> >
> > "Debra Dalgleish" wrote:
> >
> >
> >>A Pivot table has a Top 10 feature that you can use to show only the top
> >>values.
> >>
> >>Or, you could add a column to the source data, and use the SUMIF or
> >>SUMPRODUCT function to calculate if the total for an item is over your
> >>set limit. Then, add that field to the page area, and use it as a filter.
> >>
> >>Angus wrote:
> >>
> >>>I want to show the data greater than a specific number, say 10, in a pivot
> >>>table, like what can be done by autofilter in raw data.
> >>>
> >>>I know I can use "autofilter" at pivot table, but it doesn't apply to the
> >>>new pivot tables created by "show pages". How to keep showing data greater
> >>>than 10 at the new pivot tables created by "show pages".
> >>
> >>
> >>--
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[Debra Dalgleish]

That's why I mentioned putting the gross sales target in cell K2 -- you
could change it as required, and the formula results would change
automatically.

For a range, you could use minimum and maximum cells, e.g.:

=IF(AND(SUMIF($C$2:$C$44,C2,$G$2:$G$44)>=$K$2,
SUMIF($C$2:$C$44,C2,$G$2:$G$44)<=$L$2),"Show","Hide")

Gilescis wrote:
> Well this is sort of what I wanted but i never know what my query range is
> going to be, today it could be >50,000 & < 100,000 but next week i may
> want to search for sales > 65000 but < 85000
>
> so my query could change at anytime
>
> thanks
> Dean Castaldo
> Giles Chemical Corp
> [email protected]
>
>
>
> "Debra Dalgleish" wrote:
>
>
>>You could add a column to your source table, and use a SUMIF formula to
>>calculate the total for each customer. For example, with customer names
>>in column C, sales in column F, and your gross sales target in cell K2:
>>
>> =IF(SUMIF($C$2:$C$500,C2,$F$2:$F$500)>$K$2,"Top","Bottom")
>>
>>Add this field to the page area of the pivot table, and select Top from
>>its dropdown list.
>>
>>GILESCIS wrote:
>>
>>>Debra,
>>>I have a similar request.
>>>I have a colum that has gross sales in a pivot table along with other data.
>>>
>>>I want to do a query (or custome lookup) if the gross sales is greater
>>>then 50,000.00 and less then 100,000
>>>then i only want those records to appear in the pivot table.
>>> Is this possible and how,
>>>
>>>Thanks
>>>Dean Castaldo
>>>Giles Chemical Corp
>>>
>>>
>>>
>>>"Debra Dalgleish" wrote:
>>>
>>>
>>>
>>>>A Pivot table has a Top 10 feature that you can use to show only the top
>>>>values.
>>>>
>>>>Or, you could add a column to the source data, and use the SUMIF or
>>>>SUMPRODUCT function to calculate if the total for an item is over your
>>>>set limit. Then, add that field to the page area, and use it as a filter.
>>>>
>>>>Angus wrote:
>>>>
>>>>
>>>>>I want to show the data greater than a specific number, say 10, in a pivot
>>>>>table, like what can be done by autofilter in raw data.
>>>>>
>>>>>I know I can use "autofilter" at pivot table, but it doesn't apply to the
>>>>>new pivot tables created by "show pages". How to keep showing data greater
>>>>>than 10 at the new pivot tables created by "show pages".
>>>>
>>>>
>>>>--
>>>>Debra Dalgleish
>>>>Excel FAQ, Tips & Book List
>>>>http://www.contextures.com/tiptech.html
>>>>
>>>>
>>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Gilescis]

Well that almost work except i was checking the values and i set my ranges
for >=50,000 but <=100000.00

there was a record that had a value of 72,000 and it returned hide instead
of show.. Actually there was about 15 records like this.

Any Idea


"Debra Dalgleish" wrote:

> That's why I mentioned putting the gross sales target in cell K2 -- you
> could change it as required, and the formula results would change
> automatically.
>
> For a range, you could use minimum and maximum cells, e.g.:
>
> =IF(AND(SUMIF($C$2:$C$44,C2,$G$2:$G$44)>=$K$2,
> SUMIF($C$2:$C$44,C2,$G$2:$G$44)<=$L$2),"Show","Hide")
>
> Gilescis wrote:
> > Well this is sort of what I wanted but i never know what my query range is
> > going to be, today it could be >50,000 & < 100,000 but next week i may
> > want to search for sales > 65000 but < 85000
> >
> > so my query could change at anytime
> >
> > thanks
> > Dean Castaldo
> > Giles Chemical Corp
> > [email protected]
> >
> >
> >
> > "Debra Dalgleish" wrote:
> >
> >
> >>You could add a column to your source table, and use a SUMIF formula to
> >>calculate the total for each customer. For example, with customer names
> >>in column C, sales in column F, and your gross sales target in cell K2:
> >>
> >> =IF(SUMIF($C$2:$C$500,C2,$F$2:$F$500)>$K$2,"Top","Bottom")
> >>
> >>Add this field to the page area of the pivot table, and select Top from
> >>its dropdown list.
> >>
> >>GILESCIS wrote:
> >>
> >>>Debra,
> >>>I have a similar request.
> >>>I have a colum that has gross sales in a pivot table along with other data.
> >>>
> >>>I want to do a query (or custome lookup) if the gross sales is greater
> >>>then 50,000.00 and less then 100,000
> >>>then i only want those records to appear in the pivot table.
> >>> Is this possible and how,
> >>>
> >>>Thanks
> >>>Dean Castaldo
> >>>Giles Chemical Corp
> >>>
> >>>
> >>>
> >>>"Debra Dalgleish" wrote:
> >>>
> >>>
> >>>
> >>>>A Pivot table has a Top 10 feature that you can use to show only the top
> >>>>values.
> >>>>
> >>>>Or, you could add a column to the source data, and use the SUMIF or
> >>>>SUMPRODUCT function to calculate if the total for an item is over your
> >>>>set limit. Then, add that field to the page area, and use it as a filter.
> >>>>
> >>>>Angus wrote:
> >>>>
> >>>>
> >>>>>I want to show the data greater than a specific number, say 10, in a pivot
> >>>>>table, like what can be done by autofilter in raw data.
> >>>>>
> >>>>>I know I can use "autofilter" at pivot table, but it doesn't apply to the
> >>>>>new pivot tables created by "show pages". How to keep showing data greater
> >>>>>than 10 at the new pivot tables created by "show pages".
> >>>>
> >>>>
> >>>>--
> >>>>Debra Dalgleish
> >>>>Excel FAQ, Tips & Book List
> >>>>http://www.contextures.com/tiptech.html
> >>>>
> >>>>
> >>>
> >>
> >>--
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>