Pivot Table Question

[ashfire]

im trying to run some data from pivot tables but am getting annoyed with the fact that it groups like items together, and cant find an option to "show all".


e.g i have 2 people A and B and each have products 1,2,3,4. If i ran a pivot with people then product i would get one A group with 4 product lines and one B group with 4 product lines. As you know A and B would be aligned with product 1.


However i would also like A and B to show next to products 2,3 and 4.


I cant change the data around as its much more complicated than my example, cheers

 

[keymaster]

Ashfire.. I believe this is a limitation of excel versions up to 2010. (in 2010 you can extend labels...). You can fix this in the following way...


Copy the pivot table data as values in to an empty sheet. Lets say Range A1:B10 has the labels with A - name and B - Product.


Now, create 2 separate columns in the end. Lets say they are E and F.


in E1 write = A1

in E2 onwards, =if(A2<>"",A2,E1)


Drag E2 to fill up to E10.


Now copy E1:E10 to F1:F10. This should give you repeated labels....


All the best.

 

[ashfire]

Cheers Chandoo, thats much quicker than copying and pasting, and the formula will be in use for quite some time, as Excel 2007 is just about to be approved for use in the business...


Cheers again