Hi All,

Working with Excel 2010 and doing pivot tables with a large data set.  I've grouped my display columns into Months / Years from a date column in the source data.  I'm summarising the data as a count (then using conditional formatting to make a heat map). 

The problem is that if I don't have a full years worth of data (say range is 1/1/14 - 31/5/15) then I end up with a bunch of unnecessary columns on the right hand side for all the months in the year that are missing -- see the image below. 

How do I stop those columns from appearing? 

Hope that's enough info for you to understand my problem.  Thanks for your help

CammersNZ

edit:  Can't insert the image yet -- will get that sorted ASAP.

• Edited by CammersNZ Monday, May 18, 2015 1:30 AM

Hi CammersNZ,

Based on your description, I test your issue in my own environment. I get a result as shown in the following figure, but Im not sure if this is your issue. As shown in the following figure, on the right hand side there are columns contain whole 2 years data for each month.

I find these months appeared by field setting in Date of COLUMNS, you can click Date under COLUMNS and select field setting, then make sure subtotals check the None option. As shown in the following figure.

Now your pivot table is clearly without the columns contain the whole 2 years data for each month.

If my understanding is incorrect, please upload a shortcut about your issue via third upload image web site and share the URL to me. Or, if you need your account verified to post images or links sooner, you can email "fissues" at Microsoft and ask them to verify your account. Then you can upload images here.

About how to verify your account, please refer to this link:

http://social.technet.microsoft.com/wiki/contents/articles/15960.how-to-verify-your-msdntechnet-forums-account-so-that-you-can-post-images-and-links.aspx

Im glad to help and follow up your reply.

Regards,

George Zhao
TechNet Community Support