Using Excel 2007, in pivot table, is there a way to put show detail for EVERY
cell?

Example: User has raw survey data consisting of one row per respondent;
includes ID, question and response.

ID....question....response
AB...Q1...91
AB...Q2...81
AB...Q3...71
CD...Q1...62
CD...Q2...52
CD...Q3...42
EF...Q1...33
EF...Q2...23
EF...Q3...13

User wants table like this.
ID.....Q1.....Q2.....Q3.....Avg
AB.......91.....81.....71...._81
CD.......62.....52.....42..._52
EF.......33.....23.....13..._23
Avg.._62...._52...._42

So in pivot table, is there a way to put show detail for EVERY cell? Or is
there another quick/easy way to create a table like the above?

I guess I don't understand the question. Should just create the pivot with
ID in Rows and Question in Columns with Response in Data Fields, change Field
settigns to Average.

"johng" wrote:

Using Excel 2007, in pivot table, is there a way to put show detail for EVERY
cell?

Example: User has raw survey data consisting of one row per respondent;
includes ID, question and response.

ID....question....response
AB...Q1...91
AB...Q2...81
AB...Q3...71
CD...Q1...62
CD...Q2...52
CD...Q3...42
EF...Q1...33
EF...Q2...23
EF...Q3...13

User wants table like this.
ID.....Q1.....Q2.....Q3.....Avg
AB.......91.....81.....71...._81
CD.......62.....52.....42..._52
EF.......33.....23.....13..._23
Avg.._62...._52...._42

So in pivot table, is there a way to put show detail for EVERY cell? Or is
there another quick/easy way to create a table like the above?

Sorry, a couple factors are actually different than my first example:
1. User wants count, not average (a minor change)
2. Responses contain TEXT (this is probably significant).
*In Excel 2007 my PivotTable task pane has SIGMA Values, NOT Data Fields.

EXAMPLE 2: raw data for ID ... Question ... Response
AB ... COLOR... red
AB ... FRUIT... apple
AB ... PET ... dog
CD ... COLOR... yellow
CD ... PET... cat
EF ... COLOR... green
EF ... FRUIT.. kiwi

User wants table like this:
ID ... COLOR ... FRUIT ... PET
AB ... red ... apple ... dog
CD ... yellow ... banana ... ___
EF ... green ... ___ ... parakeet
Count ..... 3 ..... 2 ..... 2

Pivot table looks like this:
..... COLOR ... FRUIT ... PET... GrTotal
AB .......... 1 ..... 1 ......... 1 ....... 3
CD .......... 1 ..... _ ......... 1 ....... 2
EF .......... 1 ..... 1 ......... _ ....... 2
GrTotal ... 3 ..... 2 ......... 2 ....... 7

I checked the ribbon PivotTable Tools Options Active Field Fields
settings, which include SUM, COUNT, AVERAGE, MAX, MIN, other stats (same as
SIGMA Values), but NOT detail.

"Sean Timmons" wrote:
I guess I don't understand the question. Should just create the pivot with
ID in Rows and Question in Columns with Response in Data Fields, change Field
settigns to Average.

Pivots will only show numbers in the Data Fields section. To show text, you
would want to use an INDEX() with MATCH() as an array search.

"johng" wrote:

Sorry, a couple factors are actually different than my first example:
1. User wants count, not average (a minor change)
2. Responses contain TEXT (this is probably significant).
*In Excel 2007 my PivotTable task pane has SIGMA Values, NOT Data Fields.

EXAMPLE 2: raw data for ID ... Question ... Response
AB ... COLOR... red
AB ... FRUIT... apple
AB ... PET ... dog
CD ... COLOR... yellow
CD ... PET... cat
EF ... COLOR... green
EF ... FRUIT.. kiwi

User wants table like this:
ID ... COLOR ... FRUIT ... PET
AB ... red ... apple ... dog
CD ... yellow ... banana ... ___
EF ... green ... ___ ... parakeet
Count ..... 3 ..... 2 ..... 2

Pivot table looks like this:
..... COLOR ... FRUIT ... PET... GrTotal
AB .......... 1 ..... 1 ......... 1 ....... 3
CD .......... 1 ..... _ ......... 1 ....... 2
EF .......... 1 ..... 1 ......... _ ....... 2
GrTotal ... 3 ..... 2 ......... 2 ....... 7

I checked the ribbon PivotTable Tools Options Active Field Fields
settings, which include SUM, COUNT, AVERAGE, MAX, MIN, other stats (same as
SIGMA Values), but NOT detail.

"Sean Timmons" wrote:
I guess I don't understand the question. Should just create the pivot with
ID in Rows and Question in Columns with Response in Data Fields, change Field
settigns to Average.