University of Wisconsin-Extension

Cooperative Extension
Madison, Wisconsin G3658-14

PD&E
Program Development
Using Excel
for Analyzing Survey
Questionnaires
Jennifer Leahy
& Evaluation

Introduction Step 1
You have created, tested, and implemented a
survey, and now you would like to see the results
Create an Excel
of your work. This guide will help you set up a database
simple database in Microsoft Excel 2002 to When you open up Microsoft Excel, you will
analyze your survey data. Some steps may be see a blank worksheet. This worksheet is part of
slightly different in other versions of Microsoft a workbook. A workbook holds all of your
Excel.* worksheets, and is simply another name for an
This guide is organized in five steps: Excel file.
Step 1 Create an Excel database, page 1 A blank Excel worksheet is composed of a series
of vertical columns, horizontal rows, and individ-
Step 2 Code your data, page 6
ual cells (see Figure 1). You can select different
Step 3 Enter your data, page 7 worksheets by clicking on the tabs at the bottom
Step 4 Clean your data, page 8 of your workbook.
Step 5 Analyze your data, page 8 Columns are alphabetized A, B, C, D
Resources on page 28 include companion eval- from left to right across the top.
uation materials available online. You may find Rows are numbered 1, 2, 3, 4 from
these helpful as you use this booklet. top to bottom down the left of the worksheet.
Cells are individual boxes within the work-
sheet.

* Using this product example is not intended to endorse it over others that may be similar, but rather as a convenience to
readers. Microsoft Excel is a registered trademark of Microsoft Corporation in the United States and other countries.
Using Excel for Analyzing Survey Questionnaires is an independent publication and is not affiliated with, nor has it
been authorized, sponsored, or otherwise approved by Microsoft Corporation.
2 P R O G R A M D E V E L O P M E N T A N D E V A L U A T I O N

Figure 1: Blank Excel worksheet*

Worksheet

Columns

Cell

Row

Create a title for your

database
To begin creating your Excel database: Type
the survey title in the first cell at Row 1, Column What is a unique identifier?
A (Type your title here in Figure 2, Title of A unique identifier is an assigned number that
survey in Figure 3). identifies each questionnaire. When assigning
Then move down two rows to Row 3, Column A. unique identifiers, you may want to use incremen-
This is where you will enter column headers tal ID numbers such as 001, 002, 003, 004.
labels to identify each question in your survey. Write this number on the corner of each paper
survey questionnaire, and enter that same
Create column headers number in the column labeled ID# followed by
The first column is for the ID number of each the data for that questionnaire. Later, this will
questionnaire. This is called a unique identifier. allow you to find a particular questionnaire or
Type the header ID # into the first cell in Row remove identifying elements from your database.
3, Column A, as shown in Figure 3.

* Screen shots reprinted by permission from Microsoft Corporation.

 U S I N G E X C E L F O R A N A L Y Z I N G S U R V E Y Q U E S T I O N N A I R E S 3

Next, create column headers for each of the Adjusting column width
survey questions (see Figure 3). Decide which and row height
kind of header will work better for you.
You can adjust column width to fit your headers
You can label columns one of these ways: and data. However, keep the columns as narrow
For narrow columns, use the number of as possible. This allows more columns to be seen
each question Q1, Q2, Q3, Q4. at once and makes the database easier to navi-
Or use a descriptive header that encapsu- gate.
lates each questions meaning for instance, Adjust column width
if a question asks Do you smoke? the To adjust column width: Move your cursor
column header could be Smokes? over the top of the column you want to adjust.
Keep track of the header you give to each ques- The stretching tool will appear ( ).
tion. A good way to do this is to take a blank Click and hold the left mouse button over the
questionnaire and write the header next to each stretching tool. Then drag this to adjust the
question. This is your codebook. column size to be as wide or as narrow as you
Continue creating column headers until all ques- desire.
tions are labeled. Each question header is entered
Auto-format column width
into a separate column.
To auto-format column width: You can change
column width automatically either of these ways:
Figure 2: Create a database title Double-click on the stretching tool.
and column headers
Or select (highlight) the entire column or
columns you want to adjust.

To highlight (select) an entire column: Point

your mouse over the letter at the top of the column.
When an arrow pointing down appears, click your
mouse to highlight the entire column (see Figure 3).

Figure 3: Highlight a column

4 P R O G R A M D E V E L O P M E N T A N D E V A L U A T I O N

