Excel 2016 Windows 10 PivotTable and Chart - How to limit the number of items shown with no data

Copper Contributor

Hello,

I am working on creating a PivotTable and Chart that shows the last 13 months (If currently Dec 2018 it shows Dec 2017 thru Dec 2018) of Expenses grouped by Month and Year. My "Raw Data" consist of Multiple rows of Expense Dates ranging from 2015-2019. I already built a Pivot Filter that will show me the last 13 months based on the selected current month. My issue is: I want to see each (13) months on my table/chart even if there is no expense for that month. I have went into the Field Setting of my Expense Date Field and enabled "Show Items with no Data" under the Layout and Print tab. This makes more than 13 months show regardless of the Filter I have placed on the table/chart that is limiting to the specific 13 months. It shows from the beginning of my "Raw Data" to the end. My Question is: How do I limit the number of items shown with no data? In the example provided, I only want to see from DEC 2017 thru DEC 2018.

0 Replies