Don’t just collect data – ask it a question (Using Excel Forms and Pivot Tables to conduct a meaningful survey)

This is another guest post from Peter De Lisle from Kwazulu Natal, one of our Microsoft's 2016/2017 MIEExperts from South Africa. Peter shared a post recenlty on using OneNote with the 16 habits where we focused on him in a spotlight on the South African MIEExperts.  In this post Peter shares on how to use Excel Forms and Pivot Tables to conduct a meaningful survey

Introduction
I am sure you have asked your students to conduct a survey at some time. It is a great way to get them to generate their own data, rather than relying on you or the Internet to provide stats. But, it can be a rather lower-order activity if all they do is collect data. They need to be working with it, trying to answer a question or solve a problem. So there needs to be some playing with the data once it is collected, aka manipulating variables. For example, students might want to ask the question: “Who uses what kind of social media?” If they get an answer, they can then be challenged to ask why that might be the case, to come with a theory to explain their findings.

This blog post describes a process whereby data is collected in Excel Online via a survey, then processed in Excel Desktop using Pivot Tables and Charts to allow students to see if the variables they have chosen do or do not have an effect. This is a cognitively powerful process – first it provides a way to take the raw, chaotic data of the world, and order it; then it facilitates visualising the patterns in the data, thereby making thinking visual.

Step 1: Set up a Survey

The first step is to use the Online version of Excel by logging into your Microsoft OneDrive account. Click on New, and choose Excel Survey.

:You can also access this feature from the Excel Online ribbon:

At this point, you will need to decide on what information you want to collect. What is very important is to collect Independent Variable (IV) and Dependent Variables (DV).

IV – information that defines who or what is being studied; eg as a researcher I have a hunch that gender and age play a part in determining what social media people use; so I would need gender and age as my IVs.

DV – this is information that will allow us to measure the effect of our IVs, eg frequency of social media use, and which platform. To make your life easier, choose categories for the age groups.

Here is an example of a survey I set up:

You can also access this feature from the Excel Online ribbon

To see my live survey, follow this link: http://bit.ly/2fDGJ6X . Once the survey is set up, get students to find participants by sharing the URL by whatever means they can: email, social media, QR Codes, etc. If it’s going to be difficult to get students to create and distribute an online survey, what I have also done is to set up a paper-based one for them on a piece of paper, with the IV column headings already specified, eg

Participant	Age Group	Gender	On a scale of 1 -5 rate…	What is your favourite…	??
1
2
3
4

 Once the data has been collected, it is time to start analysing it.

Step 2: Create a Pivot Table
If you have used an online survey, you need to open the sheet containing the data in Excel Online. From there you can click on “Open in Excel”. Once you have the data in Excel Desktop, it should look something like this:

Click anywhere inside the data, and then click Insert > Pivot Table, and then click OK. You will be faced with a new sheet, which looks like this:

To set up the Pivot:

• drag any of the column headings from the top box to the VALUES box (but not one that has numbers in it) because we have to count something
• drag one of the IVs (eg Gender) to the ROWS box – you will see it now breaks down the data by this IV.
• drag one of your DVs (eg Social Media Platform) to COLUMNS – this is the magic part! The data is now broken down two ways.

It should now look something like this:

So, we have an answer to our question. But it is quite hard to read the data in the table. So, we need to create a chart.

Step 3: Create a Pivot Chart

• Click anywhere in the Pivot Table, and then click on Insert > Column or Bar Chart.
• Select the 3-D 100% Stacked Column format.

The reason for this is that it then does not matter how many of each kind of participant there are – eg there may be more females than males. You should now see something like this:

 Encourage your students to tell the story that is shown in the chart; to answer the question why is it like this? If there seems to be no story, then ask then to try swopping one of the IVs for another, or swopping one of the DVs. In this way they can play with the data, and clearly see the relationships between IVs and DVs. You can try using two IVs at once, but this will only work well if you have a large number of participants, eg:

An interesting aside is that once the Pivot Table and Chart have been set up, they are available to be used in Excel Online – you just can’t create them there.

Conclusion
I think that you will agree that it would take a long time to collect data from hundreds of participants, type it in, and then work out formulas to extract this data; and it would not be interactive in a way which allows modelling and thinking

Get involved in the Microsoft MIEE program in 2017

If you are a teacher who likes to be innovative in the classroom, think about entering Microsoft's Innovative Teacher MIEExpert program in 2017 when applications reopen. You can learn more about the program at this link: http://bit.ly/1H4gKcB on the Microsoft Educator Community.