Why does my pivot have a column for dates that don't exist in my data?

[carrod65]

Hi,

My pivot table is working fine, but it has been really bugging me why I can't find the setting/option to disable this. I have data that shows the number of incidents handled per month, and the pivot table does a great job to summarize the data for this purpose. My dataset has entries with dates starting with 10/1/14 through the end of August 2016. When I create a pivot table, there is always a blank column that represents dates <10/1/2014.

Since I know my data has no entries from before 10/1/14, why does excel insist on adding this column? If I actually had entries from earlier days, excel's date groupings would just add that data in the correct way with a new column, so I don't see how this is ever helpful.

Thanks gurus.

2016-09-27 09_42_50-Microsoft Excel - PRD Service complaints raw data.xlsx.png

[etaf]

thats the way the grouping is working
you can click on that to stop it appearing

I have
< earliest date in the table and also
> latest date in the table
and i just untick those - so they do not appear on the table

[xlnitwit]

Hi,

It can be helpful if you wish to focus your analysis on a subset of your data. For example, I often have large sets of historical data but may only be interested in the last 5 years, so I set the earliest date to 5 years ago and everything prior to that date is simply grouped into one item that I can then easily hide with one click while maintaining detailed groupings for the data of interest to me.

[carrod65]

Thanks for your reply.

Is the grouping you are describing different from the grouping options available in this menu:

2016-09-27 10_19_14-Microsoft Excel - PRE Service complaints raw data.xlsx.png

Since the grouping allows me to specify start and end dates, I guess I want to know why the pivot insists on adding this column that will not have data based on the start dates I just typed in.

And if I only wanted to look at slices of the overall data (Say Q2 and Q3 of 2015), why would I not just use the pivot table filter for this? Grouping doesn't seem to make sense to me.


Like I said, I am getting the pivot table to do what I need, but I really want to understand some aspects of the pivots that are still foreign to me. Thanks again.

[xlnitwit]

It's a one-size-fits-all scenario. If you were to do as I describe, you would need the extra column so it is always created by the wizard. I assume it simplified the coding to do it this way.

However, you should not see that column if it has no data unless you have added a calculated item or set the field to show items with no data.

[carrod65]

I cannot, for the life of me, find how to quote posts in this forum, so my response is based on your comment:

"However, you should not see that column if it has no data unless you have added a calculated item or set the field to show items with no data. "

I think that is exactly what I am asking. I have gone through the pivot table options, field settings, and anywhere else I could think of trying to find a setting to change this. I've never used calculated columns in my pivots, so that should not be the case. But I can't seem to find a "set the field to show items with no data" option.

Within the field settings, there is a checkbox for "show items with no data", but this is not helpful. It just lists the rest of the 2014 months (Jan-Sept) with a value of 0, but does nothing to remove my <10/2/2014 blank column.

Thanks

[xlnitwit]

Do you have blanks in your date column? That would explain the appearance of the additional column.

[carrod65]

Thank you xlnitwit and all others that responded!

My data did not contain any cells that had the date missing, but your comment helped me understand what I was doing wrong.

I was using CRTL+A, insert pivot table. When I manually select the cells with actual data and then insert pivot table, the blank column I don't want to see disappears. So, while I had no rows that were missing the date created field, excel was likely treating blank rows after my data as possible data and I bet this is why it includes that <10/1/14 column.

Thank you! This had been driving me nuts, and since my data had 20K+ lines, it was faster to crtl+A vs selecting only the cells that have data.