Home

Pivot table is counting empty cell

%3CLINGO-SUB%20id%3D%22lingo-sub-364667%22%20slang%3D%22en-US%22%3EPivot%20table%20is%20counting%20empty%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364667%22%20slang%3D%22en-US%22%3E%3CP%3ETo%20all%20the%20Excel%20gurus%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20which%20has%20data%20after%20a%20date%2010%2F3%2F18.%20However%2C%20when%20I%20created%20a%20pivot%20table%2C%20it%20created%20a%20new%20group%20for%20the%20dates%20before%2010%2F3%2F18%20and%20even%20generated%20the%20total%20for%20the%20sales%20before%20that%20date%2C%20which%20don't%26nbsp%3Bexist%20at%20all!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyone%20has%20an%20idea%20where%20and%20why%20there%20is%20data%20before%20that%20date%3F%20Is%20there%20any%20way%20I%20can%20get%20rid%20of%26nbsp%3Bit%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-364667%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-365111%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20is%20counting%20empty%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-365111%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much!%20The%20solution%20is%20so%20simple!%20How%20come%20I%20didn't%20know%3F!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364679%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20is%20counting%20empty%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364679%22%20slang%3D%22en-US%22%3E%3CP%3ERight-click%20the%20date%20field%20and%20select%20%22Group...%22.%20Make%20sure%20the%20two%20check-boxes%20at%20the%20top%20are%20checked.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

To all the Excel gurus, 

 

I have a spreadsheet which has data after a date 10/3/18. However, when I created a pivot table, it created a new group for the dates before 10/3/18 and even generated the total for the sales before that date, which don't exist at all! 

 

Anyone has an idea where and why there is data before that date? Is there any way I can get rid of it? 

2 Replies

Right-click the date field and select "Group...". Make sure the two check-boxes at the top are checked.

Highlighted

Thank you so much! The solution is so simple! How come I didn't know?!

 

Thanks again!