Then go to the menu bar, and select

Format>>Column>>AutoFitSelection (see
Figure 4).
Figure 4: Format column width

This will change each column width to fit the After highlighting the cells or column(s), go to
widest label or value you entered in that column. the menu bar and select Format>>Cells. Select
If you have open-ended questions on your the Alignment tab at the top of the window
survey, see how to wrap text as shown in Figure that opens. Under Text control, select the
5 and Figure 6. Wrap Text option (see Figure 5).
Figure 5: Select Wrap Text
Adjust row height
To adjust row height: Move your cursor over
the row number on the far left. The stretching
tool will appear ( ).
Click and hold the left mouse button over the
stretching tool. Then drag this to change the row
height.

Wrap text
If you have open-ended questions on your
survey, you may need to format those columns in
your database to make room for the text.
Wrap text helps you see all of the text in the cell
regardless of the amount of narrative data.
To wrap text: First, highlight the cells in which
you want text to wrap. You may want to select an
entire column to wrap text for every response to
a question.
 U S I N G E X C E L F O R A N A L Y Z I N G S U R V E Y Q U E S T I O N N A I R E S 5

Using Wrap Text will make your narrative data look like the text in Column E, Row 4 of Figure 6.
Figure 6: Text after using Wrap Text

Borders
Now that you have typed in all your column headers and formatted the columns, you may want to
draw a line under the headers to distinguish them from the data cells.
To underline headers: First, select and highlight Row 3 with all the column headers as shown in
Figure 7.
Figure 7: Select a row
6 P R O G R A M D E V E L O P M E N T A N D E V A L U A T I O N

Next, from the menu bar, click on

Format>>Cells. Select the Border tab at the
Step 2 Code your data
When you use a database, every response item
top of the window that opens. To underline your
on the questionnaire needs to be entered as a
headers, click on the bottom of the white box that
numbered code (except narrative text). To do
says Text in the middle. This will create a line
this, assign numbers to your responses prior to
under your headers (see Figure 8).
entering your data.
Figure 8: Underline headers

What is a response item?

A response item is one possible answer to your
survey question. In the first question on the next
page Do you smoke? No and Yes are
the response items.

When assigning numbers to response items in a

question:
Start with a small number such as 1 (but not
zero).
Or leave the cell blank if the question is
unanswered.

You have now created the framework for a data-

base that should look something like Figure 9.

Figure 9: Sample database without data entered

 U S I N G E X C E L F O R A N A L Y Z I N G S U R V E Y Q U E S T I O N N A I R E S 7

Sometimes, numbers already exist next to the

responses in your survey. These are your codes.
Step 3
For example: Enter your data
1. Do you smoke? (Please circle your response.) Beginning with Row 4, enter the ID number, then
the data for each questionnaire (see Step 2). Do
1 No } 1 = code for No
this by typing the code of the selected response
2 Yes } 2 = code for Yes for each question in the cell under that questions
Other times, there may be no numbers next to the header. Be careful to enter each response
responses. Then you need to assign response accurately.
code numbers before you enter the data. For
example: Troubleshooting
2. Do you think secondhand smoke is harmful? While entering your data, you may encounter
No 1
some unexpected problems. Here are tips for pre-
venting problems and dealing with common situ-
Unsure 2 ations that come up while entering data:
Yes 3 If a question is not answered: Leave the
If unanswered, leave cell blank cell for the unanswered question blank or
The response code may be the number a respon- code it as no response.
dent gives. In this example you would enter the If the respondent missed an entire page
number 20 in your database. of the survey: If possible, contact that
3. How many cigarettes do you smoke in a day? respondent again to complete the survey. Or
20 leave the cells for unanswered questions
blank.
If a question is not answered, leave that cell
blank. If two responses are selected for a ques-
tion when only one is requested: That
While assigning codes to your response items,
invalidates the data for that question. Treat
note the codes in the same codebook you used to
this question as if it were not answered.
document your column headers.
If someone circles data between two
points on a scale: This can be tricky. If you
are not sure of the response, treat the question
as if it were not answered. Or treat all
responses that lie between two points on a
scale the same. For example, give them all the
exact same number that is directly between
the two points, such as 0.5 if the response sits
somewhere between 0 and 1, or round to the
higher or lower number. The important thing
is to treat all responses between two points on
a scale the same way. Note this in your code-
book.
If an open-ended response is incomplete:
Enter the data that are given.
8 P R O G R A M D E V E L O P M E N T A N D E V A L U A T I O N

