Here are the steps necessary to generate a Pivot Table in Excel:
Some experimental software outputs results in plain text format or as .csv files. These can be imported into Excel without any extra work. However, some software outputs results in a proprietary format that must be converted before Excel can read the data.
In Step 1, select Delimited and click Next.
In Step 2, check the Tab option from the list of Delimiters and click Finish.
4. Delete Row 1
By default, E-Prime outputs the file path in the first line of the .txt file, which becomes row 1 when you import it into Excel. Delete it. This will cause row 2 to move up. Now row 1 contains your column headings which will become your Pivot Table fields.
Select all active columns. To do this, click on column A (by clicking on the A) and hold down the Shift key and click on the column label of the right most column (mine is HZ). This can be done using the keyboard by pressing Command+Shift+right arrow while column A is selected.
Click on the Insert ribbon and then Pivot Table. When the Create Pivot Table dialog appears, click OK.
6. Build your Pivot Table
Drag the fields that you are interested in into the Rows, Columns or Values areas. Subject/participant normally goes into the Rows area. The measures of interest (e.g., accuracy or reaction time data) usually go into Values.
This gives you options to calculate the sum, average, maximum, minimum, standard deviation and so on. For experimental data, most of the time, we are interested in averages.
Spend time playing around with the fields in the Pivot Table Builder. You can explore your data in powerful ways, such as by isolating particular subjects, conditions, or cases.
Pivot Tables are great tools for exploring your data. They offer a degree of flexibility that is unmatched. They are very useful for answering "What happens if..." questions, and the calculations can be done very quickly.