Google Sheets Warm-Up: What's Your Favorite Formula?

Think about any time you've used Google Sheets or another spreadsheet program.

1. What's one thing you've done with a spreadsheet?




2. If you know any spreadsheet formulas, what's one you've heard of or used?




3. If you don't know any, what's something you wish a spreadsheet could do automatically for you?

Formula Fun: Sheets - Teacher Script

Part 1: Warm-Up & Introduction (10 minutes)

(Display Slide 1: "Welcome to Formula Fun: Sheets!")

"Good morning/afternoon, everyone! Today, we're going on an exciting adventure into the world of Google Sheets. We're going to become 'Formula Fun' experts!"

"First, let's get our brains warmed up. I've handed out a quick Google Sheets Warm Up or you can find it digitally. Please take about 3 minutes to answer the questions about your experiences with spreadsheets. Think about: What's one thing you've done with a spreadsheet? If you know any formulas, what's one you've heard of? If not, what do you wish a spreadsheet could do for you automatically?"

(Allow 3 minutes for students to complete the warm-up.)

"Alright, let's share a few ideas. Who'd like to share what they've done with a spreadsheet before? (Call on 2-3 students). Great examples! And how about what you wish a spreadsheet could do? (Call on 1-2 students)."

(Display Slide 2: "Spreadsheets: More Than Just Tables!")

"You're all hitting on something important: spreadsheets are amazing tools for organizing and analyzing data. But they're not just for typing lists or making pretty tables. The real magic happens with formulas! Formulas are like smart commands that tell the spreadsheet to do calculations or organize information for you automatically."

"Today, we're going to learn four super useful tools: COUNT, AVERAGE, SUM, and SORT. By the end of this lesson, you'll be able to use these to make your data work smarter, not harder!"

Part 2: Guided Practice & Activity (25 minutes)

(Display Slide 3: "COUNT: How Many Are There?")

"Let's start with COUNT. Imagine you have a long list of student IDs, and you want to quickly know how many students are on that list. Typing and counting each one would take forever! That's where COUNT comes in."

"The COUNT formula simply tells you how many numbers are in a selected group of cells. If you have text in a cell, it won't count it, only numbers. We use it to count things like the number of items sold or how many students completed a survey."

"On the screen, you can see the example: =COUNT(A2:A10). This would count how many numbers are in cells A2 through A10. Let's try this together. Open a new Google Sheet (or the shared activity sheet). Type some numbers into cells A2 to A5. Now, in another cell, type =COUNT(A2:A5) and press Enter. What do you see? (Wait for responses. Emphasize that it only counts numbers.)"

(Display Slide 4: "AVERAGE: What's the Middle?")

"Next up is AVERAGE. We use average a lot in school, right? Like finding your average test score. The AVERAGE formula calculates the typical value, or the mean, of a group of numbers."

"The example is: =AVERAGE(B2:B10). This would calculate the average of numbers in cells B2 through B10. Let's try it. In the same sheet, put a few different numbers in cells B2 to B5. Now, in an empty cell, type =AVERAGE(B2:B5) and press Enter. What's the average of your numbers?"

(Display Slide 5: "SUM: Let's Add it Up!")

"Now for SUM. This is probably one you might have guessed! The SUM formula adds up all the numbers in a selected group of cells. Super useful for finding totals!

"Our example: =SUM(C2:C10). This adds up numbers in cells C2 through C10. Let's quickly try this. In cells C2 to C5, put some numbers. In an empty cell, type =SUM(C2:C5) and see your total!"

(Display Slide 6: "SORT: Get Organized!")

"Finally, let's talk about SORT. Sometimes your data can be a mess, and you need to put it in order. SORT helps you arrange your data alphabetically or numerically. You can sort by different columns, and in ascending (A-Z, smallest to largest) or descending (Z-A, largest to smallest) order."

"How it works: You select the data you want to sort. Then, you go to Data in the menu bar, choose Sort range, and tell Google Sheets which column you want to sort by and in what order. Let's practice quickly. Add some names in column A and scores in column B. Now, select both columns A and B, go to Data > Sort range, and try sorting by Column A (Name) A-Z. Then try sorting by Column B (Score) Z-A. Notice how the names and scores stay together!"

(Display Slide 7: "Your Turn! Time to Practice!")

"Excellent! You've learned about COUNT, AVERAGE, SUM, and SORT. Now it's your turn to apply these skills. I'm going to give you the Sheets Formula Activity. You'll be working with a sample dataset (or creating your own) and using these formulas to answer some questions about the data."

"You can work individually or with a partner. Remember to read the instructions carefully. I'll be walking around to help if you have any questions. Your goal is to fill out the activity sheet by applying the formulas correctly."