Make backup copies

Remember to SAVE, SAVE, SAVE!!! Save your file
Step 5
after each record using the keyboard command Analyze your data
<ctrl-s>. Now that all the data are entered, you are ready
After all your data are entered: Create at least to analyze your data. Think about what you
one backup copy of your file. This will be helpful would like to do with your results:
during the analysis process. Who will read or use the data?
What do they want to know?
Step 4 What types of analyses will they want?
Clean your data What will be of most interest?
After you have entered all your data, you will Will you want charts or graphs to illustrate
want to clean your data to make sure they are your findings?
accurate. Use the answers to these questions to determine
If you have a small number of question- what analyses will meet your needs.
naires: Go through each paper survey and The following provides help to easily calculate
compare it with the data in your database to basic analyses in Excel frequencies and
make sure the two documents match. Checking percents; means, modes and medians; ranges,
each questionnaire is a best-case scenario. standard deviations, and variance.
If you have a large number of question-
naires: Revisiting every entry may be too time- Frequencies and percents
consuming. Carefully scan the worksheet for A frequency tells you how many times some-
unusual entries such as a 14 when the responses thing occurred. A frequency table displays fre-
should range from 1 to 4. quencies and/or percents of the response items
Look down the column of responses for each for one question (see Figure 10).
question, and see if any entries look unusual. If
they do, go back to the original questionnaire and
check to make sure the data are accurate.
You can also check your data entries
Figure 10: Frequency table for Q1 Do you smoke?
in Excel by running frequencies on
your data using the PivotTable
feature. Creating frequencies using
PivotTables easily and accurately
identifies responses that do not
belong in your data. You will read
more on PivotTables in the next step,
Analyze your data.
 U S I N G E X C E L F O R A N A L Y Z I N G S U R V E Y Q U E S T I O N N A I R E S 9

Excel gives you two ways to create frequencies: To select a different worksheet: Click on the
COUNTIF function You type in formulas that appropriate tab at the bottom left-hand corner of
calculate frequencies. your worksheet (see Figure 12).

PivotTable Wizard Use the PivotTable

Rename and add worksheets
Wizard to create frequencies that are
automatically formatted into a table. By default, Excel provides Sheet1, Sheet2
and Sheet3. You can rename these tabs and add
COUNTIF new worksheets:
The Excel COUNTIF function is a formula
To rename the tab: Right-click over the tab you
inserted into a cell or the formula bar. The
want to change, then select Rename.
formula bar displays the information from the
active cell and vice versa. Formulas may be To add a new blank worksheet: Right-click
entered directly into a cell or the formula bar. But over the tab you want to change, then select
when using the formula bar, the appropriate cell Insert, then Worksheet.
must be selected first.
The formula bar is located under the buttons at
the top of your Excel window (see Figure 11).
Figure 11: Formula bar

COUNTIF calculates frequencies for each

response item. For instance, if you asked Do you
smoke? and 1 = No and 2 = Yes, you would
need to use a separate COUNTIF function for
each response item, 1 and 2.
Before you enter your COUNTIF formulas, create
an empty table in a separate worksheet.
Creating an empty frequency table
Create an empty table in a worksheet that does
not contain your data, but is in the same Excel
workbook.
10 P R O G R A M D E V E L O P M E N T A N D E V A L U A T I O N

Once you have selected the new sheet,

create your table (see Figure 12). Enter:
1. A title for your frequency table.
2. The name of the question you are analyzing.
3. The header Code and the codes for the
question.
4. The header Response Item and the label for
each code you listed. Type Total below the
response items.
5. Headers for Frequency and Percent.

Figure 12: Frequency table created in a new sheet in the same workbook

1. A title for your frequency table.

2. The name of the question you are

analyzing.

5. Headers for Frequency and Percent

3. The header code

and the codes for the 4. The header Response Item and the label for each code
question. you listed. Type Total below the response items.

These tabs allow you to navigate among

worksheets.
 U S I N G E X C E L F O R A N A L Y Z I N G S U R V E Y Q U E S T I O N N A I R E S 11

Calculating frequencies using COUNTIF

Now that you have created an empty table in a separate worksheet, you can enter the COUNTIF
formula.
First, start in the appropriate cell of your table (see Figure 13).

Figure 13: Select a blank cell in your table

