Show all values of a Pivot Table

[tcolan]

Hopefully this is an easy one but I'm stumped. I have a pivot table and instead of grouping the headings, I want every cell to show it's value.

Note that I've hidden all of the sub-totals.

So, instead of

X AA 1
BB 1
CC 1
Y AA 1
CC 1
Grand Total 5

I want to the Pivot table to display all of the values in column A, ie.:

X AA 1
X BB 1
X CC 1
Y AA 1
Y CC 1
Grand Total 5

Any advice is appreciated,
Tom

 

[xlbo]

can't be done in a pivot table

Easiest way to get theis result as a one off is to:

copy/paste values over the whole pivot table
Select the column where you want the data to duplicate down
Edit>Goto>Special>Blanks
(keystroke)=
(keystroke)up arrow
(keystroke)Ctrl+Return

et voila (thanks to Tony Jollans for that tip!)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

I usually insert an extra column to the left of the pivottable and generate the "missing" items with formulae. For example:

=IF(B4="",A3,B4)

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

OR use MS Query to return a summary. q68-5829

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tcolan]

Thanks!!!