Yet when I filter the pivot where there is only 1 row of data, other rows still show the full data set.

Note: We are using Excel 2016 for this Excel. I have also gone to each of the fields and under field settings have I went to 'Layout & Print' and ensure that the box labeled 'Show Items with No Data' is unchecked. Excel provides you the worksheet to create a new document in it. I have tried doing 'Right click on Pivot > Display > Uncheck 'Show items with no data on rows' or 'Show items with no data on columns' (For excel 16)' I can see that the boxes are unchecked and grayed out. Is this doable? I can use Excel 2010 or 2016. I understand that with slicers Sep - Dec would be greyed out in the slicer, but I want the pivot table slicer to behave like when there are no slicers and only show me available options. When there are slicers, although I filter (using either slicer or pivot table filter) on Year 2018, Jan - Dec are available (pivot table filter) although there are no records for Sep - Dec. Displayable options in the Month field are Jan - Jun, since that is all that is available for this year. In the sheet of the Pivot Table, click inside the Pivot Table display area and click on Slicer. Insert a Pivot Table using this as the data source.

When there are no slicers present, and I filter a field using the pivot table, the other filters display the subset that is available within that option.Įx. Create a two column and four rows table like below in a new Excel file (better if this is the only Excel file open on your PC).

How to use slicers in excel 2016