Google Sheets Workshop

• Freeze rows and columns

• Resize columns and rows

• Use wrap text

• Basic built-in calculations

• Basic formulas like =SUM(A1:A5), =AVERAGE(A1:A5)

• Splitting text (e.g. first and last names)

  1. Insert a new column

  2. Select the cell (or several cells)

  3. Select Data - Split text to column

  4. Select your divider (like comma, space etc.)

• Drag the blue handle at the bottom right of a cell to transfer its formatting to the cell(s) below. HINT:  Also works to populate cells in consecutive order (numbers, days of the week, months etc.)

• Format Numbers: Select Format, then select what format to apply.

• Click on the funnel icon to filter data: This makes a large range of data more manageable, or lets you analyse a subset of data (all Y9 boys etc.)

• Sort data A-Z

• Convert data to a table - right-click in any cell, select Convert to table.

• Sort data in your table by column to see related information grouped together. E.g., view all Vietnam trip participants grouped by house, or by year level.

• Conditional formatting: Highlight selected data such as attendance rates, A/M/E etc:

  1. Select the range you want to apply this to (e.g. all cells of the sheet)

  2. Format - Conditional Formatting

  3. Select your criteria, e.g. Text contains Kōwhai

  4. Select what colour to apply

  5. Click Done.

• Insert Drop-downs = Data Validation

  1. Insert - Drop-down

  2. Check what range this applies to, or apply it to a single cell and later transfer the formatting to other cells with the blue handle.

  3. Replace Option 1 with your first choice, e.g. achieved

  4. Replace Option 2 with your next choice, e.g. merit

  5. Replace Option 3 with your next choice, e.g. excellence

  6. Replace Option 4 with your next choice, e.g. not attempted

  7. Add colours as desired

  8. You can edit your drop-downs via the little pen and also add more options.

• Make large sets of data easier to read with alternating colours (click the paint bucket, select alternating colours)

More handy tricks:

• 1. Merging data from two different cells into one cell, like first and last name:

     • Insert a column to the right

     • Add this formula into the first cell =CONCATENATE(A1," ",A2)

     • Please note that the " " adds a space between the first and the last name

     • Use the blue handle to apply this formula to the cells below.

  2. Check a list for duplicate entries, e.g. a student has submitted their Google Form twice:

     • Conditional Formatting

     • Use Custom Formula

     • Enter this formula =COUNTIF(A:A,A1)>1

     • Select a colour, then click Done.