@EdTechTeam

Summit

 

 

HELLO!

My name is Jesse Lubinsky
I’m the Director of Technology and Chief Information Officer
for the Irvington Union Free School District in Irvington, NY.
You can find me on Twitter/Instagram at @jlubinsky
or via email at jlubinsky@gmail.com or
jesse@edtechteam.com

 bit.ly/jesseresources

 

This Session...
Title
“Revenge of the Sheets”
Learn to Google Sheets the Jedi Way!

Description
The long-awaited sequel to
“May the Forms Be With You”, this session will
enable educators to wield the full power of
Google Sheets. We will explore various
spreadsheet techniques, Add-Ons (like
Doctopus), as well as Google Forms and Google
Classroom integration strategies that will help
turn even the most basic spreadsheet users into
full-fledged Google Sheets Jedi Masters!

 Full
Disclosure
This presentation contains excessive
references to the film Star Wars and its
affiliated sequels and prequels. While this
session contains information designed to
strengthen your knowledge of Google Sheets
and empower you with new tools to use in
your classrooms, those not strong with the
Force may find themselves susceptible to the
influence of the Dark Side.

 I
la. 

{Fun-lull? .?Pip-nu-rh .nn Pun-lul- Huh-I'-
ovum-.1? Ill-a- tin-r nl 

ruddy. did?i?l?l?] lily ii?ll?l pflw??t 
F?run. [hurl-r.- ?uncut-bit. 
I?dk ?par: lessons. {En-1? tank?. the

and ?fth in Order in: 
Ike power of Sheets and haunt-cw;

Eaagfe Sheets Jedi Masters!

 

Google
Sheets Jedi
Academy
Welcome
to the
Google
Sheets
Jedi
Academy!

Will you
succeed
and
become a
Jedi or
join the
Dark
Side?

 Before We Get 

WE aofn Ban @0313

   
   
 

ngoufnus

Getting Our
Hands Dirty
Practice Google Sheet

When you see the Empire
logo in the upper left
corner of a slide, it
means there is a practice
tab in the Practice
Google Sheet for that
topic.

 j}




a


11

1B
19

 

Sheets

Vocabulary
Let?s review some important Google Sheets

term:

 

Example Google Sheet

File Edit View Insert



Name
A

IName I
I

Count Dooku

Rey Doe

Kylo Ren

Jar Jar Binks
Obi-Wan Kenobi
Poe Dameron
Captain Phasma
Chewbacca Wookiee
Finn Doe

Wilhuff Tarkin
Anakin Skywalker
Boba Fett

Darth Vader
Emperor Palpatine
General Grievous
Han Solo

Luke Skywalker
Mace Windu
Reading Data .



Format

.0 .00 123'
n- -t




Force GR Level
Dark
Light
Dark
Light
Light
Light
Dark
Light
Light
Dark
Light
Dark
Dark
Dark
Dark
Light
Light
Light


m<<4xc<~<gcg<4m~<g

Level

Data Tools Add-ons Help

1

ADC

Last edit was yesterday at 5:16 PM



WI

811?



'n
G)
I

SC
98

Comp. CompComp. A






Total Comp.


Fluency 0-3

ilubinsky@gmail.com 



WPM

175
215
130
150
150
170
165
170
160
190
145

180
170
170
175
155



 

Student RR Tracking 

Behavior Data 

4 
Explore

 

 

Sheets

Vocabulary
Cel 

 

 

    
   

   

 

xample Google Sheet I 
Edit View Insert Format Data Tools Add-ons Help Last edit was yesterday at 5:16 PM 
.o_.ar_1.123v Arlal . 1o . 1 11A- E'L'l?lh 
f3-

1 Force GR Level GR Level Acc SC Comp. Comp. Comp. A Total Comp. Fluency 0-3 WPM
2 Dark Light Kylo Ren Dark Jar Jar Binks Light Obi-Wan Kenobi Light Poe Dameron Light Captain Phasma Dark Chewbacca Wookiee Light Finn Doe Light Wilhuff Tarkin Dark Anakin Skywalker Light Boba Fett Dark 100
14 Darth Vader Dark 98
15 Emperor Palpatine Dark 98 A I
16 General Grievous Dark Han Solo Light 99
15 Luke Skywalker Light 100 
19 mm as sprea 6612 ere ata can 
Reading Data Student RR Tracking Behavior Data 

 

entered.

 

Cdumn

Sheets
Vocabulary

 

  
  

  

 

 

 

   
     
   
   
     
  
  
 

 

 

       

   

 

