Google Forms to Sheets in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are an event coordinator at a community center.

📋 Request: Your manager wants you to collect and analyze participant registrations for an upcoming workshop using Google Forms and Google Sheets.

📊 Data: You have a Google Form that collects participant name, email, age group, and preferred workshop session. The form responses are automatically recorded in a Google Sheet.

🎯 Deliverable: Create a Google Sheet that organizes the form responses, calculates the total number of participants per session, and shows the percentage of participants in each age group.

Sample Data

Timestamp	Name	Email	Age Group	Workshop Session
2024-06-01 09:15:00	Alice Smith	[e-mail]	18-25	Morning
2024-06-01 09:20:00	Bob Jones	[e-mail]	26-35	Afternoon
2024-06-01 09:25:00	Carol Lee	[e-mail]	18-25	Morning
2024-06-01 09:30:00	David Kim	[e-mail]	36-45	Evening
2024-06-01 09:35:00	Eva Green	[e-mail]	26-35	Afternoon
2024-06-01 09:40:00	Frank White	[e-mail]	18-25	Morning
2024-06-01 09:45:00	Grace Black	[e-mail]	46-55	Evening
2024-06-01 09:50:00	Henry Brown	[e-mail]	26-35	Afternoon

1

Step 1: Open the Google Sheet linked to the Google Form to see all participant responses organized in columns: Timestamp, Name, Email, Age Group, Workshop Session.

No formula needed; data is auto-populated by Google Forms.

Expected Result

All form responses appear in the sheet starting from row 2.

2

Step 2: Create a summary table to count how many participants registered for each Workshop Session.

In a new area, list sessions: Morning, Afternoon, Evening. Next to each, use formula: =COUNTIF(E2:E9, "Morning") (adjust session name accordingly).

Expected Result

Morning: 3, Afternoon: 3, Evening: 2

3

Step 3: Calculate the total number of participants to use for percentage calculations.

=COUNTA(B2:B9)

Expected Result

8

4

Step 4: Create a summary table to count participants in each Age Group.

List age groups: 18-25, 26-35, 36-45, 46-55. Use formula for each: =COUNTIF(D2:D9, "18-25") (adjust age group accordingly).

Expected Result

18-25: 3, 26-35: 3, 36-45: 1, 46-55: 1

5

Step 5: Calculate the percentage of participants in each Age Group.

For each age group count cell, use formula: =(count_cell / total_participants_cell) * 100 and format as percentage with 1 decimal place.

Expected Result

18-25: 37.5%, 26-35: 37.5%, 36-45: 12.5%, 46-55: 12.5%

Final Result

Workshop Session Summary
------------------------
Morning:   3
Afternoon: 3
Evening:   2

Age Group Summary
-----------------
18-25: 3 (37.5%)
26-35: 3 (37.5%)
36-45: 1 (12.5%)
46-55: 1 (12.5%)

✓Most participants prefer Morning and Afternoon sessions equally.

✓The largest age groups attending are 18-25 and 26-35, each making up 37.5% of participants.

✓Evening session has fewer participants.

✓Older age groups (36-45 and 46-55) have smaller representation.

Bonus Challenge

Create a chart in Google Sheets that visually shows the number of participants per Workshop Session and the percentage distribution of Age Groups.

Show Hint

Use Insert > Chart, select 'Column chart' for sessions count and 'Pie chart' for age group percentages.