Microsoft Excel functions banner image.

Previous step: 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
  4. Problems are opportunities

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. Ensure that each column only contains one piece of data.

For example, if the first “question” in your questionnaire is Name, create a column heading Name. Headings must be single words with no punctuation at all. 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.

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!

4. Problems are opportunities

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.