# Excel Tutorial

### Text-only Preview

**LAURA COLOSI**

**Measuring Evaluation Results with Microsoft Excel**

The purpose of this tutorial is to provide instruction on performing basic functions using Microsoft

Excel. Although Excel has the ability to perform a large array of mathematical and statistical

functions, this resource addresses data entry, and calculating means (averages) for either one time or

pre/post survey instruments (or post/pre instruments). In addition, when an instrument is completed

at two points in time, Excel provides the ability to perform statistical tests (t-tests) to determine the

significance of mean differences between the pre and post test for participants receiving a program

(or treatment). Finally, additional resources to help you master Excel are listed at the end of this

brief.

**1.**

**Open Microsoft Excel.**

You will be put into a blank “workbook” which is

simply a blank spreadsheet. Please note that Excel

Figure 1. Column labels.

automatically numbers the rows in the left hand

A

margin, and assigns letters to the columns – these

1

Participant Number

will not change and you must label both your

2

1

rows and columns for your data entry and

3

2

analysis. (Excel uses these column letters and

4

3

numbered rows to perform mathematical

5

4

functions, which is explained later.)

6

5

7

6

2.

**Label your columns.**

8

7

Start with the top row and label each column, as

9

8

follows:

10

9

a. Column A = Participant Number or

11

10

identifying word, symbol, etc. [For

12

11

example, if you have 12 participants,

13

12

number from 1-12 DOWN the column]

See Figure 1, right.

b. The Column labels across the spreadsheet (B, C, D, etc.) need to be labeled for each item

on your survey that has a response that needs to be coded. [In this example, there are five

items (questions) to measure, so Column B gets labeled Question 1, Column C is labeled

item 2, etc.]

A

B

C

D

E

F

1

Participant Number

Question 1

Question 2

Question 3

Question 4

Question 5

c. Note also that it is helpful to include a key word from each question to help you remember

which question is which. For example, if your statement reads, “I am confident in choosing

books that are appropriate for my children,” then you may want to label that column,

“Question 1 Confidence” so you can understand your spreadsheet more fully.

A

B

C

D

E

F

Participant Question 1 –

Question 2 -

Question 3

Question 4

Question 5

1

Number

Confidence Insert key word Insert key word Insert key word Insert key word

3.

**The Spreadsheet.**Once your columns are all labeled and the participant numbers delineate

each row of responses, your spreadsheet should look like this:

A

B

C

D

E

F

1

Participant Number

Question 1

Question 2

Question 3

Question 4

Question 5

2

1

3

2

4

3

5 4

6 5

7 6

8 7

9 8

10

9

11

10

12

11

13

12

14

Mean

Score

4. Now you are ready to

**prepare your data**for entry into a spreadsheet.

5.

**Entering the data and assigning values to your responses.**The first important step to

complete when entering data is assigning numerical value to each response on your Likert

scale from your evaluation instrument. (A

**Likert scale**is used to rate each item on a

response scale. For instance, when parents complete pre and/or post tests about a workshop,

they are asked to answer each question by rating each item on a 1-to-5 response scale.)

a. For example, if your Likert scale includes the items: “strongly agree,” “agree,”

“neutral,” “disagree,” and “strongly disagree” – you could code responses to have

strongly agree = 5, agree = 4, neutral = 3, disagree =2 and strongly disagree = 1 so

that a higher score reflects a higher level of agreement of each item.

b. This is important because after you enter the individual scores, you will calculate

an average – or mean score for the whole group for each survey question. In the

case of assigning higher values to stronger agreement, then higher mean scores

for each question will translate into levels of agreement for each item, and thus,

lower scores will reflect participants’ disagreement with each item asked.

c. It is extremely important to note that how you interpret the level of agreement for

each survey question will depend on each item asked. For example, if your survey

states, “I am confident in choosing books that are appropriate for my children,”

then you would hope for a higher mean – closer to 4 or 5 which indicates that

respondents do feel confident. However, if you had phrased the question

differently, “I am NOT confident in choosing books that are appropriate for my

children,” then you would like to see a lower mean score, closer to 2 or 1 which

would reflect parental confidence.

2

6. After assigning a value to each response, you can

**enter the data accordingly into the**

**spreadsheet.**

*(Strongly agree = 5, agree = 4, neutral = 3, disagree =2 and strongly*

disagree = 1 so that a higher score reflects a higher level of agreement of each item).

disagree = 1 so that a higher score reflects a higher level of agreement of each item).

a. For example, if Participant #1 responds to question #1 with an “agree” response,

