Master Google Sheets: Essential Tools and Techniques for Productivity

Unit 4.3: Identifying Patterns and Making Connections This work was funded by the National Science Foundation through grant #2013338, Teaching with Investigation and Design in Science (TIDeS). The opinions, findings, conclusions, and recommendations expressed are those of the authors and do not necessarily reflect the views of the NSF.

Getting to know Google Sheets Open up Google Drive and sign in Click on New in the upper left hand corner Select Google Sheets Name your spreadsheet as follows: YourName_LocationofData_DataSetDateRange Example: AngelaDaneshmand_Fullerton_2023 The Google Sheet is organized by letters and numbers Kind of like Battleship! Here is C2

Dragging Cells In A1 type 1, In B1 type 2 Click on A1 and drag your cursor over B1 to highlight both boxes Then, click on the small circle on the bottom right and drag it to the right Sheets will look at the sequence and spread it over as far as you have dragged it If you highlight your sequence, the sum will be calculated on the bottom right of the sheet

Using Formulas In C5 type in =30+5, then press enter The = tells Sheets that this is a formula Click on the A3 box and type = Then, click on A1 press + and then click on B1 , then press enter Another way is: Click on the A4 box and type in =A1+B1, then press enter Click on the A2 box and type =50* and then click on A1 and press enter Click on the A2 box, click and drag the circle to F2

Inserting New Columns/Rows Right click on A Select insert 1 column left Right click on 1 Select insert 1 row above In A2 type in HW Assignments In A3 type in Study Time Double click between the A and B columns to autofit the box

Freezing Panes Select View Select Freeze Select 1 Column

Creating a Graph or Chart Click on B2 and drag the cursor until it covers everything to G3 Click on Insert, then click on Chart Sheets will assume the chart you want based on the data set This is a column/bar chart

Creating a Scatter Plot On the right hand side you will see the chart editor Change the chart type to Scatter Plot

Editing Chart Visuals In the Chart Editor, click on Customize Click on Chart & Axis Titles. Enter a Title for the chart and titles for the x- and y-axes. Feel free to play around with the font type and size! Click on Series. Here you can adjust the color, shape, and size of the data points. Feel free to play around with this. If you scroll down a bit, here you can also add error bars, data labels, and a trendline. The trendline is important as it can help with forecasting!

Making Forecasts Click on Horizontal Axis Type in 10 under Maximum Value Click on Vertical Axis Type in 600 under Maximum Value Using the plot, how long should you study if you have 8 assignments? 10?

Creating a New Sheet and Adding Data New Sheet Click on the + on the bottom left hand side of the screen Adding Data to Sheet 2 Go to Weather Underground and search for your location Click on the History Tab Change the View Window to Monthly Scroll down to Daily Observations and copy the data into your Google Sheet Highlight the Data Set, Press Ctrl+C Google Sheet: Right Click, Paste Special, Values Only Note: You will have to edit the data so that it is useable, your instructor may have a file that s already been cleaned up for you to use

Adding a New Data Sheet Your instructor may have a file that s already been cleaned up for you to use Navigate to the correct sheet Right click on the sheet name at the bottom of the page and select Copy to and select Existing Spreadsheet Find and select the spreadsheet you have been working on

How to select non adjacent rows/columns: PC users: Select first column, hold down ctrl and select second column Mac users: Select first column, hold down command and select second column

Break Out #1: Plotting the data 1. Create a graph for each of the following sets of data: Temperature Vs. Time Humidity Vs. Time Precipitation Vs. Time 2. Describe which type of graph you used for each and why you chose to use this type of graph. 3. Analyze your graphed data and identify any potential patterns that you see. 4. How do these data compare with the data you collected in the field last class (Unit 4.2)?

Break Out #2 Small Group Discussion Discuss data collected and graphed with your group: What patterns have you identified within your own data set? Did other group members come to the same conclusions? What might account for any differences you see? Share questions that came up during your data analysis and come to a conclusion on answers.

Break Out #3: Explore US Daily Climate Normals Each group will compare their data sets with the US Daily Climate Normals and will report back to the class.

Discussion: Making Connections Recall what we have learned in Unit 4 thus far. In your groups, answer the following questions: 1. Why is it important to compare current data to Climate Normals? 2. Infer how data can inform models and be used to make predictions of weather 3. Thinking back to recording data in the field during Unit 4.2 and looking at larger datasets in Unit 4.3, why do you think it is important to collect weather data at the local scale and to compile those data at larger scales (states, countries, etc.)?