Using Excel for Analyzing Survey Questionnaires - WCASA

1 IntroductionYou have created, tested, and implemented asurvey, and now you would like to see the resultsof your work. This guide will help you set up asimple database in Microsoft Excel 2002 toanalyze your Survey data. Some steps may beslightly different in other versions of MicrosoftExcel.* This guide is organized in five steps: Step 1 Create an Excel database, page 1 Step 2 Code your data,page 6 Step 3 Enter your data,page 7 Step 4 Clean your data,page 8 Step 5 Analyze your data, page 8 Resourceson page 28 include companion eval-uation materials available online. You may findthese helpful as you use this 1 Create an Excel databaseWhen you open up Microsoft Excel , you willsee a blank worksheet is part ofa workbook. Aworkbookholds all of yourworksheets, and is simply another name for anExcel blank Excel worksheet is composed of a seriesof vertical columns, horizontal rows, and individ-ual cells (see Figure 1).

2 You can select differentworksheets by clicking on the tabs at the bottomof your workbook. Columnsare alphabetized A, B, C, D .. from left to right across the top. Rows are numbered 1, 2, 3, 4 .. fromtop to bottom down the left of the worksheet. Cells are individual boxes within the Excel for Analyzing SurveyQuestionnairesJennifer LeahyG3658-14 Program development & EvaluationPDE&&University of Wisconsin-ExtensionCooperative ExtensionMadison, Wisconsin* 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 Excel for Analyzing Survey Questionnaires is an independent publication and is not affiliated with, nor has itbeen authorized, sponsored, or otherwise approved by Microsoft Corporation. Figure 1: Blank Excel worksheet*Create a title for your databaseTo begin creating your Excel database:Typethe Survey title in the first cell at Row 1, ColumnA ( Type your title here in Figure 2, Title ofsurvey in Figure 3).

3 Then move down two rows to Row 3, Column is where you will enter column headers labels to identify each question in your column headersThe first column is for the ID number of eachquestionnaire. This is called a unique the header ID # into the first cell in Row3, Column A, as shown in Figure development AND EVALUATION n n nRowColumnsCellWorksheet* Screen shots reprinted by permission from Microsoft is a unique identifier?Aunique identifieris an assigned number thatidentifies each questionnaire. When assigningunique identifiers, you may want to use incremen-tal ID numbers such as 001, 002, 003, this number on the corner of each papersurvey questionnaire, and enter that samenumber in the column labeled ID# followed bythe data for that questionnaire. Later, this willallow you to find a particular questionnaire orremove identifying elements from your , create column headers for each of thesurvey questions (see Figure 3).

4 Decide whichkind of header will work better for can label columns one of these ways: For narrow columns, use the number ofeach question Q1, Q2, Q3, Q4. Or use a descriptive headerthat encapsu-lates each question s meaning for instance,if a question asks Do you smoke? thecolumn header could be Smokes? Keep track of the header you give to each ques-tion. A good way to do this is to take a blankquestionnaire and write the header next to eachquestion. This is your creating column headers until all ques-tions are labeled. Each question header is enteredinto a separate column width and row heightYou can adjust column width to fit your headersand data. However, keep the columns as narrowas possible. This allows more columns to be seenat once and makes the database easier to column widthTo adjust column width:Move your cursorover the top of the column you want to stretching tool will appear ( ).

5 Click and hold the left mouse button over thestretching tool. Then drag this to adjust thecolumn size to be as wide or as narrow as column widthTo auto-format column width:You can changecolumn width automatically either of these ways: Double-click on the stretching tool. Or select (highlight) the entire column orcolumns you want to Excel FOR Analyzing Survey QUESTIONNAIRESn n nFigure 2: Create a database title and column headersFigure 3: Highlight a columnTo highlight (select) an entire column: Pointyour mouse over the letter at the top of the an arrow pointing down appears, click yourmouse to highlight the entire column (see Figure 3).Then go to the menu bar, and select Format>>Column>>AutoFitSelection (seeFigure 4).Figure 4: Format column widthThis will change each column width to fit thewidest label or value you entered in that you have open-ended questions on yoursurvey, see how to wrap text as shown in Figure5 and Figure row heightTo adjust row height: Move your cursor overthe row number on the far left.