you would enter a 4 in that cell. It would look like this:

A

B

1 Participant Number Question 1

2

1

**4**

3

b. You can continue to enter the corresponding number for each response in each

cell of the spreadsheet. If your data was as follows:

i. Participant 1, Question 1, “agree” = 4

ii. Participant 1, Question 2, “disagree” = 2

iii. Participant 1, Question 3, “neutral” = 3

iv. Participant 2, Question 1, “strongly agree” = 5

v. Participant 2, Question 2, “agree” = 4

vi. Participant 2, Question 3, “strongly disagree” = 1

The spreadsheet entry would look like this:

A

B

C

D

1 Participant Number Question 1 Question 2 Question 3

2

1 4

2

3

3

2 5

4

1

4

7. Once you have filled in the spreadsheet you can

**calculate the mean score for each**

**question asked.**

8.

**Calculating the mean in Excel**for a survey administered ONE TIME is done by the

following steps:

a. Place the cursor in the box where you want the mean score to appear (see below

(X) for example).

A

B

C

D

1 Participant Number Question 1 Question 2 Question 3

2

1 4

2

3

3

2 5

4

1

4 Mean

Score

X

b. Go to the

*symbol1 and place the cursor inside the blank area to the right of the*

**fx**

*symbol. Type:*

**fx****=average(B2:B3)**in the blank area to the right of the symbol. If

you look at the table above, you will see that the score in column B row 2 = 4, and

in column B row 3 = 5. By calculating an average for column B, you will know

the mean for that item on the pre-test. Note that if you have more than two

respondents, you will need to include more numbers in your average. For

1 Depending in the version of Excel you use, the

*symbol will either be just above column C on your spreadsheet*

**fx**or next to the ? symbol on the top toolbar. Some versions use a pop up menu, in which you would select the

functional category “statistical” and then “average.”

3

example, if you have 12 respondents, with data in boxes B2 through B13, then

you would type

**average(B2:B13)**.

c. Once you type in the formula above, depress the enter key and the mean will

appear in the box you marked, and should look like this:

A

B

C

D

1 Participant Number Question 1 Question 2 Question 3

2 1

4 2 3

3 2

5 4 1

4 Mean

**4.5**

As you can see the mean score of question 1 for this sample of two participants is

4.5, which corresponds between “strongly agree” and “agree.” You could then

report that your mean score on Question 1

*before*your program was 4.5.

*If you are conducting a survey that is administered only once, you can compute*

and report the mean scores for each item on your survey. If you are using a pre

and post test, step 9 provides instruction for the remaining data entry and then

calculating and testing differences in means for each item at the two points in

time.

and report the mean scores for each item on your survey. If you are using a pre

and post test, step 9 provides instruction for the remaining data entry and then

calculating and testing differences in means for each item at the two points in

time.

9.

**Calculating the mean in Excel**for a survey administered TWICE is done by repeating

steps 1-8 to

**generate a spreadsheet and calculate means for your post test instruments**

if you have one. In many instances, it is possible to make one spreadsheet that contains

both pre and post test scores, which can be arranged as follows:

A

B

C

D

E

F

G

H

1 Participant

Question 1 Question 2

Question 3

Question

1 Question 2

Question 3

Number

PRE

PRE

PRE

POST

POST

POST

2 1

4

2

5 5

4

5

3 2

3

3

3 4

3

4

4

**Mean Score**

**3.5 2.5 4.0 4.5 3.5**

**4.5**

10. After you have calculated both pre and post test means for each item you can

**report the**

**changes in scores between the program’s beginning and end.**

a. For example, if your mean score on an item, “I am confident in choosing books

that are appropriate for my children,” on the pre-test was

**3.5**(between neutral and

“agree” on our scale), and a

**4.5**on the post test (between “agree” and “strongly

agree” on our scale); you could report that on average, participants in your

workshop increased 1.0 on that item, reflecting an increase in parent’s confidence

(one of your program goals).

11.

**The T-Test**. After calculating the means for both pre and post test scores, it is important to

test whether or not the differences in mean scores for each item are significant, rather than

due to chance or other circumstances. For our purposes, we can run a t-test using Excel to

determine the significance of the differences in means between the pre- and post-tests.

Note that the t-test tells us whether or not the difference in means for each question is

statistically significant among all program participants.

4

To begin the t-test, place the cursor in the box where you want the t-test result to appear (see

below (X) for example.)

A

B

C

D

E

F

G

1 Participant

Question 1 Question 1 Question 2 Question 2 Question 3

Question 3

Number

PRE

POST

