Goals of today

• Review G-Suite 
• Learn best practices for Google Forms
• Learn basic spreadsheet functions in Google Sheets

The world of G Suite

Collaboration, Productivity, Anywhere, Anytime

Why G Suite?

• Cloud based collaboration
• Unlimited storage
• Cloud based productivity tools
• Learning anytime, anywhere

Google Drive

Your cloud based storage site

Forms

Survey tool

Google Form Tips

• Question Types
  • Short answer: can’t hit enter
  • Paragraph: unlimited
  • Multiple choice: can only pick one
  • Checkboxes: can pick several
  • Dropdown: can only pick one
  • File upload: allows them to upload
  • Linear: ranking
  • M.C. Grid: can only pick one per line
  • Checkbox grid: can pick several
    

Google Form Tips

• When making questions, make the important ones required!
• In settings, you can collect emails  or limit to 1 response but it will require Google Sign-On.  Make a question asking for email if you are sending it outside of BCSC.
  
  
• Stick to list options over open ended questions (ex. School names, teacher names)
• Data will match questions so plan ahead on what data you want and will need to analyze
  
• When sending the form, use the link and don’t type in emails
  
  
• Create a spreadsheet, see summary of results, or turn the survey off.
  
  

Sheets

Spreadsheet

• Basic
• Large Data

Google Sheet Work Along

• https://docs.google.com/spreadsheets/d/1_c4pGK-QcsbWm3cCBGT2W-JQ53KMluv0CdlnWdFEJwY/copy
  
• Cheat Sheet
• Use the shortcut icons including link, comment, graph, formula, and filter
  
• Filtering Data - under Data menu, filter
  • You can sort data by condition or value
  • You can set multiple filters, but they will go in order of how you set them
    
• Useful Formulas - Use their hints or Google/YouTube
  • SUM: Finds the total of a range
  • AVERAGE:  Finds the average of a range
  • COUNT:  Total number of items in a range
    • COUNTIF:  Count based on criteria
  • IF:  Allows you to fill cell based on criteria
  • SUBTOTAL:  Finds above values but for a filtered view
  • VLOOKUP:  Will compare data sets
• Right click on a cell or highlight cells and under Format, you can set Conditional Formatting
  • Allows you to change the color of the cell based on a set of rules you set. (ex. Equal to a word or value)
    
• Right click on a cell or highlight cells and under Format, you can set Data Validation
  • Allows you to reject or warn users that data isn’t valid based on criteria
    

Google Sheet Tips - Large Data

• Managing large amounts of data
  • Pivot tables
  • Multiple tabs
• Pivot Table (Data --> Pivot Table)
  • Allows you to quickly analyze data sets automatically
  • Work backwards - think about values you want and then add rows and columns
  • Filter to get snapshots of what data you want to see
    
• Multiple Tabs and Sheets
  • You can split data up by different categories
  • You can share with different people without them seeing the original
    
• Setting up tabs
  • You can set up a pivot table to copy over information
    Add every row without totals and filter for tab
  • Use Query: tab name, range, what
    =QUERY(Student!A2:I33,"Select*Where D ='Hufflepuff'")
• Pulling to a new spreadsheet
  • Allows you to share data with other people who don’t need access to everything
  • Importrange (URL, tab, range)
    =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1GXa2ReGwPka6nwu3yTdkFVkhNzIzNXE2EcGe2sfz4Ug/edit#gid=1866589602","Richards!A1:I100")
  • Need to grant permission
  • Data can be copied for schools to manipulate

Google Sheets Tips - Graphing

• Data is easiest to graph if you have one to one data points
  • Example - test name and test scores
• You can create tabs with pivot tables to help set up graphs easier - especially if you are pulling from a live Google Form
• You can copy and paste Graphs into presentations and they will update
• Use the $ in front of data sets that you don’t want to automatically format
  
• Double click on bottom right of cell to auto copy
  
• Use Control H to find and replace