I’m working on on a pivot table summarizing items in several groups which are broken down into sub groups, and then into individual items which are then totaled up for dollar amount. I’m working in Excel 2003.
I’m wondering if there is a way to show only items which exceed a certain dollar amount. I can see how to do a top 10 show (and adjust that to a different number), but it will still show me the top 10 (or whatever number I chose) items for each group or subgroup that I set it up for. I’d like to set a dollar limit by item instead and have it show only those items that exceed that amount. Some groups might have more than 10 items shown, others could have 1 or none. I still want to see the actual totals listed for each group and sub group including everything, I just don’t want to see item detail listed if they are under my threshold.
When I put an Auto Filter on the list of data (i.e. the source for the pivot table) and filtered for a subset of values, the pivot table changed and only showed the subset data.
Another option is to append a extra column or row to your base data with an IF statement that checks if the data in the column/row exceeds your threshold and then returns Yes/No or 1/0.
Then rebuild the pivot and you can choose that field as a filter.
May or may not be feasible depending on the kind of analysis your doing.
If I read the OP correctly, your “top 10” is based on a category sub totals, and your data is transactional.
An approach for that, though it’s not particularly elegant:
Use your pivot table to summarise the data by whichever category you want.
Paste as values that data into a new worksheet.
Use an IF statement to flag each category as “Top 10” or “”
Use vlookup to put the flag on each row in the data table.
On refreshing the pivot table, you can use the flag as a filter in the pivot table.
The problem is pivot table filters normally affect the totals. I think circumventing this will be difficult. I suggest copying the pivot table (values and formats) with too many detail rows into another worksheet and manually deleting the items you want to exclude. In other words, cheat.
It’s probably one of those esoteric options on the Tools-Options menu.
The simplest way to do what the OP is asking is to take the original data, sort it by dollar value descending, then make the pivot table only on the rows that exceed the dollar threshhold.
FasterThanMeerkats’ solution works just as well, and is better if you need to easily toggle back and forth in the same pivot table between “all values” and “values in excess of $X.XX”.