To calculate the frequency for a question in Excel: Click on the function key that you see
next to the formula bar. Make sure the blank cell you originally selected is still highlighted by a thick
black outline. The Insert Function window will pop up as shown in Figure 14.

Figure 14: Select COUNTIF in the Insert Function window

Under Select a function: Select

COUNTIF
If COUNTIF does not appear when the
window opens: Type COUNTIF in the
Search for a function field, and click
Go. COUNTIF will appear. Select
COUNTIF, then click on OK (see Figure
14).
In the next window, you will be asked to
choose a Range and Criteria (see
Figure 15).
12 P R O G R A M D E V E L O P M E N T A N D E V A L U A T I O N

Figure 15: Choose Range and Criteria in the Function Arguments window

Range is the group of cells that contain the data you want to analyze.
To select a range: First, make sure your cursor is in the range field. Then go to your data work-
sheet, and use your mouse or keystrokes to highlight the data you want analyzed (see Figure 16).
To highlight data: Place your cursor in the top data cell of the data column for the question, and use
your mouse or keyboard as follows:
Hold down the mouse button and drag the cursor to the bottom cell of the data column.
Or hold down the shift key while using the down arrow key.
Or select data using cell labels.
A dotted line appears around the data you select.

Figure 16: Highlight the range of data

Select data using cell labels:

In the blank cell you chose, type
the column letter and row
number of the first data point
in the column for the question
you want to average B4 for
Q1 in this database. Then type a
colon : to separate the data
points. Finally, type the column
letter and row number for the
Dotted line appears when last data point in the range
selecting a range of data. B31 in this database.
So in this database, you could
have typed B4:B31 in the blank
cell you chose, instead of high-
lighting the data with your
mouse or keystrokes. Either
way, a dotted line appears
around the data you select.
 U S I N G E X C E L F O R A N A L Y Z I N G S U R V E Y Q U E S T I O N N A I R E S 13

In Excel, criteria means the code of the specific response item for which you want frequencies.
If you asked Do you smoke? and codes are 1 = No and 2 = Yes, to count the number of people who
answered No, criteria would be response code number 1.
To select criteria: In the Criteria field, type the code for the response item. Then click OK (see
Figure 17).
Figure 17: Type the criteria (response code)

After you have successfully created a COUNTIF formula, a number will appear in the cell. This is the
frequency.
For a completed frequency table, see Figure 8 below.

Percents
Using the frequency table you created with COUNTIF, you can create a table that shows percents for
your data. See Figure 19 for a completed table with percents.
To create percents using formulas in Excel:
First, create a frequency table for all possible response items by using the COUNTIF function. Then
calculate the total number of responses by summing all of the frequencies in your table.
To calculate percents: In a separate column, enter a formula to divide the frequency for each
response item by the total number of responses. For example, to calculate the percent of total Q1
respondents (28) who answered No (16), enter the formula =16/28 in the Percent column (see
Figure 18).

Figure 18: Formulas to calculate percents

14 P R O G R A M D E V E L O P M E N T A N D E V A L U A T I O N

After you enter the formula into a cell, a figure will appear. This figure will begin with a decimal
point.
To make the resulting decimal look like a percent: Select the cells containing your intended
percents, then hit the % button located below the menu bar (see Figure 19).
Figure 19: Frequency table with percents

The % button

If you want, you can then delete the Frequency column by highlighting the cells you no longer
need in the Figure 19 example, cells C5 through C8. Then right-click over the highlighted cells and
select Delete. When prompted, select Move cells to the left to complete the table.

PivotTable frequencies
You can use the PivotTable Wizard in Excel to calculate frequencies and percents. Instead of creating
a formula for each response item, the PivotTable Wizard immediately displays in a table all of the
possible frequencies for your question.
To calculate frequencies using the PivotTable Wizard: Click on Data in the menu bar. Then
click on Create a PivotTable report or PivotChart report.
A window appears as in Figure 20. In this window, click on Microsoft list or database and
PivotTable. Then select Next >.
Figure 20: Create frequencies using PivotTable Wizard
 U S I N G E X C E L F O R A N A L Y Z I N G S U R V E Y Q U E S T I O N N A I R E S 15

In the next screen (Figure 21), the PivotTable Wizard asks you to choose the data you want to use.
Begin with the cursor in the range field, and highlight all of your data or selected questions. Include
the question headers (see Figure 21). After you have selected your data, click Next >.

