Hi,
does anyone know how to create a pivot table that does not skip through blank data cells. I have a pivot table with data for several dates, but not every date has a data point. I would want the pivot table to show all the dates with the blanks, rather than skip through the days with no data. can anyone help? would there be a VBA code that could do the job?
Can you post the spreadsheet without your confidential data?
Draconi
hi, sorry for the delay.
yes, I have posted the spreadsheet with the pivot table.
you'll notice that when you filter the data with the slicers, if a particular date and time has no data in any of the columns, it will be excluded from the pivot table. Such as 10/26/13 7:00:00 AM. I would like to be able to ensure that blank data cells show up for every date and time, but I would like to do it, without using blank place holders, because there are just too many different combinations to account for each one. thanks for the help!
When I viewed your workbook, at the bottom of the slicer was a section for Excluded items and Exclude Blanks was in there. Right click that section and delete. All blanks should now appear.....at least they appeared for me.
The exact name of this was "Exclude Data Point".
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Hi New Doverman,
Thats by design, to remove the bad data points. what I would want to do is to exclude all the bad data points, and still have blanks where the data used to be. it works, except where all the data in one row is excluded, in which case the row disapears. thanks though.
Without access to the source data, no checking of the solution is possible as the table doesn't update but there is a setting to check...In the Row Labels box, select each item, select field settings, Layout and Print, check Show Items with No Data.
I cannot verify this because of the above lack of access to source data.
Thanks New Doverman! Thats exactly what I needed to know! Thats one of those hidden things in Excel that drives people insane trying to fix.
thank you! you've just given me the most usefull thing I've learned all day!
Thank you!
You're welcome. I had forgotten about this until you said that the first solution wouldn't work.
Thank you for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks