Microsoft Excel formulas menu

Once you have administered your questionnaire to 25 respondents, you must capture and analyze the data. You can create the Excel spreadsheet ahead of time as you can plan according to the data as it will appear on your questionnaire.

Create an Excel workbook in your Phase 2 folder. Rename the first worksheet to: Data.

Add a column with the heading “No” and create a list of row headings in column A: 1, 2, 3, etc. up to the number of completed questionnaires you have (25 for Grade 12), then add column headings to row 1 by creating a heading for each question or piece of information you collected on your questionnaire.

For example, if the first “question” is Name, create a column heading Name. You will most likely end up with a set of headings for your biographical questions as follows: Name, Surname, Gender, Age, Race, Occupation, Suburb. Select your headings, apply bold formatting, and center the content of the cells. Select cell B2 and use the Freeze Panes command to freeze the column and row headings. Do NO other formatting on this sheet!

Top Tip: keep your column headings short on your raw data sheet (the sheet on which you first capture your data) and do not use punctuation or special characters – this is important for when you import your data to Access because your column headings are going to become your field names in your table.

Next, you must create headings for your topic questions. This might be slightly more complex. If you asked a simple closed question where the respondent was required to enter or select a single value answer, the one column will be enough.

However, if you asked a closed question where the respondent could enter multiple answers, you must accommodate these answers correctly.

Let’s use the example of a closed question which allows a respondent to select their 3 favourite flavours of ice-cream. You will then need 3 columns to record each of the choices (entering 3 flavours in one column will create challenges when analysing the data).

Have you already captured the data in one column? No problem – we have a solution for that too: the Text to Columns!

Next step: Excel analysis.

By MisterFoxOnline

CAT Educator

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.