Figure 21: Select your data

In the next screen (Figure 22), choose New worksheet. Then click Finish >.
Figure 22: Choose New worksheet
16 P R O G R A M D E V E L O P M E N T A N D E V A L U A T I O N

Excel then takes you to a screen that looks like Figure 23.
Figure 23: PivotTable row and data fields

Choose the question for which you want to create frequencies in the PivotTable Field List. Drag the
question header from the PivotTable Field List to the Drop Row Fields Here box in the
PivotTable (see Figure 24).
Figure 24: Drag the question header into the Drop Row Fields Here box

After you have dragged the question header to the Drop Row Fields Here box, the response items
will appear in the PivotTable.
 U S I N G E X C E L F O R A N A L Y Z I N G S U R V E Y Q U E S T I O N N A I R E S 17

To make data appear in the PivotTable: Select your question from the PivotTable Field List
again. This time, drag the question into the field that says Drop Data Items Here (see Figure 25).
Figure 25: Drag the question header into the Drop Data Items Here box

You now have a PivotTable that lists the response items for the question you chose, and the response
items frequencies (see Figure 26).
Figure 26: Frequency PivotTable for Q1 Do you smoke?

Double-click here if it
does not say count.

When checking over your frequencies, make sure that the top left cell in the table says Count of [the
question you chose] (see Figure 26). If not, double-click on that cell.
This takes you to the PivotTable Field window. Go to the field below Summarize by: and select
Count (see Figure 27) to make sure you have frequencies.
Figure 27: Select Count
in the PivotTable Field window
18 P R O G R A M D E V E L O P M E N T A N D E V A L U A T I O N

Inserting descriptive labels Figure 29: Create PivotTable percents

After you have created a frequency PivotTable,
you may want to replace the response item codes
with descriptive labels.
To insert descriptive labels: Click on the cell
that holds the code. Then type in the label you
prefer. In Figure 28, the descriptive label Doesnt
smoke replaces code 1, and Smokes replaces
code 2.
Figure 28: Descriptive labels for
example Q1 Do you smoke?

Percents
To create a PivotTable with percents: First,
create a PivotTable with frequencies (see direc-
tions on pages ***-***). After you complete the Show percents as whole
frequency PivotTable: Double-click on the top numbers
left cell in your PivotTable that says Count of To round percents to the nearest whole
[question you chose]. number: In the PivotTable Field window,
In the PivotTable Field window, click on the choose the Number button, then choose %.
button that says Options>>. An extension From the field Decimal places, select the
appears. In the drop-down menu Show data number 0 to round to the nearest whole number.
as: select % of total (see Figure 29).
Click on OK in both windows, and your
PivotTable will show percents as in the Figure 30
example.
You will also round percents for PivotTable cross
tabs (Figure 38 and Figure 39 on page 27).
You can also use this process to round percents in
cross tabs created with the Pivot Table Wizard
(Figure 38 and figure 39 on page 27).
 U S I N G E X C E L F O R A N A L Y Z I N G S U R V E Y Q U E S T I O N N A I R E S 19

Figure 30: PivotTable percents

Where should you place your formula? You
may choose the cell at the bottom of the data
column (see Step 1 below). Or you may choose
a cell in a worksheet separate from your data
worksheet in the same workbook.
Either way, make sure you create an adjacent
label that clearly articulates:
the type of formula you use, and
the question for which you select the data
Measures of central tendency
to analyze.
Measures of central tendency include means,
After you have determined what data you are
modes and medians. You can read more about
using and where you are going to place the
when to use each of these in the UW-Extension
booklet Analyzing Quantitative Data (see
mean, type the label and move on to calculat-
ing the mean.
Resources on page ***).
Mean To calculate a mean for a question in Excel:
To calculate a mean in Excel, use the AVERAGE Either follow the directions for Using the func-
function. tion Key on page 21, or type the formula into
First, identify the data you want to average, and the formula bar in the next four steps.
where you want the mean to appear.
Entering a formula
Do you want to calculate a mean, a mode or a
median? A range or standard deviation? You can
follow the Entering a formula directions on
pages 19-21 to calculate any of these measures.
1. Start in a blank cell.
20 P R O G R A M D E V E L O P M E N T A N D E V A L U A T I O N

