Pivot table data

[sergiovame]

In excel 2003, after getting a summary of my data in a pivot table, (my example below) the table summarizes the information in a way that higher levels conserve the labels (CC1, PA) but lower levels of information will not show it ? ie. in my example, the CC and the Estate are not shown as part of the product 2 even though they are under the category CC1 and PA.


CC |Estate|PRD| $ |


CC1|PA     | 1    |10

     |          | 2    |20

     |          | 3    |35

CC1 Total  65


CC2|NJ     | 5   |15

     |          | 6   |25

     |          | 7    |30

CC2 Total 70


Is there any way in the pivot table to show for products 2, 3, 6 and 7 the information related to the Estate and CC?


Thank you,

 

[Luke M]

Not in a PivotTable. To get what you want, you'd need to:

1. Copy the data, paste secial - values somewhere else

2. Select the CC1 and PA columns, going down as far as necessary

3. Bring up the Goto dialogue (Ctrl+G)

4. Press Special, choose blanks

5. Type "=" (sans quotes)

6. Hit the up arrow, then Ctrl+Enter

7. Voila! All the blank cells should now be filled in.

 

[sergiovame]

Thanks for the answer. However, when dealing with large amount of data this could be a long time task... Doesn't the pivot table give you the option to completed the information not shown? or using any other more efficient way ?


Thx,

 

[Luke M]

Afraid not, unless you have XL 2010. Linky:

http://www.contextures.com/xlfaqpivot.html#Repeat

 

[mooks]

As a work-around.. you can build a helper column outside the pivot where the data can be filled. Not best practice... but produces the desired effect.