I got a problem with my pivot table. In my sales table it doesnt show me the last dates of my sales time period. I guess its because there have been no sales in the last two days (values = 0). For another sales region the same table does show me these dates because there have been for example sales of 5, 8, and 10.
Here an impression: enter image description here
So there has been actually a 08/04, 09/04,10/04 and I need these values (even though they are 0) to calculate some other values. Do you know how I can force the pivot table to show all dates? I guess it somehow hides these right now.
Happy for any idea or solutions!
Thanks a lot!
Lisa
1 Answer
Is the "Region" a column of data you are using to filter your Pivot Table? Or do you mean you have two sets of data for two regions, and two independent Pivot Tables for these?
If it's the first one, you can tell Excel to stop hiding data which has no values to show at the moment.
Right click the field heading, in this case for one of your dates (or in the task pane use the drop down arrow on the field item). Select "Field settings". On the Layout and Print tab, select the checkbox "Show items with no data". OK. Refresh Pivot Table and see if it works.
If your data is two separate tables then you could cheat and add dummy rows to your data to show all possible dates, then refresh, then delete the dummy data and refresh again. Tedious, but possible (you could do this to both to add dates out to some reasonable future point eg end of month, end of year, then use filters to not show too far into the future at any point).
