Pivot Table - eliminating irrelevant (empty) subcategories

I have 2 questions which I think ultimately come down to not knowing how to phrase my Google searches:

The more important question:

2) Is there a way to make Excel "hide" the subcategories which are irrelevant due to my current selection of sorting criteria? Example: Column A consists of either X, Y, or Z. Option X has corresponding values of 1, 2, 3 in Column B; Option Y has values of 2, 3, 4 in Column B; Option Z has values 2, 3, 5 in Column B. In my pivot table, I select only option X, meaning that the subcategories 4 and 5, which exist in my data set, are irrelevant based upon my current selection criteria (Option X only). Similarly if I selected X and Z only, I would want the sorting options for Column B to display 1, 2, 3, 5 but eliminate the ability to select 4 as an option, because it only corresponds to Option Y, which I have not selected to sort for/display.

This obviously becomes more useful as the number of categories and the number of data points become quite large, as is the case in my current application. I hope this example makes the question clear.

Any and all help would be hugely appreciated- thanks!


[Solved] (and thus moved down)
1) In my data table, I have on column referring to a measurement which only has 2 input options: call them 5 and 10. I dragged a "5" cell down for a large part of the column, not realizing it was adding 1 to each instead of copying the cell. I made the pivot table and realized what had happened once I saw the subcategories 5,6,7,8,etc in the corresponding column. I went back and changed all the data to exclusively 5s and 10s (and formatted the cells to only accept those 2 inputs), but no refreshing of the table is eliminating the empty subcategories "6,7,8" etc. Is there a way to get rid of those without changing the range of the data source and then changing it back, thus losing my pivot table formatting?

For #1:
Right click on Pivot Table - Select "Pivot Table Options" - select the "data" tab - set "Number of items to retain per field:" to "None"

It is difficult to get a good idea of what you are describing in #2 without seeing a sample workbook of the scenario you described.
I made the attached file. Is this simliar to what you are dealing with?

the table on the left has "Show items with no data" selected, which I assume you want. By using Slicers, you could easily see that "4" does not exist in the current Pivot table, and you could select only options 1,2,3 & 5. (click on "1", hold shift, click on "5")

The table on the right does not have "show items with no data" selected. X only shows 1,2,3 and Z shows 2,3,5.
Not sure if this will work for you, but it might.


I believe The scenario you are describing might be similiar to one I ran into earlier this week. In my scenario, column B represented a week #. You would want week #2 to show even if their was no data, so you have "show items with no data" selected, however you have data for a few years, and you are filtering for only the current year, so it would end up show all 52 weeks, despite the fact that obviously only the first 25 or so weeks will have data for 2013. You would want the pivot table to not show weeks 26-52 because there is NO data for any of the items in column A (when you filter for 2013).
I had difficulty figuring out a solution. If you don't select "show items with no data", it will exclude weeks where their is no data, so the table could go from week 1, to week 3, to week 7, which I did not want. If you select "show items with no data", it includes a number of weeks which don't have data yet. Frustrating!

I figured out a work-around solution for my issue, because it involved dates, but I am not sure of a good solution to your issue rather than manually selecting the entries on the slicer. A macro might exist that could do what you are looking, but I am not sure how to construct that.

That would almost certainly require a Macro. Would a Slicer be a possible option? It would put the applicable options at the top of the list, and the items with no data would be visibly different.