2. While the blank cell is selected, type =AVERAGE( in the formula bar.
You can also type this into the blank cell, since the formula bar displays information typed in the cell
you selected, and vice versa.

you can type your formula into

the cell or the formula bar.
While this cell is
selected

3. Select a data range for the question you want to average.

See page xx for how to
highlight data.
 U S I N G E X C E L F O R A N A L Y Z I N G S U R V E Y Q U E S T I O N N A I R E S 21

4. Close the formula by closing the parentheses.

Type ) and then hit the Enter key (Return).

The mean for the question you chose will appear Using the function key
in the cell where you entered your formula. In
To calculate a mean using the function key
the step 4 example, the mean is 1.43.
in Excel: Click on the function key. After
To round to a certain decimal point: You can you click on this key, the Insert function
use the button to round up by a decimal window appears. In this window, select
point. This button is located on the standard AVERAGE from the Select a function field,
toolbar. Click on the button as many times as nec- then click OK.
essary to round to the decimal point you desire.
After you click OK, the Function Arguments
window will appear. Select a range of data in this
Using the function key window (see Figure 31). You only need to select a
Do you want to calculate a mean, a mode, or a data range for the Number1 field, even though
median? A range or standard deviation? You can Excel gives you two fields for entering a range.
follow the Using the function key directions
If the Function Arguments window blocks
on this page to calculate any of these measures.
the data you want to select: Click on the ( )
button next to the Number1 field. Clicking this
button will collapse that window so only the
Number1 field appears while you select data.
After you finish selecting your data, click on the
( ) button again to make the rest of the
window reappear.

If AVERAGE does not appear in the

Select a Function field type AVERAGE
in the Search for a Function field.
22 P R O G R A M D E V E L O P M E N T A N D E V A L U A T I O N

Figure 31: Select a data range

After you have selected your data: Click Median

OK. The cell where you inserted the formula A median is the middle number for your ques-
should show the mean for the data you selected. tion an equal number fall above and below
this number.
Mode
A mode is the most commonly occurring answer For example, in the sorted string of numbers
to a question. For example, in the string of 1,2,2,2,3,3,4,4,5,5,5 the median is the number 3.
numbers 1,1,1,2,2,2,2,2,3,3 the mode would be
the number 2.
To calculate a mode for a question in Excel:
1. Start in a blank cell.
2. While the blank cell is selected, type =MODE(
in the formula bar.
3. Select a data range for the question you want.
4. Close the formula by typing ) and then Enter
(Return).
Or, you can use the function key . Select
MODE from the drop-down field in the Insert
function window. All other steps stay the same
as for Using the function key on page 21.
 U S I N G E X C E L F O R A N A L Y Z I N G S U R V E Y Q U E S T I O N N A I R E S 23

To calculate a median for a question in Excel: Or, you can use the function key to calculate
1. Start in a blank cell. a range. Select MIN (and later MAX) from the
drop-down field in the Insert function window.
2. Type =MEDIAN ( in the formula bar.
All other steps stay the same as for Using the
3. Select a data range for the question you want. function key on page 21.
4. Close the formula by typing ) and then Enter Standard deviation
(Return). A standard deviation demonstrates the degree
Or, you can use the function key . Select to which individual answers vary from the mean.
MEDIAN from the drop-down field in the Insert To calculate the standard deviation for a
function window. All other steps stay the same question in Excel:
as for Using the function key on page 21.
1. Start in a blank cell.
Measures of variability 2. While the blank cell is selected, type
Measures of variability express the spread or =STDEVP( in the formula bar.
variation in responses. Measures of variability 3. Select a data range for the question you want.
include range, standard deviation and variance. 4. Close the formula by typing a ) and then
Range Enter (Return).
A range shows you the minimum and maximum Or, you can use the function key . Select
answers to a question. For example, in the sorted STDEVP from the drop-down field in the Insert
string of numbers 1,1,2,2,3,4,4,5 the range functions window. All other steps stay the same
would be expressed as (1, 5). Ranges are useful as for Using the function key on page 21.
with questions such as timeframes, but not with
Variance
categorical data.
The variance is sometimes used instead of a
standard deviation, and has the same purpose as
What are categorical data? a standard deviation. Variance is expressed as
Categorical data come from a question that (Standard deviation)2.
has a limited number of response items. To calculate the variance in Excel:
For example, from the question Do you smoke? 1. Start in a blank cell.
the responses 1 No or 2 Yes are categorical 2. Type =STDEVP( in the formula bar.
data.
3. Select a data range for the question you want.
4. Close parentheses with a ) to close your data
To create a range in Excel: You will need to
range.
run two separate formulas on your data to obtain
the minimum and maximum answers: 5. Type ^2 to complete the formula, then Enter
(Return).
1. To calculate the minimum for the ques-
tion: Start in a blank cell and type =MIN( in
the formula bar. Then select the data range.
Cross tabulations
Close parentheses with ) to close the formula. Cross tabulations (cross tabs) provide effec-
tive means to compare the results of two survey
2. To calculate the maximum for the ques-
questions. In Excel, use the PivotTable Wizard to
tion: Start in a blank cell and type =MAX( in
create cross tabs.
the formula bar. Then select the data range.
Close parentheses with ) to close the formula. To create cross tabs using PivotTable Wizard:
Click on Data in the menu bar. Then click on
You then have the low and high values for your
Create a PivotTable or PivotChart report.
question. This is a range, which you can write
out as (MIN, MAX). The first window appears (see Figure 32). In this
window, click on Microsoft list or database and
PivotTable. Then click on the Next > button.
24 P R O G R A M D E V E L O P M E N T A N D E V A L U A T I O N

