Psychology 212
Excel 2000 How-To

(Sorting, Filtering, Correlations and T-Tests)
Nick Yee
nyee@haverford.edu

Sorting:

Sorting allows you to arrange cases along with their corresponding values in ascending or descending order.

1)      Highlight the values that need to be sorted. Select not only the case names, but also the dependent values of the cases. Otherwise, Excel will sort the case name, but not the corresponding values and the spreadsheet will be rendered useless. (Excel warns you about this as well).

2)      Select Data > Sort from menu bar.

3)      If you labeled the columns they will appear here (Header Row), otherwise Excel labels them numerically (No Header Row). Choose the factor that you want to sort by. Then choose either ascending or descending. You can choose up to two other secondary sort criteria. When you are ready, press OK.

Filtering:

Filtering temporarily screens your cases by a criterion you set, such as age. The data that are hidden are not deleted.

1)      Highlight all column labels, cases and their values. In most cases, pressing CTRL+A will accomplish this.

2)      Select Data > Filter > AutoFIlter.

3)      You will notice that drop-down arrows have been added to each column name. Clicking on this arrow brings up a menu of all the values that were under that column. You can filter by a specific value. Try that now and then choose ALL to bring back all the values.

4)      You can also filter by a criterion. Click the arrow again and choose custom.

5)      The window that pops up is fairly intuitive in what it does. To show only ages over 15, change “Equals” to “Is Greater Than” and enter or choose a value. Then press OK.

Correlations:

Excel can give the correlation coefficient for two sets of values.

1)      Highlight an empty cell. This is the cell where Excel will output the correlation coefficient.

2)      Select Insert > Function.

3)      Change the category to Statistical on the left hand side and then choose “CORREL” from the list on the right. Press okay.

4)      In the new window that pops up, you have to input the two sets of values. Instead of typing them in manually, use your mouse and highlight the first column of values. Notice these are automatically entered. Then click the 2nd array input field and highlight the second column of values.

5)      Notice that once you have entered two sets of values that the coefficient appears in the bottom part of the window. So choosing an empty cell first is not strictly necessary. Pressing OK places this value into the empty cell.

T-Test:

Excel can give you the T-test of the statistical significance of the difference between the means of two sets of values. This section is essentially identical to the one before on doing a correlation.

1)      Highlight an empty cell. This is the cell where Excel will output the T-test.

2)      Select Insert > Function.

3)      Change the category to Statistical on the left hand side and then choose “TTEST” from the list on the right. Press okay.

4)      In the new window that pops up, you have to input the two sets of values. Instead of typing them in manually, use your mouse and highlight the first column of values. Notice these are automatically entered. Then click the 2nd array input field and highlight the second column of values. Choose the appropriate Tails and Type value. Read the help that appears in the window as you select the input field of each parameter.

5)      Notice that once you have entered the two sets of values and the type that the coefficient appears in the bottom part of the window. So choosing an empty cell first is not strictly necessary. Pressing OK places this value into the empty cell.