(Distribute/direct students to the activity. Circulate and provide support.)

Part 3: Discussion & Wrap-Up (10 minutes)

(After sufficient time for the activity, or when about 5-7 minutes remain.)

"Alright everyone, let's bring our attention back together. Great effort on the activity!"

(Display Slide 8: "Great Work, Formula Masters!")

"To wrap things up, let's have a quick discussion about what we learned and how these tools might be useful in the real world. I have some questions on the Sheets Formula Discussion sheet."

"Who can remind us what the COUNT formula is used for? (Listen for answers like 'counting numbers'). Great!"

"How about AVERAGE? When would you use that? (Listen for answers about finding a typical value, like grades). Excellent."

"And SUM? (Listen for answers about totals). Perfect."

"Lastly, when is SORT helpful? (Listen for answers about organizing data, finding highest/lowest). Exactly!"

"Can anyone think of a way these formulas or sorting could help them in another class, or even in their personal life? (Encourage a few responses)."

"Fantastic job today, formula masters! You've taken the first steps to making your spreadsheets incredibly powerful. Keep practicing these skills, they'll serve you well! Any final questions before we finish up?"

Google Sheets Formula Activity: School Sports Day Data!

Objective: Practice using COUNT, AVERAGE, SUM, and SORT in Google Sheets.

Part 1: Set Up Your Data

1. Open a new Google Sheet.
2. In Cell A1, type Athlete Name.
3. In Cell B1, type Event.
4. In Cell C1, type Score.

Now, fill in the following data starting from row 2:

Part 2: Apply the Formulas!

Use the formulas you learned to answer the following questions using your data. Type your formulas directly into cells in your Google Sheet and write down your answers here.

1. COUNT: How many scores are recorded in total for all events?
   

   • Formula used:
     
     
     
   • Answer:
     
     
     

2. AVERAGE: What is the average score across all events?
   

   • Formula used:
     
     
     
   • Answer:
     
     
     

3. SUM: What is the total sum of all scores from all events?
   

   • Formula used:
     
     
     
   • Answer:
     
     
     

Part 3: Sort Your Data!

Use the SORT function in Google Sheets to organize your data. After sorting, describe what you did and what changed.

1. Sort by Athlete Name (A-Z):

   • Steps:
     
     
     
     
     
     
   • What changed?
     
     
     
     
     
     

2. Sort by Score (Highest to Lowest):

   • Steps:
     
     
     
     
     
     
   • What changed?
     
     
     
     
     
     

Challenge (Optional):

• Can you find the highest score using a formula (MAX)? What about the lowest (MIN)? (Hint: These work similarly to SUM/AVERAGE!)
  
  • Highest Score Formula:
    
    
    
  • Lowest Score Formula:
    
    
    

Google Sheets Formulas: Class Discussion

Let's talk about what we learned and how these powerful tools can help us!

1. Reflection on Formulas:

   • Which formula (COUNT, AVERAGE, or SUM) did you find the easiest to use and why?
     
     
     
   • Which one do you think you'll use the most in the future, and for what purpose?
     
     
     

2. Sorting Power:

   • When we used the SORT function, what was the biggest advantage you noticed? How did it make the data easier to understand?
     
     
     
   • Can you think of a situation where sorting data would be absolutely essential?
     
     
     

3. Real-World Connections:

   • Beyond our school sports day example, where else in real life might people use these formulas (COUNT, AVERAGE, SUM) or sorting functions? Think about businesses, science, or even personal use.
     
     
     
   • How do you think using formulas and sorting data makes people more efficient or better at their jobs?
     
     
     

4. Challenges & Learning:

   • Was there anything tricky or confusing about using these formulas or the sort function at first?
     
     
     
   • What advice would you give to a friend who is just starting to learn about Google Sheets formulas?

Google Sheets Formula Activity: School Sports Day Data! - Answer Key

Part 2: Apply the Formulas!

1. COUNT: How many scores are recorded in total for all events?
   

   • Formula used: =COUNT(C2:C11)
     
   • Answer: 10
     
     

2. AVERAGE: What is the average score across all events?
   

   • Formula used: =AVERAGE(C2:C11)
     
   • Answer: 8.6
     
     

3. SUM: What is the total sum of all scores from all events?
   

   • Formula used: =SUM(C2:C11)
   • Answer: 86
     
     

Part 3: Sort Your Data!

1. Sort by Athlete Name (A-Z):
   • Steps: Select columns A:C. Go to Data > Sort range > Advanced range sorting options. Choose Column A to Sort by, order A > Z. Ensure