Figure 32: First window of the PivotTable Wizard

In the second window, the PivotTable Wizard asks you to choose the data you want to use. Begin
with the cursor in the range field, then highlight all of your data or just the data for the questions you
want to analyze. Include question headers, but not the title. Make sure not to include any calculations
that are in your database. After you have selected your data, click Next > (Figure 33).
Figure 33: Select your data

In the third PivotTable Wizard window, choose New worksheet, then click Finish. A blank Pivot
Table will appear.
 U S I N G E X C E L F O R A N A L Y Z I N G S U R V E Y Q U E S T I O N N A I R E S 25

Next, determine which questions to To create a cross tab for this example (see
compare: Figure 34):
One question is an independent variable. 1. Select the independent variable header (Q1)
The question to compare is a dependent from the PivotTable Field List, and drag it
variable. to the PivotTable Drop Row Fields Here
box.
For example, you may want to compare the age
of your respondents to their attitudes about 2. Select the dependent variable header (Q2)
smoking. from the PivotTable Field List, and drag it
to the PivotTable Drop Column Fields Here
In this example, age is the independent vari-
box.
able. The independent variable is expected to
affect the dependent variable in this case, 3. Select the dependent variable header (Q2)
the respondents attitude toward smoking. again from the PivotTable Field List, and
drag it to the Drop Data Items Here area in
the blank PivotTable report.

Figure 34: Create a cross tab

Your cross tab will look like Figure 35.

Independent variable (Q1)

Dependent variable (Q2)
26 P R O G R A M D E V E L O P M E N T A N D E V A L U A T I O N

Figure 35: Example of a cross tab

Percents in
PivotTable cross tabs
To create a PivotTable cross tab with per-
cents: First, create a cross tab PivotTable (see
directions on pages 23-25). After you complete
the PivotTable cross tab, double-click on the top
left cell in your table that says Count of [ques-
tion you chose].
In the PivotTable Field window, click on the
Options>> button. An extension appears.
Checking your cross tab Under Show data as: select the % you would
When checking over your cross tab, make sure like to use (see Figure 37).
that the top left cell in the table says Count of [the Figure 37: Select the % you want in
question you selected] (see Figure 34). If not , your cross tab
double-click on that cell.
This takes you to the PivotTable Field window.
Go to the field below Summarize by: and select
Count, then click OK. This assures that your
table contains frequencies.

To insert descriptive labels: You may want to

replace the numbered codes in your cross tab
with descriptive labels. To do so, click in the cell
that holds the numbered code. Then type in the
label you prefer.
In Figure 36, the descriptive label Doesnt
smoke replaces Q1 response code 1, and
Smokes replaces response code 2. For Q2, the
descriptive labels Not harmful, Unsure, and
Harmful replace response codes 1, 2, and 3.

]
Figure 36: Insert descriptive labels
Which do you use?
The two percents most
commonly used in cross
tabs are:
1. % of row
2 % of column
Read why on the next
page.
 U S I N G E X C E L F O R A N A L Y Z I N G S U R V E Y Q U E S T I O N N A I R E S 27

Most common percets used in cross tabs 2. % of column

