• First, enter your data in Excel.

• The example is from an acceptability judgement questionnaire.

• Here I have columns for Subject, Group, Item, Condition and “1=yes” (acceptability).

Using Excel: data entry and pivot tables

Select your data.Select Pivot Table Report from the Data menu

The Pivot Table wizard appears: our data is in an Excel database.

Click ‘next’.

Making a pivot table

If necessary, fill in the range. Click ‘next’.

Choose where you want the table to appear, and click on ‘layout’.

On the right are buttons for each of the column headings.Drag the appropriate buttons onto the diagram

We need to choose ‘subject’, ‘condition’ and ‘1=yes’ for a by-subjects analysis.

Note that the default for the data field is ‘count’. We want the average, so double-click on the button to change it

You can choose different kinds of summaries -- we want the average

Click ‘OK’, and then click ‘finish’ on the next window.

The result: a Pivot Table.

This one shows us the average rate of acceptability in the ‘e’ and ‘s’ conditions, for each subject.

You can do a quick t-test in Excel using the following syntax:

=ttest(condition1data, condition2data, 2,1)2 = 2-tailed

1 = paired

You can make more complicated pivot tables -- let’s look at the subjects’ averages by condition and group.