Pivot Table Data

Occasional Contributor



I am having some trouble understanding how to get the data i want from a Pivot table. In my Sheet1 i have some data that is pasted from another application.


The Month and Week are then calculated from the Date.


I have several problems - if i select all the cells A-E then down to 200 (i won't know how many records will be pasted, so i use 200 never more t) then insert a Pivot table i get what is shown on the left in Sheet4.


What i want is what is shown on the right, which i can only get if i only select down to row 52, missing that last row which rolls over to week 1 month 1.


Also, as i don't  know how many records may be pasted, i can change the formatting to remove any uncalculated fields (B,C,E - 54-200) by setting 0;-0;;@, but this still causes a black to be shown in the table.


So what i want is the table on the right, from selecting all data A-F 1-200.


Is this possible? Many thanks for any help.



1 Reply


If you group dates the same way for both PivotTables and apply filter to left one the will be identical. Perhaps it's better to add Year to grouping and add it to the filter.