Microsoft Excel formulas menu

Previous post: Phase 2 Questionnaire

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

In this post:

  1. Workbook set-up
  2. Biographical questions section
  3. Topic Questions section

1. Workbook set-up

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).

2. Biographical questions section

Next, 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” in your questionnaire 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 the cells containing your headings, apply bold formatting, and centre the content of the cells. Select cell A1, use the CNTRL+* keyboard combination shortcut to select the range of data and use the Borders button to add All Borders. 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.

3. Topic questions section

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, then one column will be enough. For example, if you asked a Yes/No question like “Do you like ice-cream?” a single column will be enough to capture “Y” for yes and “N” for no.

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 command!

You will have to capture the full text of your open question and you will be super glad now if you took my advice and only included one of them in your questionnaire — or if you used an electronic form!

If you made any errors along the way there is always a solution to make adjustments and corrections. For example, you could use Excel’s TRIM and CLEAN functions to clean your data as described in the post Cleaning data in Excel. Keep these “adjustments” as rough work: the steps you take to correct something demonstrate the learning you have done — and that is what the PAT is all about!

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.