ample Google Sheet I ilubinsky@gmail.com .
File Edit mow Insert Format Data Tools Add-ens Help Last edit was yesterday at 5:16 PM a 
?n %.o_.og123v Artal . 1o . 315A- Evi-l?1Foroe Level GR Level ADC 80 Comp. Comp. Comp. A Total Comp. Fluency 0-3 WPM I
2 Count Dooku Dark Rey Doe Light Kylo Ren Dark Jar Jar Binks Light Obi-Wan Kenobi Light Poe Dameron Light Captain Phasma Dark Chewbacca Wookiee Light Finn Doe Light Wllhuff Tarkin Dark Anakin Skywalker Light 
Boba Fett Dark 
14 Darth Vader Dark 
15 A group of cells next to each
16 General Grievous Dark 
17 Han Solo Light 
18 mm other vertically. Labeled With
19 Mace Windu Light 

 

  
   

  

 

     

Reading Data Student RR Tracking 

letters.

 

 

Sheets
Vocabulary

 

 

 

 

 

 

 

Example Google Sheet I 
File Edit View Insert Format Data Tools Add-ens Help Last edit was yesterday at 5:16 PM 
?rm? 5 %.o_.og.123v Arlal . 1o . ?nElv 
?r Name
A 
1 Name Force GR Level GR Level Acc SC Comp. Comp. Comp. A Total Comp. Fluency 0-3 WPM
Count Dooku Dark 98 4 3 2 3 2 140
Rey Doe Light Jar Jar Binks Light Pee Dameron Light Captain Phasma Dark Chewbacca Wookiee Light Finn Doe Light Wilhuff Tarkin Dark Anakin Skywalker Light 97
13 Boba Fett Dark 100
14 Darth Vader Dark 98
15 98 A group of cells next to each
16 General Grievous Dark 98
17 Han Solo Light 99 
15W other horizontally. Labeled With
19 Mace Windu Light 98
Reading Data . Student RR Tracking Behavior Data rs
I

 

 

Sheets
Vocabulary

Cell Name
C4

Column letter and row number
(in that order) used to identify a
cell. For example, C4.

 Formula

Sheets
Vocabulary

Equation consisting of numbers, cell
names, and mathematical symbols in order
to perform a mathematical operation. The
result of the formula is displayed in the cell
that holds the formula.

 Sort

Sheets
Vocabulary

Operation that rearranges data
so it is in a specified ascending
or descending order, usually
alphabetical or numerical.

 Filter

Sheets
Vocabulary

A operation which allows you
hide data that you want to
temporarily take out of view.

 Level 1: Death Star I (Basic Tips & Tricks)

“That’s no moon. It’s a space station.” - Obi Wan Kenobi

 Sorting
Sorting can be done in
several ways:
⊡ Using the arrow next to
the column header
⊡ Using the Data menu
⊡ Choosing Sort range
underneath the Data
menu

 Filtering
Filtering can be done in
several ways as well:
⊡ Using the Data menu
and choosing Filter or
clicking the Filter
button on the toolbar
⊡ Creating custom Filter
views under the Data
menu

  . .. ..

   

Everything is proceeding
as haveforeseen.

Data
With data validation, youValidation
can restrict the values entered into a
cell as well as provide warnings for invalid data.

Cell that IDs
the criteria
for the count

 Conditional
Formatting
Conditional formatting allows you to have the text or
background change color dynamically based on content.

Cell that IDs
the criteria
for the count

 Protect Range
You can protect a range of cells or an
entire sheet of data. Right-click on a cell
and you can choose “Protect range…”

 Mapping
Sheets
Mapping Sheets lets you take a list of
addresses from Google Sheets and create a
Google Map image of those addresses.
Video Walkthrough

 Save As Doc
Save As Doc allows you to convert any Google
Sheets spreadsheet into a Google Doc for
easy printing or improved readability.

 Lab Scheduler
Lab Scheduler allows schools to easily setup
sign-out sheets for resources such as labs and
carts. Accommodates changing schedules
also.
Video Walkthrough

  

?We would be honored if you would join us. 
Darth Vader

Yet Another
Mail Merge
Yet Another Mail Merge is an incredibly
powerful mail merge for use with Gmail.
Among best features, you can track who
opens messages.
Video Walkthrough

 Power Tools
Power Tools offers one-click solutions for many
tasks: Split cells, remove duplicates, change
case, advanced find and replace, formulas, and
more.
Video Walkthrough

 QR Code
Generator
QR Code Generator allows you to quickly and
easily create QR codes directly from a Google
Sheet. Can save codes as one Doc or in PNG
files.
Video Walkthrough

 No Messy Data Allowed!
1

- .

"r

r.

?No disintegrations! 
Darth Vader

Remove Blank Rows

Remove Blank Rows will quickly remove all
blank rows in a spreadsheet. This eliminates
the time needed to parse through a
spreadsheet as well as accidental deletions.
Video Walkthrough

 Level 2: Death Star II (Powering Up)

“Now witness the firepower of this armed and fully
operational battle station!” - Emperor Palpatine

 Flubaroo
