Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-687001%22%20slang%3D%22en-US%22%3EExcel%202016%20Windows%2010%20PivotTable%20and%20Chart%20-%20How%20to%20limit%20the%20number%20of%20items%20shown%20with%20no%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-687001%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20am%20working%20on%20creating%20a%20PivotTable%20and%20Chart%20that%20shows%20the%20last%2013%20months%20(If%20currently%20Dec%202018%20it%20shows%20Dec%202017%20thru%20Dec%202018)%20of%20Expenses%20grouped%20by%20Month%20and%20Year.%20My%20%22Raw%20Data%22%20consist%20of%20Multiple%20rows%20of%20Expense%20Dates%20ranging%20from%202015-2019.%20I%20already%20built%20a%20Pivot%20Filter%20that%20will%20show%20me%20the%20last%2013%20months%20based%20on%20the%20selected%20current%20month.%20My%20issue%20is%3A%20I%20want%20to%20see%20each%20(13)%20months%20on%20my%20table%2Fchart%20even%20if%20there%20is%20no%20expense%20for%20that%20month.%20I%20have%20went%20into%20the%20Field%20Setting%20of%20my%20Expense%20Date%20Field%20and%20enabled%20%22Show%20Items%20with%20no%20Data%22%20under%20the%20Layout%20and%20Print%20tab.%20This%20makes%20more%20than%2013%20months%20show%20regardless%20of%20the%20Filter%20I%20have%20placed%20on%20the%20table%2Fchart%20that%20is%20limiting%20to%20the%20specific%2013%20months.%20It%20shows%20from%20the%20beginning%20of%20my%20%22Raw%20Data%22%20to%20the%20end.%20My%20Question%20is%3A%20How%20do%20I%20limit%20the%20number%20of%20items%20shown%20with%20no%20data%3F%20In%20the%20example%20provided%2C%20I%20only%20want%20to%20see%20from%20DEC%202017%20thru%20DEC%202018.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-687001%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Visitor

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.

Related Conversations
Paste in the Filter search field does not work
luka23 in Excel on
0 Replies
Adding new data to a pivot table
Anthony Blackburn in Excel on
1 Replies
how to search a list of items in a cell
shekharreddy in Excel on
0 Replies
town-level heat map in excel?
mcmct in Excel on
0 Replies
Multiple crashes of Excel
SCSVCS in Excel on
0 Replies
Search for multiple values
JKLOSS48 in Excel on
2 Replies