PivotTable for questionnaire analysis

Question

I have some data from a reply form that I need to compile. I have some questions that have values from 1-5 or No-Yes. Example data:

Date       Question1 Question2 Question3
2010-12-01      4       3       No
2010-12-01      5       5       No
2010-12-01      4       3       Yes
2010-12-01      4       4       Yes
2010-12-01      3       4       No
2010-12-01      3       4       No
2010-12-01      2       4       Yes
2010-12-01      2       3       Yes
2010-12-01      2       4       Yes
2011-01-01      4       3       No
2011-01-01      5       1       No
2011-01-01      3       5       No
2011-01-01      5       4       No
2011-01-01      4       3       No
2011-01-01      3       4       No
2011-01-01      4       4       No
2011-01-01      5       4       No
2011-01-01      5       4       No
2011-01-01      3       3       No
2011-01-01      4       4       No
2011-01-01      3       5       Yes
2011-02-01      3       4       No
2011-02-01      5       5       No
2011-02-01      4       3       No
2011-02-01      4       5       No
2011-02-01      4       3       Yes
2011-02-01      4       5       No
2011-02-01      2       3       No
2011-02-01      2       5       No

I want to make a pivot table that I can use to analyse my data. I would like that it's like:

<whatever>     1 2 3 4 5
2010-12-01     
-Question1     0 3 2 3 1  
-Question2     0 0 3 5 1
2011-01-01     
-Question1     0 0 4 4 4  
-Question2     1 0 3 6 2
2011-01-01     
-Question1     0 2 1 4 1  
-Question2     0 0 3 1 4

Or an even smarter pivot table for analysing, but has to be organized by date.

Answer 1

Unpivot (eg as described here), sort the Table on Column to move Question3 to the bottom. Convert to Range and pivot data down to the end of Question2 with Row above Column for ROWS, Value for COLUMNS and Count of Value for VALUES.

Add a row above the PT and filter the left-hand column to select greater than 0. Format the values to blend into the background. In PivotTable Options, enter 0For empty cells show: . Result should be something like (month does not match for the bottom set):

Answer 2

Best solution I found was not a PivotTable. It was most readable when I used merged cells insted.