1 of 43

Google Sheets 201

February 2, 2023

Haruna Hosokawa & Kate Ziegler

BPSTechnology Digital Learning Team

bit.ly/BPS-sheets102

2 of 43

Facilitators

Haruna Hosokawa�BPSTechnology �Program Dir. of STEM & Robotics�📧 hhosokawa@bostonpublicschools.org

Kate Smith-Ziegler�BPSTechnology �Instructional Technology Coach�📧Ksmithziegler@bostonpublicschools.org

bit.ly/BPS-sheets102

3 of 43

Housekeeping

This session is closed captioned. Click on the “Show Captions” icon.��
This session is being recorded.
Please make sure you are MUTED.
Please use your name in Zoom.
Ask questions in the chat.

bit.ly/BPS-sheets102

4 of 43

Who’s In the Room?

Please let us know in chat…

Your name
School or department & role
What you use Google Sheets for

bit.ly/BPS-sheets102

5 of 43

Objectives

Practice using Google Sheets features such as Vlookup and Pivot Tables.
Learn tips & tricks including Conditional Formatting with custom functions, Xlookup, and more.

bit.ly/BPS-sheets102

6 of 43

This workshop assumes that you are…

Familiar with basic Sheets operations such as navigation and using simple formula.
Comfortable navigating between data in different tabs (sheets)

bit.ly/BPS-sheets102

7 of 43

Topics to be covered in Sheets 201

Import Range (importing data from another spreadsheet)
Vlookup, Xlookup
Combining multiple functions
Advanced Conditional Formatting
Named Range
Other commonly used Functions
Charts
Pivot Tables

bit.ly/BPS-sheets102

8 of 43

Agenda

12:00-1:00 Sheets 102

1:00-1:30 Optional “Office Hours”

bit.ly/BPS-sheets102

9 of 43

👈 Click to make your own copy of sample data set.

Click here for a completed version of the sample sheet

bit.ly/BPS-sheets102

10 of 43

⚠️

Disclaimer

You will not get ALL of your Sheets questions answered today.

We recommend that you:

WATCH during this webinar.
DO on Sample Data at a later time OR during Office Hours.
Book 1:1 time for more help and specific questions.

We will send you the recording & completed Sample Data file.

bit.ly/BPS-sheets102

11 of 43

A note about “Range”

Formula and tools often refer to a “range” or address(es) of cells that the formula/tool applies to. Examples are:

Column: A2:A8

Single cell: B3

Area: A2:B4

Row: A2:E2

❗ Entire column(s) can be selected by omitting the row number. �For example, A:A is the entire A column.

bit.ly/BPS-sheets102

12 of 43

More about “Range”

Cell range references are RELATIVE to the current location.

If you “fill down” or copy & paste a formula to another cell, the cell range contained in the formula will automatically change unless you:

add “$” in front of column letter to lock column (Example: $A2)
add “$” in front of row number to lock row (Example: A$2)
or both (Example: $A$2) to lock both column and row

Enclose sheet names in ‘single quotes’ (optional if sheet name does not contain a space) followed by ! (Example: ‘sheet name’!A2)

bit.ly/BPS-sheets102

13 of 43

IMPORT RANGE

What it is: IMPORT RANGE allows you to import data from another spreadsheet file into the current sheet.

This is not the same as Copy & Paste because the data remains dynamic - it will update real-time as the source data changes.

Use case: Import a list of school & school leaders from a Master List.

You may be prompted to give permission to access the source sheet. You will get an error if you don’t have permission.

bit.ly/BPS-sheets102

14 of 43

IMPORT RANGE

Syntax: =IMPORTRANGE(“spreadsheet_url”,“range_string”)

“spreadsheet_url”

Copy & Paste the URL of your source sheet until right before “/edit”
Must be enclosed in quotation marks.

“range_string”

Name of sheet and cell range, enclosed in quotation marks �Ex. “‘Sheet1’!A:D”
Use single quotes around sheet name if name contains space or special characters.

bit.ly/BPS-sheets102

15 of 43

Note about IMPORT RANGE

IMPORT RANGE only works to import data from a different file.

Imported cells can not be edited.

Editing any part of an imported range will “break” the whole range.
Any data editing must be done in the source sheet. Imported cells are updated instantaneously.

