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.

Practice

1. What happens when you link a Google Form to a Google Sheet?

easy

A. Each form response is added as a new row in the Sheet.

B. The Sheet deletes all previous data automatically.

C. The form responses are saved only in the Form, not in the Sheet.

D. The Sheet creates a new column for each response.

Solution

1. Step 1: Understand Google Forms and Sheets connection

   When a Google Form is linked to a Sheet, each submission adds a new row to the Sheet.

2. Step 2: Check how data is stored

   Responses are stored row-wise, not by deleting data or adding columns per response.

3. Final Answer:

   Each form response is added as a new row in the Sheet. -> Option A

4. Quick Check:

   Form responses = new rows in Sheet [OK]

Hint: Remember: Each form answer adds a new row [OK]

Common Mistakes:

• Thinking responses overwrite old data
• Believing responses create new columns
• Assuming data stays only in the Form

2. Which formula correctly sums the values in column B starting from row 2 in a Google Sheet linked to a Form?

easy

A. =SUM(B2:B100)

B. =SUM(B:B2)

C. =SUM(B2:B)

D. =SUM(B2)

Solution

1. Step 1: Understand SUM range syntax

   The correct way to sum values in column B starting from row 2 is to specify a fixed range like B2:B100 to ensure only valid rows are summed. Using B2:B is invalid syntax in Google Sheets and will cause an error.

2. Step 2: Check each option

   =SUM(B2) sums only one cell. =SUM(B:B2) is invalid range. =SUM(B2:B) is invalid syntax. =SUM(B2:B100) sums a fixed range, which includes rows 2 to 100.

3. Final Answer:

   =SUM(B2:B100) -> Option A

4. Quick Check:

   SUM from B2 to B100 = =SUM(B2:B100) [OK]

Hint: Use fixed ranges like B2:B100 to include expected data [OK]

Common Mistakes:

• Using incorrect range syntax like B:B2
• Summing only one cell instead of range
• Using open-ended ranges like B2:B which are invalid

3. Given a Google Sheet linked to a Form, what will the formula =COUNTIF(C2:C, "Yes") return?

medium

A. An error because COUNTIF cannot use open-ended ranges.

B. The total number of rows in column C.

C. The number of rows where column C has the text "Yes".

D. The sum of all numeric values in column C.

Solution

1. Step 1: Understand COUNTIF function

   COUNTIF counts how many cells in a range meet a condition. Here, it counts cells in C2:C equal to "Yes".

2. Step 2: Analyze the formula behavior

   The formula counts all rows from C2 down with "Yes" text. It does not count all rows or sum numbers.

3. Final Answer:

   The number of rows where column C has the text "Yes". -> Option C

4. Quick Check:

   COUNTIF(C2:C, "Yes") = count of "Yes" [OK]

Hint: COUNTIF counts cells matching criteria in a range [OK]

Common Mistakes:

• Thinking it counts all rows regardless of content
• Confusing COUNTIF with SUM
• Believing open-ended ranges cause errors

4. You want to count how many responses in column D are greater than 50, but your formula =COUNTIF(D2:D, ">50") returns 0 even though there are numbers above 50. What is the likely problem?

medium

A. The formula syntax is incorrect; COUNTIF cannot use ">50".

B. The numbers in column D are stored as text, not numbers.

C. The range D2:D is invalid and should be D:D2.

D. Google Sheets does not support COUNTIF with comparison operators.

Solution

1. Step 1: Check formula syntax

   The formula syntax is correct; COUNTIF supports ">50" and open-ended ranges.

2. Step 2: Identify data type issue

   If numbers are stored as text, comparison operators fail, so COUNTIF returns 0.

3. Final Answer:

   The numbers in column D are stored as text, not numbers. -> Option B

4. Quick Check:

   Text numbers cause COUNTIF comparison to fail [OK]

Hint: Check if numbers are text; convert to number format [OK]

Common Mistakes:

• Assuming formula syntax is wrong
• Using invalid range syntax
• Believing COUNTIF can't use comparison operators

5. You have a Google Sheet linked to a Form collecting sales data: columns are Timestamp, Salesperson, Product, and Amount. You want to create a summary sheet that shows total sales per Salesperson. Which formula in the summary sheet cell B2 (next to Salesperson name in A2) will correctly calculate this?

hard

A. =VLOOKUP(A2, 'Form Responses'!B:D, 3, FALSE)

B. =COUNTIF('Form Responses'!B:B, A2)

C. =SUM('Form Responses'!D:D)

D. =SUMIF('Form Responses'!B:B, A2, 'Form Responses'!D:D)

Solution

1. Step 1: Understand the goal

   You want total sales amount per Salesperson. So sum Amount where Salesperson matches A2.

2. Step 2: Analyze each formula

   =SUMIF('Form Responses'!B:B, A2, 'Form Responses'!D:D) sums Amount (column D) where Salesperson (column B) equals A2. =COUNTIF('Form Responses'!B:B, A2) counts entries, not sums. =SUM('Form Responses'!D:D) sums all sales ignoring salesperson. =VLOOKUP(A2, 'Form Responses'!B:D, 3, FALSE) looks up a single value, not sum.

3. Final Answer:

   =SUMIF('Form Responses'!B:B, A2, 'Form Responses'!D:D) -> Option D

4. Quick Check:

   SUMIF sums Amount by Salesperson [OK]

Hint: Use SUMIF to sum amounts matching salesperson [OK]

Common Mistakes:

• Using COUNTIF instead of SUMIF for totals
• Summing entire column ignoring salesperson
• Using VLOOKUP which returns single match only