1. % of row The % of column illustrates the percent of
The % of row illustrates the percent of answers answers per column item.
per row item. In Figure 39, % of column shows what percent of
In Figure 38, % of row shows what percent of those who answered Do you think secondhand
those who fit the description Doesnt smoke or smoke is harmful? (Q2) as Not harmful,
Smokes (Q1 responses) answered the question Unsure or Harmful also fit the description
Do you think secondhand smoke is harmful? Doesnt smoke or Smokes in response to Do
(Q2) as Not harmful, Unsure and Harmful. you smoke? (Q1).
In the Figure 38 example, of the respondents who In the Figure 39 example, of the respondents who
answered that they do not smoke: answered that they think secondhand smoke is
6 percent thought secondhand smoke is not not harmful,
harmful. 20 percent do not smoke.
25 percent were unsure. 80 percent do smoke.
69 percent thought secondhand smoke is
harmful.
Figure 38: % of row for Do you
smoke? (Q1) with Do you think
secondhand smoke is harmful? (Q2)

Figure 39: % of column for Do you think

secondhand smoke is harmful? (Q2) with
Do you smoke? (Q1)

To show percents as whole numbers:

See the directions for frequency PivotTables
(Figure 30) on page 18-19.
28 P R O G R A M D E V E L O P M E N T A N D E V A L U A T I O N

Closing comments Tips on survey analysis and

These instructions are designed to help you enter data management
your data and run simple analysis with Microsoft Quick Tips 15: Dont Average Words How to
Excel.* analyze categorical data
For more extensive statistical analysis, consider Quick Tips 22: Make Certain Your Electronic Data
software packages such as SPSS or SAS. Are Accurate Cleaning your data
www.uwex.edu/ces/pdande/resources/quick-
Resources tipsnumerical.html

Evaluation materials Tips for using Microsoft Excel

This publication is one in a series of program Tips for Using Excel, 1 page
Tips for Viewing Data Using Excel, 2 pages
evaluation guides designed to help extension
educators better plan and implement credible
and useful evaluations. These also may be useful Includes information on filtering data in Excel.
to agencies or funders seeking realistic evalua- Choose Tips for using the AutoFilter function in
tion strategies. Excel from the web menu.

This guide refers you to companion evaluation www.uwex.edu/ces/tobaccoeval/analyzing-

publications in this series. You may find these data.html
helpful as you work through this booklet:
Analyzing Qualitative Data G3658-12, 12 pages
Microsoft references*
Analyzing Data with Microsoft Excel 2002 by
Analyzing Quantitative Data G3658-6, 6 pages Microsoft Overview of analysis in Excel
These and other evaluation materials are avail- www.microsoft.com/education/?ID=Excel2002
able online: www.uwex.edu/ces/pdande/ Tutorial
evaluation/evaldocs.html
Excel General information and assistance
office.microsoft.com/home/office.aspx?assetid=
FX01085800&CTT=6&Origin=ES790020011033

* Using this product example is not intended to endorse it over others that may be similar, but rather as a convenience to
readers. Microsoft Excel is a registered trademark of Microsoft Corporation in the United States and other countries.
Using Excel for Analyzing Survey Questionnaires is an independent publication and is not affiliated with, nor has it
been authorized, sponsored, or otherwise approved by Microsoft Corporation.

Copyright 2004 by the Board of Regents of the University of Wisconsin System doing business as the divi-
sion of Cooperative Extension of the University of Wisconsin-Extension. All rights reserved. Send copyright
inquiries to: Manager, Cooperative Extension Publishing, 432 N. Lake St. Rm. 103, Madison WI 53706
Author: Jennifer Leahy, Program Development and Evaluation, University of Wisconsin-Extension. The author
wishes to thank the following UW-Extension faculty and staff for their careful reviews and useful contributions
to this publication: Ellen Taylor-Powell, Susan Anderson, Heather Boyd, Rhonda Lee and Lisa Linfield.
Produced by Cooperative Extension Publishing, University of Wisconsin-Extension.
An EEO/AA employer, University of Wisconsin-Extension Cooperative Extension provides equal opportunities
in employment and programming, including Title IX and Americans with Disabilities Act (ADA) requirements.
This publication is available from your Wisconsin county UW-Extension office or from Cooperative Extension
Publishing. To order, call toll-free: 1-877-WIS-PUBS (877-947-7827)
Or visit our web site: http://cecommerce.uwex.edu

Using Excel for Analyzing Survey Questionnaires (G3658-14) I-8-04