You can format an imported range or add rows & columns before/after the range.

Imported data can be FILTERED, but not SORTED.

bit.ly/BPS-sheets102

16 of 43

VLOOKUP

What it is: VLOOKUP looks down a column for a specific value, then return the corresponding value in another column. Typically used to pull data from another sheet.

Use case: (Google Forms and Aspen) Use email addresses from Google Form responses to pull information for that user such as ID# or school name.

Useful for combining data from two or more sheets into one table.

Click here for more information

bit.ly/BPS-sheets102

17 of 43

VLOOKUP Example

Look for a specific email in a list in another tab and find the matching ID number.

bit.ly/BPS-sheets102

18 of 43

VLOOKUP

Syntax: =VLOOKUP(search_key, range, index, [is_sorted])

search_key is the value you are looking to match, or the corresponding cell address (ex. Email address)

range is the range of columns containing your data, typically in another sheet, with the first column being the data with search_key.

index is the column # of the value you want to place in the cell with the formula (ex. ID#). Look-up column is always column 1.

is_sorted is an optional field asking if the look-up column is sorted or not. For most cases use FALSE (not sorted; exact match).

bit.ly/BPS-sheets102

19 of 43

More about VLOOKUP

The column containing the search_key must be the FIRST COLUMN of your selected range. (If it is not, see Xlookup or rearrange your data so that the look-up column is to the left of return column)
Vlookup will return “#N/A” if the search_key value is not found within the look-up column.
If there are multiple matches, Vlookup will return the value from the FIRST match found.

bit.ly/BPS-sheets102

20 of 43

XLOOKUP

What it is: A new function in Sheets, it is a function that is similar to VLOOKUP but with more flexibility and functionality.

Use case:

Want to do a VLOOKUP but the look-up column is to the right of the return column.
Want to do VLOOKUP but search from bottom up (useful when you want to find the latest form response from a user)

Click here for more information

bit.ly/BPS-sheets102

21 of 43

XLOOKUP

Syntax: =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

