Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to show data greater than 10 in pivot table
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". |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#6
|
|||
|
|||
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","Hid e") 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 "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 |
#7
|
|||
|
|||
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","Hid e") 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 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keep conditional format when "show pages" from Pivot table | Excel Discussion (Misc queries) | |||
Pivot table of data in more than one worksheet | Excel Discussion (Misc queries) | |||
Problems with Pivot Table Field Sorting in Excel 2002 | Excel Discussion (Misc queries) | |||
Pivot Table Auto Update Data Source? | Excel Worksheet Functions | |||
Pivot table data | Excel Discussion (Misc queries) |