6 The stretchingtoolwill appear ( ).Click and hold the left mouse button over thestretching tool. Then drag this to change the textIf you have open-ended questions on yoursurvey, you may need to format those columns inyour database to make room for the text helps you see all of the text in the cellregardless of the amount of narrative data. To wrap text:First, highlight the cells in whichyou want text to wrap. You may want to select anentire column to wrap text for every response toa highlighting the cells or column(s), go tothe menu bar and select Format>>Cells. Selectthe Alignment tab at the top of the windowthat opens. Under Text control, select the Wrap Text option (see Figure 5).Figure 5: Select Wrap Text 4 PROGRAM development AND EVALUATION n n nUsing Wrap Text will make your narrative data look like the text in Column E, Row 4 of Figure 6: Text after Using Wrap Text BordersNow that you have typed in all your column headers and formatted the columns, you may want todraw a line under the headers to distinguish them from the data underline headers:First, select and highlight Row 3 with all the column headers as shown inFigure 7: Select a row5 Using Excel FOR Analyzing Survey QUESTIONNAIRESn n nNext, from the menu bar, click on Format>>Cells.

7 Select the Border tab at thetop of the window that opens. To underline yourheaders, click on the bottom of the white box thatsays Text in the middle. This will create a lineunder your headers (see Figure 8).Figure 8: Underline headersYou have now created the framework for a data-base that should look something like Figure 2 Code your dataWhen you use a database, every response itemon the questionnaire needs to be entered as anumbered code(except narrative text). To dothis, assign numbers to your responses prior toentering your assigning numbers to response items in aquestion: Start with a small number such as 1 (but notzero). Or leave the cell blank if the question is unanswered. 6 PROGRAM development AND EVALUATION n n nWhat is a response item?A response item is one possible answer to yoursurvey question. In the first question on the nextpage Do you smoke? No and Yes arethe response 9: Sample database without data entered Sometimes, numbers already exist next to theresponses in your Survey .

8 These are your example:1. Do you smoke? (Please circle your response.)1 No } 1 = code for No 2 Yes } 2 = code for Yes Other times, there may be no numbers next to theresponses. Then you need to assign responsecode numbers before you enter the data. Forexample: 2. Do you think secondhand smoke is harmful?No 1 Unsure 2 Yes 3 If unanswered, leave cell blankThe response code may be the number a respon-dent gives. In this example you would enter thenumber 20 in your How many cigarettes do you smoke in a day? 20If a question is not answered, leave that cellblank. While assigning codes to your response items,note the codes in the same codebook you used todocument your column 3 Enter your dataBeginning with Row 4, enter the ID number, thenthe data for each questionnaire (see Step 2). Dothis by typing the code of the selected responsefor each question in the cell under that question sheader.

9 Be careful to enter each response entering your data, you may encountersome unexpected problems. Here are tips for pre-venting problems and dealing with common situ-ations that come up while entering data: If a question is not answered: Leave thecell for the unanswered question blank orcode it as no response. If the respondent missed an entire pageof the Survey :If possible, contact thatrespondent again to complete the Survey . Orleave the cells for unanswered questionsblank. If two responses are selected for a ques-tion when only one is requested: Thatinvalidates the data for that question. Treatthis question as if it were not answered. If someone circles data between twopoints on a scale:This can be tricky. If youare not sure of the response, treat the questionas if it were not answered. Or treat allresponses that lie between two points on ascale the same. For example, give them all theexact same number that is directly betweenthe two points, such as if the response sitssomewhere between 0 and 1, or round to thehigher or lower number.

10 The important thingis to treat all responses between two points ona scale the same way. Note this in your code-book. If an open-ended response is incomplete:Enter the data that are given. 7 Using Excel FOR Analyzing Survey QUESTIONNAIRESn n nMake backup copiesRemember to SAVE, SAVE, SAVE!!! Save your fileafter each record Using the keyboard command<ctrl-s>.After all your data are entered:Create at leastone backup copy of your file. This will be helpfulduring the analysis 4 Clean your dataAfter you have entered all your data, you willwant to clean your data to make sure they you have a small number of question-naires:Go through each paper Survey andcompare it with the data in your database tomake sure the two documents match. Checkingeach questionnaire is a best-case you have a large number of question-naires: Revisiting every entry may be too time-consuming. Carefully scan the worksheet forunusual entries such as a 14 when the responsesshould range from 1 to down the column of responses for eachquestion, and see if any entries look unusual.