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


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

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

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


Age Group


On a scale of 1 -5 rate…

What is your favourite…






 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
  • 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


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:  on the Microsoft Educator Community.

« Return to Latest News

SchoolNet South Africa is an incorporated Non Profit Company - Registration 2001/012244/08, NPO Number 030-817
and holds Public Benefit Organisation (PBO) Status, in terms of Section 30 of the Income Tax Act - PBO Number 130003557.

Click here to support SchoolNet