PivotTable shows empty columns with "< 21/22/2016" as heading

[Beemerang]

Hi All,

I'm using PivotTables for the first time after years of manually generating reports and while I can see how powerful they can be, it's driving me nuts figuring out how everything works. I did do a couple of searches before posting but perhaps I am not using the correct search term as I found nothing related to my query.

I have a fairly simple list upon which I have based a PivotTable. Everything calculates fine but I have two columns appearing in the PivotTable to the right of the column that contains the year's subtotal with a header in the first column that says "<21/11/2016" and in the cell below it "(blank)" and another column to right of this one with the header "<21/11/2016 Total" and below that a blank cell.

Now I know that the date 21/11/2016 (UK format) is the date of the first list entry. The line directly above the first entry is the heading row. No rows are hidden. There are no list entries with a date that is prior to 21/11/2016 so I have no idea why these columns appear.

How can I prevent these columns from appearing every time the PivotTable us updated? Any help would be greatly appreciated!

Regards,
Beem

 

[Jerry Sullivan]

Hi Beem, What are settings for the Grouping for that field.

To check, right-click on one of the header dates, then Group....

Post the information displayed in the dialog window such as Auto, Starting, Ending, and which units highlighted under By

 

[Beemerang]

Hi Jerry,

Thanks for the reply.

Under Auto it has ticks for Start at 21/11/2016 and Ending at 25/11/2016 and By shows that Months and Years are selected. In the Number of Days field, it displays 1.

Regards,
Beem

 

[Jerry Sullivan]

Beem, What you describe could happen if you have one or more cells in your date field that are blank. Can you eliminate the records with the blanks in the date field? Then resize the source data reference to just include rows with records, if needed.

 

[Beemerang]

Hi Jerry,

There aren't any blank cells in the date field, every single cell is populated. This is why I am thrown by this.

Is there any other information I could provide that might be useful to you?

 

[Jerry Sullivan]

Check to see if your PivotTable options are set to retain deleted items. There's a dropdown setting on the Data tab. If that's currently set to Max or Auto, change it to None. Then Refresh your Pivot and see if that removes the unwanted columns.

 

[Beemerang]

Jerry, I've changed the setting to None without any change.

Again, thank you so much for taking the time to try and help me.

 

[Jerry Sullivan]

One other thing to try. Double click on one of the blank cells under the field "< 21/22/2016". If you have drilldown enabled in your Pivot, this will show which record(s) are being summarized in that field.

 

[Beemerang]

If I do that, it shows the header row and a blank row. This really has me stumped!

Thanks for all the suggestions.

 

[Jerry Sullivan]

Seems like Excel thinks there's a blank row in your source data.

Have you checked the address of the source data to make sure it isn't including the row below your dataset?