Spreadsheets Are My Superpower
1 Spreadsheet Mania - can you keep up?
This is a fun quick way to get students to understand rows, columns, and cells in a spreadsheet. Students should go to Google Drive and create a blank sheet.
Teachers should do the same. Then as a teacher, tell them a spreadsheet is a bit like the game of battleship. Point out the columns are identified by letters and rows are identified by numbers and cells are identified by referencing the column and row in that order.
Have students select column C. Students should click the C at the top and notice that the entire column is highlighted. Then have students select row 3. If they click the 3 they notice the entire row is highlighted. Columns go up and down (just like the columns on the white house.) Rows go across like the rows in theater.
Then have them select cell C3. They should only be in one cell.
Now have them click on cell D5. Ask these questions:
What column are you in? answer is D
What row are you in? answer is 5
What cell are you in? answer is D5
Practice this until students understand the difference. Then have all students go to A1 (show students how pressing CTRL key + HOME key gets them to A1 very quickly.)
Now for the fast play game. Start calling out instructions. Start the game slowly and then you will speed up. You need to make the same moves you are calling out but where students cannot see where you end up. Call out:
If students follow the instructions below they should end up on E4. Ask where they are? (You can ask what column are you in, what row, what cell?) Have students call out answers. Then tell them E4 is correct. Have all students get on E4 and call out new directions (you follow at the same time). See how many students end up at the correct place.
Get faster and faster and only keep the students in the game who are on the correct cell. See if you can get a winner or a few winners.
Use CTRL HOME in one of the later games to see if they can keep up!
Now you are ready to start the spreadsheet activities.
Open Google Drive and create a new blank spreadsheet. Wait for teacher instructions as you will compete in a fast game of Spreadsheet Mania.
2 Opening and Saving Budget Spreadsheet Template
Make the Google Holiday Budget Spreadsheet Template available to students. You can direct them to this link to force a copy: Budget Spreadsheet Template
Or create your own copy, edit and provide the link to students.
Open the Holiday Budget Spreadsheet and save a copy to your Google Drive. Budget Spreadsheet Template
3 Practice Data Entry
Ask the class to vote on their favorite ice cream flavor from the choices in the spreadsheet template. (Chocolate, Strawberry, Vanilla, Other) Have students raise hands to vote. Remind students they can vote only once. Once the numbers are tallied by the teacher, have students enter the numbers in the appropriate cell on the spreadsheet.
Students enter number of chocolate ice cream votes into Cell B4.
Students enter number of vanilla ice cream votes into Cell C4.
Students enter number of strawberry ice cream votes into Cell D4.
Students enter number of other ice cream votes into Cell E4.
4 Understanding Basic Formulas and the Benefits
Have students type in the total by manually adding it up and typing in a number. Then, tell them 2 new imaginary students have voted for Chocolate and have them increase the chocolate number by 2. Ask them if the total they entered previously is still correct? It is not. Have them erase the total they typed in and teach them how to enter a basic formula.
Formulas are just equations that contain cell references so that no matter what numbers are entered or changed in a cell, the formula will calculate the correct answer.
Ask students which cells have the votes that should be added for the votes? They should say B4, C4, D4, and E4.
Now have them type in an equation starting with the = sign that adds up those cells. Have them type the equation in cell F4 where the total should go.
It should look like =B4+C4+D4+E4
Once they press enter the total should be correct.
Now have them change the chocolate votes back to the original number and they should see the total change automatically.
Students should type in a total in F4 to manually add up the votes.
Then the teacher will instruct them to add 2 votes to the Chocolate vote and note the total does not change.
Students will enter a formula to get the total and notice how when changes are made the total updates automatically.
Students should be able to explain the advantages of the formula.
5 Adding Functions to Save Time
Ask students what would happen if there were 100 ice cream flavors to vote on? How long would the formula they just typed be if they had to use just the + signs?
Introduce the SUM function to students. Have them replace the formula in cell F4 with a SUM function.
Tell students that they still start with the = sign but use SUM and then add up the cells on either side of a colon like this:
This formula gives the same answer but adds up all values in the cells starting with B4 and ending with E4.
Have students try changing a vote value to see the formula update automatically. Discuss the benefits of functions.
Students will use a SUM formula to add the cells in B4 through E4.
Explain benefits of functions.
6 Graphing the Data Using Google Sheets Insert Chart Tool
Have students create a graph from the ice cream vote data.
Students should select data and create a chart. Cells selected should be cells B3 through E4 .Make sure students do not select the total when highlighting the data.
Use advanced editing tools to add a title and change the colors and style of the chart.
Students will probably have issues where the chart shows no data. Discuss why this might have happened. Have students offer suggestions and solutions. Usually one issue is students forgot to select the data or selected incorrect data.
Students should select the ice cream data and create a chart.
Cells selected should be cells B3 through E4 . You do not need the total in the chart.
Students should use advanced editing tools to add a chart title and change the colors and styles of the chart. Have students help each other and share when they find a new way to edit the chart.
7 Let's Go Shopping - Understanding and Using Advanced Formulas in Spreadsheets
Tell students to choose the 2nd tab in the Spreadsheet template they have been using. The tab is named Holiday Shopping.
Have students click on the cells D4, E4 , F4, F14 and F18.
Make sure students do not DELETE the formulas.
Conduct a class discussion where students discuss what they think each formula is doing in each cell.
Students will then begin pretend shopping on Amazon to find gifts and prices to add to the spreadsheet. Remind students to put the information in the correct cells. They must put a number in Quantity or the formulas will not work. They can use the UNDO button if they make a mistake.
When students are finished entering all gifts in the spreadsheet have students disucss whether they stayed in budget or not and how the spreadsheet helped them with decision making.
Students click on the cells D4, E4 , F4, F14 and F18 to view the formulas located there. Explain what each formula is doing in the spreadsheet.
You are tasked to shop for family and friends for the holidays. You should try not to go over the $500 budget you have been given.
Click the Amazon.com link to start shopping. Choose gifts on Amazon, locate prices and then type in the quantity (how many you will buy), the product name, and gift price in the appropriate cells in the spreadsheet.
Notice how the spreadsheet automatically updates when you press enter.
Students answer these questions.
1. Did you stay in budget?
2. What are the benefits of the formulas?
3. How many math problems would you have needed to do manually if you did not have the spreadsheet?
8 Extending the Lesson- Want to become a Google Master?
Tell students they can actually become more advanced in Google Sheets by attempting these training modules.
See resource link above.
Learn more by completing the training modules on the links above. Decide how you might use spreadsheets to help with your learning.
Key Standards Supported
Interpreting Categorical And Quantitative Data
Represent data with plots on the real number line (dot plots, histograms, and box plots).
Use statistics appropriate to the shape of the data distribution to compare center (median, mean) and spread (interquartile range, standard deviation) of two or more different data sets.
Interpret differences in shape, center, and spread in the context of the data sets, accounting for possible effects of extreme data points (outliers).
Use the mean and standard deviation of a data set to fit it to a normal distribution and to estimate population percentages. Recognize that there are data sets for which such a procedure is not appropriate. Use calculators, spreadsheets, and tables to estimate areas under the normal curve.