Pivot table question

[gary]

I have questionnaire data laid out horizontally like this:

Session Session1
Question1 Question 2
Person
1 1 4
2 3 5

The numbers lining up with Question1 and Question2 are the numeric responses
of
Person1 and Person2. There are about 16 sessions and each one can have a
differing number of participants. There are 10 questions for each session.

In order to get a pivot table that aggregates each session and gives the
average for each question in each session, I had to re-arrange the data
vertically like this:

Session Person Question Answer
session1 1 1 1
session1 1 2 4
session1 2 1 3
session1 2 2 5

My pivot looks like this:

SESSION PERSON QUESTION1 QUESTION2
session1 1 1 4
2 3 5
SESSION1 TOTAL 2 4.5

(that last line is the average of all the answers to the question)

My question is: was it really necessary to arrange the data vertically or
could the pivot have been done off the original data structure? I just
couldn't get it to work.

If the answer is "yes, the data needs to be arranged vertically", then
could someone suggest an easy way to re-arrange the data which is already
entered
horizontally on a sheet in the workbook? It can all be rekeyed if necessary
but I'd like to avoid it.

Lastly, if there is an online resource that addresses these questions, feel
free to point me to it. I wasn't able to quickly find anything in the great
mass of Excel tips on the web.

Thanks,
gary

[Roger Govier]

Hi Gary

On your Pivot Table, try double clicking on the Grand Total cell at the
bottom Right of the PT.
On the sheet that open up, does this now have the data in the format you
want?
If so, just rename the column Headings, from Row and Column to what they
should be, and perform a new pivot based upon this data.

--
Regards

Roger Govier


"gary" <[email protected]> wrote in message
news:[email protected]...
>I have questionnaire data laid out horizontally like this:
>
> Session Session1
> Question1 Question 2
> Person
> 1 1 4
> 2 3 5
>
> The numbers lining up with Question1 and Question2 are the numeric
> responses
> of
> Person1 and Person2. There are about 16 sessions and each one can
> have a
> differing number of participants. There are 10 questions for each
> session.
>
> In order to get a pivot table that aggregates each session and gives
> the
> average for each question in each session, I had to re-arrange the
> data
> vertically like this:
>
> Session Person Question Answer
> session1 1 1 1
> session1 1 2 4
> session1 2 1 3
> session1 2 2 5
>
> My pivot looks like this:
>
> SESSION PERSON QUESTION1 QUESTION2
> session1 1 1 4
> 2 3 5
> SESSION1 TOTAL 2 4.5
>
> (that last line is the average of all the answers to the question)
>
> My question is: was it really necessary to arrange the data vertically
> or
> could the pivot have been done off the original data structure? I
> just
> couldn't get it to work.
>
> If the answer is "yes, the data needs to be arranged vertically",
> then
> could someone suggest an easy way to re-arrange the data which is
> already
> entered
> horizontally on a sheet in the workbook? It can all be rekeyed if
> necessary
> but I'd like to avoid it.
>
> Lastly, if there is an online resource that addresses these questions,
> feel
> free to point me to it. I wasn't able to quickly find anything in the
> great
> mass of Excel tips on the web.
>
> Thanks,
> gary
>