Unleash the Sheet!

Spreadsheets. One of the most under-utilised and misunderstood tools in schools. Some people just use them for simple things like making lists or organising numbers... But for those of us who understand them, we know just how awesome they can be to manipulate, sort, and filter data. This session will bring you into the inner circle of Sheets users and help you unleash the magical power of the Sheet.

Start with the Basics

Open this Sheet (It will force you to make a copy)

Basic Fills Tab

  • Rows, Columns, Cells
  • Ranges
  • Fill Downs
  • Sequences

Basic Functions Tab

  • Split names into two columns
  • Set Last name to Uppercase
  • Calculate Totals, Averages, Max and Mins
  • Count the number of students and display in A18
  • Use conditional formatting to colour the scores, higher numbers darker

Let's get more complicated

Open this Sheet (It will force you to make a copy)

Basics Tab

  • Freeze Rows
  • Adjust column width
  • Make ID number 4 digits, ie 0001, not 1
    • Numbers vs Text
  • Filters
    • Sort, Select, Conditionals
    • Filter Views
    • Green box
  • Pivot Table - Count of Game vs Platform
  • Remove unused Rows and Columns
    • AddOn - CropSheet

Roster Tab

  • Freeze Rows
  • Make DOB Dates consistent
  • Change Gender to Male/Female using dropdown data
  • Insert cells to fix corrupt data (from Zavala)
  • Split names to new cells using PowerTools
  • Generate email address using first initial, 3 letters of last name and last three digits of school ID
    • use the fake domain myschool.tas.edu.au

Gradebook Tab

  • Centre text on Column A
  • Resize all columns
  • Freeze Row 1
  • Fill Column A with consecutive numbers
  • Use VLOOKUP to find student First and Last names based on student number   Hint: =VLOOKUP(D2,Roster!A:C,2,"FALSE")
  • Make DOB dates consistent
  • Calculate the students age  Hint: =datedif(E2,today(),"Y"
    • Use Conditional Formatting to highlight any students under 13 years old.
  • Enter up to 4 different Group names. Data validation. Look at how the Groups are added on the Menus Tab
  • Columns H-L, add 45º text rotation to the headings. Make sure they don't overlap
  • Fill these cells with colours and add gridline borders
  • In column M add up the score for these columns. Label it "Total"
  • In row 19, calculate the average score for each task to 1 decimal place

Food Diary Tab

  • Conditionally format the cells containing "junk" to show in red text
  • Adjust the date to show day/month/year
  • Create a pivot table that shows the student names and how much Junk/Healthy food they consumed  Hint: Summarize by COUNTA