search_key is the value you are looking for (ex. ID#)

lookup_range is the column containing the look-up value.

result_range is the column containing the values to return.

missing_value (optional) is value to return if data is not found

match_mode (optional) is typically “0” for exact match

search_mode (optional) is 1 for top to bottom, -1 for bottom to top.

bit.ly/BPS-sheets102

22 of 43

IFNA

What it is: Gives one response if the value is #N/A, another response if it is not #N/A.

Use case: Use in combination with VLOOKUP to replace #N/A errors with something else (such as blank or “No Response”).

Turn #N/A errors into “NO RESPONSE”

bit.ly/BPS-sheets102

23 of 43

IFNA

Syntax: =IFNA(value, value_if_na)

value: function will return this value if it’s not N/A.

value_if_na: If the “value” is #N/A, then the function will return value_if_na.

Example:�=IFNA(VLOOKUP(A1, Sheet1!A:D, 3, FALSE), “No Response”)

Returns the result of this vlookup if the value is found.

Returns “No Response” if vlookup results in #N/A.

bit.ly/BPS-sheets102

24 of 43

Conditional Formatting w/ Custom Formula

What it is: Use “custom formula” rule for conditional formatting.

Use case:

Change the color of entire row based on data from one column.
Find the maximum value across multiple columns and change the cell color.

bit.ly/BPS-sheets102

25 of 43

Conditional Formatting w/ Custom Formula

Example 1: Color entire row if Grade (Col F) is equal to 10.

bit.ly/BPS-sheets102

26 of 43

Conditional Formatting w/ Custom Formula

Example 2: Color maximum value in each row

bit.ly/BPS-sheets102

27 of 43

Conditional Formatting w/ Custom Formula

Example 3: Highlight row if a cell is blank �(useful in sign-up sheet)

bit.ly/BPS-sheets102

28 of 43

Named Range

What it is: You can name a range of cells so that you can refer to them with name in a formula (rather than using the address)

Use case: Assign names commonly used columns such as ID and email so that it is easier to enter and read your formulas.

You can go to Data→ Named ranges at any time to add, edit, or remove named ranges. Named ranges will work across all tabs.

bit.ly/BPS-sheets102

29 of 43

Counting Functions

COUNT(range) counts # of numerical values in a range. [Link]
COUNTA(range) counts the # of non-blank cells in a range (including cells with formula that returns a blank cell) [Link]
COUNTUNIQUE(range) counts the # of unique values in a range. [Link]
COUNTIF(range,criteria) counts the # of cells in a range that match 1 criteria. [Link]
COUNTIFS(range1,criteria1,range2,criteria2,...) counts the # of cells in a range that match multiple criteria. [Link]

Click here for more information

bit.ly/BPS-sheets102

30 of 43

Averaging Functions

AVERAGE(range) average of all numerical values in a range. Non-numerical cells are ignored. [Link]
AVERAGEA(range) average of all values in a range. Any cell with text will be counted as “0”. [Link]
AVERAGEIF(criteria_range,criteria,average_range) average of values in average_range for values that match the criteria in criteria_range. [Link]
AVERAGEIFS(average_range,criteria_range1,criteria1,...) same as averageif but uses multiple criteria. [Link]

bit.ly/BPS-sheets102

31 of 43

Charts & Graphs

To create a chart or graph in Sheets, highlight the data and select Insert → Chart.

Click here for more information

Charts are automatically updated �as data changes.
Charts that are copied into Slides �or Docs are updated automatically �if they are “linked”

bit.ly/BPS-sheets102

32 of 43

Pivot Table

What it is: Pivot Table is a tool to allow users to summarize and analyze data to extract information such as average, total, and counts.

Use case:

Obtain # of students by grade and school from a master list of students
Summarize test results to get average score for each school

Click here for more information

bit.ly/BPS-sheets102

33 of 43

Pivot Table: Setting up

Rows: What you want to see down the left side of the table.
Columns: Headers that you want to see across the top of the table.
Values: The data you want to see �inside the the table.

Rows: School name

Columns: Grade

Values: Average survey responses

bit.ly/BPS-sheets102

34 of 43

Bonus: FILTER Function

What it is: FILTER returns a subset of source range, based on one or more conditions.

Use case: Separate a set of data into different sheets based on school names.

Syntax: =FILTER(range, condition1, [condition2, ...])

Click here for more information

bit.ly/BPS-sheets102

35 of 43

Bonus: Protect sheet & range

What it is: You can protect sheets or ranges from getting edited by users (including “collaborators”). Different permissions can be assigned to different parts of the sheet.

Use cases: a) Create a sign-up sheet and protect all cells except for the column used to add names to prevent accidental editing.

b) Protect cells that are filled automatically by Importrange, Filter, or other auto-filling functions.

c) Assign different editors to each tab within your sheet

bit.ly/BPS-sheets102

36 of 43

Bonus: UNIQUE Function

What it is: Creates a list of unique values from a list containing duplicates.

Use case:

Get a list of unique emails from a list containing form responses where some users responded multiple times.
Get a list of school names from a master list of all students.

Syntax: =UNIQUE(range)

Click here for more information

bit.ly/BPS-sheets102

37 of 43

Bonus: More useful functions

Sum & Sumif, Sumproduct
Split & Concatenate
If, Ifs, Iferror
Flatten (use with Unique to put unique values from multiple columns into one list)
Mathematical operations: add (+) subtract (-) multiply (*) divide (/) exponent (^)

Complete Sheets Functions list

bit.ly/BPS-sheets102

38 of 43

Sheets & Forms Recommendations

When collecting data using Forms, standardize as many entries as possible by auto-collecting emails, using drop-down, multiple choice, and check boxes to enable effective filtering/sorting, lookups, and more.
When performing formulas on Form Response data that is still actively receiving responses, it is best to do the calculations on a separate sheet and reference the whole column (ex. A:A) to ensure that new responses are included.

bit.ly/BPS-sheets102

39 of 43

General Tips & Recommendations

When using functions that automatically takes up more than once cell (ex. Import Range, Unique, Filter, etc), editing any of the auto-filled cells will result in an error and the whole range will “break.” (Don’t panic - just undo if this happens!)
Whenever you use formulas, double check �the cell range and verify that the �calculated number “makes sense.”
When using formulas - look at tips and �help menu if you aren’t sure what the �inputs are.

bit.ly/BPS-sheets102