How to use Google Forms and Excel for surveys
If you're a sociologist, a homeroom teacher, a marketer, or even an analyst, you should know this.
Online surveys have become a trivial task.
Using Google Forms, you can easily create a survey of any complexity.
Survey results can be easily viewed in the forms themselves or exported to Google Sheets.
But what if the survey is large and processing the results requires complex logic?
Now it's easy. You just need to integrate Google Sheets with Excel.
This is done using Power Query.
This video demonstrates a survey based on the methodology of:
P.V. Stepanov, D.V. Grigoryev, I.V. Stepanova
TO THE CLASSROOM TEACHER
ON ASSESSING THE EDUCATIONAL PROCESS IN THE CLASSROOM
A full description of the methodology is available here: https://docs.google.com/document/d/1R...
Anyone can take this methodology and, following the steps in this video, create a similar tool for their own educational institution.
Or you can create any custom survey or form and integrate the data with Excel or even Power BI using Google Sheets and Power Query.
To make it easier to navigate, here are the time codes:
00:00 Introduction
01:12 Survey Methodology
02:30 Creating a Google Form for the Survey
03:20 Creating the Title Section
04:40 Adding a Question Section
05:50 Creating the First Question
06:40 Preparing Questions for Copying
07:40 Copying Questions
12:50 Dividing Questions into Sections
13:37 Saving and Submitting the Form
14:08 Filling Out the Survey with Sample Data
14:50 Ways to View Survey Results
15:20 Integrating Survey Results with Google Sheets
16:10 Integrating Google Sheets with Excel
17:35 A Clever Trick for Loading a Table into Power Query
19:00 Transforming Data in Power Query
22:32 Creating Question Indexes
24:10 Transferring Data from Power Query to Excel
24:55 Transcript Interpreting survey results
27:00 Merging tables with responses and decryption in Power Query
30:50 Expanding survey results in a pivot table
32:08 When Power Pivot refuses to summarize fields
34:00 Finalizing the summary table
36:00 Parting words and options for further interaction
Link to Google Form: https://forms.gle/vT91VauD2tMUs7C48
Link to Excel file: https://docs.google.com/spreadsheets/...