using a character value for the data in a pivot table

[BillE]

I want to create a pivot table with a string value in the data portion.

In my case, I am presenting test results in the spreadsheet.

The row headers are the student name and test date/time.

The column headers are the questions in the test.

The data should display the character response to the question referenced in
the column header.

For example, a test might have the question "where do you live" (a column
header), and the answer is "USA" (the data).

The spreadsheet contains four columns: Name, TestDate, Question, Answer

I want to create a pivot table with the student name and test date in the
row header; the question ("where do you live") as the column header; and the
answer ("USA") in the data cell.

It seems that I am only permitted to use aggregate functions for the data
portion, like COUNT of Answer.

Can I do this?

Thanks
bill

 

[Bernie Deitrick]

Bill,

Pivot tables really are not designed to do what you want, but with a slight additional step, you can
get there.

Drag Name, Test Date, and Answer to the row area, and Question to the column Area. Then drag Answer
to the data area.

Use the drop down to limit the Question to "where do you live" and then copy the entire pivot table
and paste special values somewhere else. Then you can rearrange the header cells to move the 'where
do you live" to the cell with "answer", then deleter the column with the numbers.

HTH,
Bernie
MS Excel MVP

 

[BillE]

thanks

Bill,

Pivot tables really are not designed to do what you want, but with a
slight additional step, you can get there.

Drag Name, Test Date, and Answer to the row area, and Question to the
column Area. Then drag Answer to the data area.

Use the drop down to limit the Question to "where do you live" and then
copy the entire pivot table and paste special values somewhere else. Then
you can rearrange the header cells to move the 'where do you live" to the
cell with "answer", then deleter the column with the numbers.

HTH,
Bernie
MS Excel MVP

 

[Shane Devenshire]

Hi,

Depending on how may results are in the data are you can display text in it.
Let me know how many items there are, if less than about 600 you can do what
you want.