Flubaroo helps you quickly grade and analyze
online assignments and assessments. Shows
analytics and emails scores to students.
Demo (bit.ly/napastarwars)
Instructions
Video Walkthrough
Flubaroo Homepage

 

When Powerful Tools Combine
?join me and together we can rule the

galaxy! 

Doctopus
Doctopus allows teachers to share, organize, and
assess student work in Google Drive through it’s ability
to manage files in Drive. It works beautifully on top of
Google Classroom and its integration with Goobric (a
Chrome Extension) adds even more powerful abilities
to attach rubrics to student work.
Doctopus & Goobric Instructions
Doctopus Video Walkthrough
Goobric Video Walkthrough
Doctopus Homepage

  

will you started. 

Autocrat
Autocrat lets you take information from a
Sheet to automatically populate and create a
Google Doc or PDF file.
Instructions
Video Walkthrough
New Visions Homepage

 Fusion Tables
Fusion tables are an amazing way to have
your data tell a story visually.
Demo
Student Sample
Video Walkthrough
Example Gallery

 Pivot Tables
Pivot Tables are a function within Google
Sheets which allows you to create quick
summaries of your data.
Click “Pivot Table” on
the Data menu to
access them.

 Level 3: Starkiller Base (Max Power)

“This fierce machine, upon which we stand, will bring an
end to the Senate! To their cherished fleet!” - General
Hux

 Function List
The function list provides you will a comprehensive list of all of
the functions that work in Google Sheets.

Cell that IDs
the criteria
for the count

 IMPORTRANG
E

IMPORTRANGE allows users to import a range of cells from
another Google Sheets file. Once users are granted permission,
any editor on the destination spreadsheet to pull from the
source spreadsheet.
Range of values to
import

=IMPORTRANGE(SpreadsheetKey, 'Form Responses'!B2:B43)

Key of spreadsheet you
are pulling from

Sample Key
Cell that IDs
This Google Slides file’s key is:
the criteria
1H84G3LHJakIJoyOt9oq65Pzz3
H712s4ReHSUpuiB8qo
for the count

 TRANSPOSE
The TRANSPOSE
function allows
you to swap the
rows and
columns of a
range of data.

First and last
cell in range,
separated by :

=TRANSPOSE('Form Responses'!A11:C12)
Name of the sheet from which you’d like to pull your
data
(in single quotes, ending with !)

 Let?s Count Stuff



?Always two there are. No more, no less. 
Yoda



COUNTIF
The COUNTIF
function will
return a value
based on the
number of times
a criterion is met.

COUNTIFS (which
allows for
multiple criteria)
is available in the
new Sheets.

=COUNTIF('Form Responses'!C2:C43,B1)
Data you’d like
to count from

Cellthat
thatIDs
IDs
Cell
thecriteria
criteria
the
for the
the count
count
for

 Counting
Functions
The COUNT function will return the number of values in a
range

=COUNT(C2:C43)

Range of
values
The COUNTBLANK function will return the number of blanks in
a range

=COUNTBLANK(C2:C43)
Range of
values

Cell that IDs
the criteria
for the count

 More Counting
Functions
The COUNTUNIQUE function will return the number of unique
values in a range

=COUNTUNIQUE(C2:C43)
Range of
values
The COUNTA function will return the number of values in a
range

=COUNTA(C2:C43)
Range of
values

Cell that IDs
the criteria
for the count

 FILTER
FILTER will return a filtered version of the source range. The data
returned will only contain the rows and columns which meet the
specified conditions.
Data you want to
use as criteria for
filter

=FILTER('Form Responses'!A2:G43, 'Form Responses'!B2:B43=B1)

Data you’d like to filter

Cell
Cell that
that IDs
the
controls
criteria
for
thethe
filter
count

 IMAGE
The IMAGE
function will
allow images to
be inserted into
cells with several
options.

=IMAGE(URL,mode)
Web address
of image to
insert

The following
mode options are
available:
1 (default) resizes image,
maintains aspect
ration
2 - compresses
image
3 - original size,
Cell that IDs
cropped
the criteria
4 - custom
size
for the count

  

You are now a Google Sheets
Jedi Master!

 

Ennis:
?2 I

 

You can ?nd me:
Twitter/Instagram: @jlubinsky Email: jlubinsky?gmaiLcom

 

 

SKETCHES ON

J1 1:1

11:1 

THE TRID

 

Pre-session music: ?Sketches on Star Wars" by The Trotter Trio

CREDITS
Special thanks to all the Google educators whose work made
this presentation possible:
⊡
⊡
⊡
⊡
⊡
⊡
⊡
⊡

Denise “D-Nice” Wills
Samantha Morra
Josh Harris
Dawn Kasperson
Brian Scheibach
Scott Lotze
Nancy Dressel
The entire EdTechTeam!

Presentation template by SlidesCarnival