PRE

POST

PRE

POST

2 1

4

5

4 4 5 5

3 2

3

4

2 3 3 4

4

**Mean Score**

3.5 4.5 3 3.5 4.0 4.5

5 T-test,

p-value

**X**

a. Then place your cursor on the

*symbol, a pop-up list will appear that provides a*

**fx**list of possible mathematical functions that Excel can perform. Under “select a

function” highlight TTEST, and depress your enter key. You will then be asked to

specify an array – or more simply the group of numbers you want to test. In this

case, array number 1 would correspond to the scores given on Question 1 PRE

test, columns B2:B3 on the table above. Therefore, you need to type B2:B3 in the

window to the right of “array 1” in the function box. The scores you are

comparing with are the POST test scores on that same question, columns C2:C3

above, this is your second array, so you need to type C2:C3 to the right of “array

2” in the function box. You then need to specify if you want a one or two tailed

distribution, in this case you need to enter “2” in the window next to the “tails”

box. Finally, because we are testing the same sample at two different time

intervals, we need to do a paired t-test – and thus, need to enter a “1” next to the

“type” window in the function box.

*(For more explanation of these statistical*

functions check Excel’s website athttp://office.microsoft.com/en-us/training.aspx

functions check Excel’s website at

*to access free tutorials.)*Note that if you have more than two respondents, you

will need to include more numbers in your average. For example, if you have 12

respondents, with data in boxes B2 through B13 for the pre-test, then you would

type

**B2:B13**for the pre-test array, and

**C2:C13**for the post-test array.

b. Once you have entered all the information in the function popup box, depress the

enter key and Excel will perform the test and generate a p-value which will appear

in the box you marked, and should look like this:

**A**

**B**

**C**

**D**

**E**

1

**Participant Question 1 Pre Question 1 Post Question 2 Pre Question 2 Post**

2

1

5 5 5 5

3

2

5 5 3 5

4

3

5 5 5 5

5

4

5 5 4

6

5

5 5 4 4

7

6

5 5 4 5

8

7

4 5 4 5

9

8

5 5 5 5

10

9

5 5 4 5

11

10

4 5 4

12

11

5 5 5 5

13

12

4 5 4 5

20

Mean

Score

4.78 4.97 4.24 4.88

21 Difference

0.19

0.64

22

**p-value**

**0.0488**

**0.0014**

5

c.

**Interpreting the p-value**. The p-value is a numerical estimate of the reliability of

our assumption that the difference in means on pre and post surveys is real and

not due to chance.

i. As you can see the p-value generated by a t-test for Question 1 is .0488,

and .0014 for Question 2.

ii. In general, researchers say that a p-value of .10 or less is statistically

significant, which means that we are 90% sure that the result we see (the

difference in means for each question) is not due to chance.

iii. Therefore, when reporting the results of your pre/post test on evaluation

surveys, you could report that a t-test confirms that the change on a given

item were “significant at a p<.10 level.”

**Summary**

This brief provides an overview of how Microsoft Excel can enable you to quantify the impact of

programs delivered in your community. At a minimum, Excel can compute the mean responses

to each item on your evaluation instrument, whether they are administered once or twice during

your program. This allows an educator to report the average response among program

participants. In addition, when both a pre and post test are completed, Excel can both calculate

the change in means among participants and test whether or not the differences in mean scores

for each item are significant, rather than due to chance.

There are many additional resources available that provide simple instructions on using

Microsoft Excel, many of which are free of charge:

Websites:

Microsoft Office Online Tutorials http://office.microsoft.com/en-us/training.aspx provides many

free tutorials.

http://www.exceltip.com/tutorial/index.html provides free tutorials.

http://www.videoprofessor.com sells tutorials for many different versions of Excel, at a nominal

charge ($6.95).

http://www.vtc.com/products/excel2000.htm sells more advanced tutorials.

Printed Materials:

• Mastering Excel 2000 (for beginner)

• Microsoft Excel Version 2002 Step by Step

• Excel 2002 For Dummies®

• Microsoft Excel 2002 Simply Visual

• Absolute Beginner's Guide to Microsoft Excel 2002

• Absolute Beginner's Guide to Microsoft Office Excel 2003

(All can be accessed through http://www.amazon.com or http://www.exceltip.com/bc-

Microsoft_Excel_books_for_Beginners,3 )

*Laura Colosi is an Extension Associate in*

the Department of Policy Analysis and

Management at Cornell University.

© 2005 Cornell Cooperative Extension

the Department of Policy Analysis and

Management at Cornell University.

6