Google Forms: Formative Assessment Tips

Google Forms Google Drive

If you use Google Forms for formative assessment quizzes here are some tips for managing the resulting spreadsheet.

Ask for Student ID Number (SID)

When organizing data it is important that your data match. Trying to sort data by student name can be sketchy when students type in their own name. They might use “Robert” one day and “Bob” another. They may simply misspell their name. While I do not need the students ID number it is harder for students to get creative in how they fill it out. This makes it much easier to sort and organize the results.

First Name

Rather than ask for “name” make 2 questions. One for “First Name” and one for “Last Name.” This way you can easily sort the resulting spreadsheet by either first or last name. This is also very handy when putting the results in the gradebook.

View Responses

From the edit screen of the Google Form you want to “View Responses” in the toolbar in order to open the spreadsheet of data. (The first time you will want to leave the defaults in the pop up window and click on “create.”)
view responses google sheets

Answer Key

After creating your Google Form go to the live form and take the quiz. For first name put “Answer” and for last name put “Key.” Fill out the form with the correct answers. This makes the first line of data in the spreadsheet the correct answers.

Freeze Bar

By default the first row of the spreadsheet is frozen. This allows you to scroll through all of the student answers and still see the questions. If you were the first person to answer the Google Form quiz and you used “Answer Key” for the name, then the second row contains the answers.

On the left hand side, between rows 1 and 2, hover over the freeze bar. You will notice the cursor turns into a hand. Click down on the freeze bar and drag it down to freeze the first 2 rows. This will freeze the answer key at the top of the spreadsheet. As you scroll through the student answers you can compare their answer to the answer key easily.
freeze bar



Hovering over the column indicators will reveal a small drop down arrow. Click on this to choose to sort the sheet. You can sort the results by Last Name, Period, etc…
sort sheet

Sort each question. This will allow you to cluster which students answered what. Since the results of a Google Form are instantly in the spreadsheet as students submit you are able to quickly identify which students answered each answer choice for a particular question. Divide up the students by who answered what to provide a differentiated activity.

Multi – Column Sort

You probably want to sort by multiple columns. Such as by Period, Last Name and then First Name. There is a blank box to the left of column A and above row 1. I call this the “awesome box.” Right click on the “awesome box” to reveal a list of options for the entire spreadsheet. Choose “Sort range…”

awesome box


Check the “Data has header row” checkbox. Choosing “Add another sort column” allows you to sort by multiple columns at once. The order matters. If you want to sort by Period and then Last Name you need to sort by Period first and Last Name second.
sort range google sheets

Conditional Formatting

Conditional formatting allows you to color code the cells based on the answer. For example if the student put down the correct answer the cell background color will be green. If a score is less than 70% you can have the background color be red. This allows you to easily identify trouble areas and find selected answers quickly.

Click on the column indicator of a question to highlight the entire column. Right click on the column indicator and choose “Conditional formatting.”
conditional formatting google sheets

There are a variety of ways you can test the data for the conditional formatting. If you have an exact answer you are looking for choose “Text is exactly” from the drop down menu. If you are testing numbers you may want to choose something like “Greater than.”
conditional formatting

Choose the formatting options that will display if the cell has that value. You can add multiple rules.
add another rule conditional formatting

Example of Conditional Formatting

Conditional Formatting

Posted in forms, Google, Spreadsheet | Tagged , , , | Leave a comment

Google Drive: Do NOT Share Everything in a Folder

Folder Image by Ken Hess

Folder Image by Ken Hess

One trick for collaborating using Google Drive is to create a folder in Google Drive and to share the folder. Anything you put into the folder will automatically have the same sharing permissions as the folder.

Sharing a Folder

Previously I had blogged about how to share a folder in the new Google Drive. Create a folder, click on the share icon in the toolbar, add permissions.

Sub Folders

You are able to create a folder within a folder. Go to the folder in Google drive and click on the “New” button to create a new folder. This folder will be nested inside the first folder. Usually by default a folder is private; however, the sub folder will take on the same sharing characteristics as the parent folder.

This is also true for any documents you put into the parent folder.

Organizing but Not Sharing

Occasionally I have need to have files in a shared folder to NOT be shared with my collaborators. Perhaps I have some brainstorming notes or something else that is related to the project but not intended for others to see.

Peer Evaluations

This is especially true when I am working with students. I want to create a folder for peer evaluations. I set the folder to anyone can edit which allows the students to comment on the work of other students. I use a Google Form to have students provide feedback and to give me data to analyze about the peer evaluations. I want to save the form and the spreadsheet in folder with student work, however, I do not the students to have access to the spreadsheet.

Change Sharing Settings

While any documents or folders within the parent folder automatically take on the sharing settings, these can be modified on a case by case basis.

Within a document click on the blue share button. The pop up window will indicate who the document is shared with, clicking on the text that says “Shared with…” or on the word “Advanced” in the bottom right hand corner will open the sharing window.
click on these links

The advanced sharing window allows you to change editing rights or REMOVE collaborators from a document. Next to each persons name is a drop down menu to change from “Can edit” to “Can comment” or “Can view.” Remove the collaborator all together by clicking on the “X” on the right hand side.
remove or change permissions on sharing google docs

Document Level Permissions

Each document has it’s own sharing settings. Placing a document into a shared folder does not permanently alter the documents sharing settings. Moving the document out of the folder will restore the documents previous share settings. This allows you to temporarily allow access by some collaborators by placing documents into a folder. You are then able to easily restrict access again by taking the document out of the folder.


Let’s say I have a document that is privately shared with person A and person B.
privately shared document

I have a folder that is set to anyone with the link can view and is also shared with person C, D and E.
share folder anyone with the link can view

If I move the document into the shared folder, it will be shared with person A, B, C, D, and E and will now have the sharing permission of anyone with the link can view.
Anyone with the link can view

I can remove editing rights from person B and C from the document by clicking on the blue share button in the document and removing their individual permissions.
Remove permissions

If I take the document OUT of the folder. It will go back to being privately shared. It will NO LONGER be shared with person D and E. Since at the document level I removed permissions for person B they also will no longer have access.
Untitled drawing (4)

Posted in Drive, Google | Tagged , , , | 1 Comment

iPad in Physical Education Bootcamp


I believe Physical Education is an important part of schools. Being an expert in EdTech, I will occasionally encounter a comment along the lines of “You can not use technology in PE.” I know many excellent PE teachers who are not only using technology in their classes, but are doing amazing things with it.

Fellow Google Certified Teacher, Jarrod from Australia, has an amazing website ( with uses for technology in the Physical Education classroom. Jarrod has been a big help to me on several occasions. He is also the creator of the Move-It Chrome extension that I use everyday. You can follow Jarrod on Twitter @mrrobbo.


The PE Geek is offering a FREE bootcamp in using iPads in PE. You can sign up by going to the PE Geek website:

Posted in iPad | Tagged , | Leave a comment

Google Classroom Deployment: Advice from David Malone

If your school is considering using Google Classroom, or is already using Google Classroom, here is some advice from David Malone on managing Google Classroom from the administrative perspective.

Posted in Classroom, Google | Tagged , | 1 Comment

10 Reasons to Introduce Google Classroom to Blackboard

Posted in Classroom, Google | Tagged , , , | Leave a comment

Sharing in New Google Drive: Get Sharable Link

get sharable link

The new sharing interface of Google Drive shows you an option to share explicitly with certain people. If you are looking to share with a wider audience, click on “Get shareable link.”


Clicking on the link automatically switches the sharing settings to anyone with the link can view. This is great news for students and teachers who are less practiced with using Google Docs.

Copy Link

The link is automatically copied to the clipboard. The only next step is to select done.
sharing google docs anyone with the link

Posted in docs, Google | 1 Comment

Create a Unique Google Forms URL for Each Student



One of the presentations I did in the Google Booth at Educause this year was on using concatenate for a spreadsheet. While you do not need to know the word concatenate to do this trick, it is wickedly useful.

Data Disaster

One of the best tools in the Google Apps suite is Google Forms. It allows any user on practically any device to fill out a Form. The user does not have to be logged in, have a Google account or even know what Google is. The data collected from the Form can be viewed in a spreadsheet and manipulated any number of ways. It is a beautiful thing.

The problem is the end user. When you let other people enter the data they can get creative or make mistakes in how they enter information. When sorting and organizing information, consistency of the data is very important. As much as possible I try to use multiple choice or choose from a list when users fill out forms to force the response options to be consistent.

Unfortunately there are fields you would not typically want to make multiple choice. Name, ID number, email address, etc… the person filling out the form has to put this information into a text field.


Google Forms allows you to pre-populate the fields of the Form. This means you can create a unique URL that when someone clicks on that link some of the boxes will be filled out already. Click Here for an example.


If you use the same Google Form repeatedly, such as for a daily warm-up, each student can have a unique URL that automatically pre-populates their SID, First Name, Last Name and Email Address.

For peer evaluation where students have to fill in the project title and name of another student, having a unique URL will allow the students to go straight to the rubric and bypass filling out the text fields.

When putting on a professional development (PD) conference event the same evaluation form is used for each session, each presenter. Rather than relying on the end user to enter in the session title, presenter, room number, session slot, etc… these can be pre-populated so the person filling out the form only needs to rate the quality of the presentation.


  1. Create a Google Form.
  2. In the edit screen under the Responses menu is an option to “Get pre-filled URL.”
  3. Fill in the fields you want to be pre-populated with place holder data.
    Example: LASTNAME in the Last name field.
  4. Click submit.
  5. Copy the unique URL provided.
  6. Locate or create a spreadsheet that contains the information you want to pre-populate.
  7. In the blank column next to your data type an equals sign, quotation, and paste the URL between quotation marks.
  8. Locate the placeholder data in the URL.
  9. Highlight the placeholder data.
  10. Type “&&”
  11. In between the ampersands (&) place your cursor.
  12. Type the cell reference for where in the spreadsheet that information is located.
    Example: “&A5&”
  13. Fill down in the spreadsheet by holding down the corner of the cell with the formula you just created and pulling down. This will create a unique URL for each row in the spreadsheet.

Educause Slides

Posted in forms, Google | Tagged , , | 6 Comments

Google Classroom: Grading with Drive’s Preview Mode

google classroom preview mode

Google Classroom collects the students work for an assignment in a folder in Google Drive just for that assignment. This means that you are able to find all of the documents for students in a single place easily. Especially at the high school level, the process for opening and commenting on each document can be cumbersome. Here is a potential workflow:

Locate the Assignment in Classroom

In Google Drive you are able to easily locate the folder that contains the students assignments by clicking on the “Folder” button in the “Assignment Status.”
google classroom assignment status folder

Two Tabs

Notice this opens a second tab with the Drive folder.
two tabs

Use the mouse to click and hold down on the Google Drive tab and pull down. This will pull the Drive folder into a new window. Resize Google Classroom and the Google Drive folder to be side by side.
Google Classroom 2 tabs

Google Drive Preview

In the Google Drive tab click on the first assignment to reveal the preview eyeball in the toolbar. This will launch preview mode.

Use Arrow to Cycle Assignments

On the right side of the preview is an arrow to cycle to the next assignment. Alternatively you can use the right and left arrows on your keyboard.
Google Drive Preview

Put Comments into Google Drive

As you cycle through the students documents in Preview mode you can insert the grade and comments into Google Classroom.


Posted in Drive, Google, Grading | Tagged , , | 2 Comments

Editing Creative Commons YouTube Videos

Creative Commons is a type of copyright license that allows others to utilize the work, with credit! I am a big advocate of teaching our students about copyright and to ensure that we use images and works with a Creative Commons license to set a good example. Click Here for more information on Creative Commons.

Set Creative Commons

By default your YouTube videos are under the standard YouTube copyright license. I recommend that you go into the video manager and set your videos as Creative Commons. This allows you, and others, to edit the video. Perhaps you have an awesome 20 minute lecture on microbiology. There may be a 2 minute segment that is particularly interesting that you, or another teacher, would want to share with the students. Setting the video to Creative Commons allows you to trim a copy of the video to that exact 2 minutes.

Profile Photo

Click on your profile photo in the upper right hand corner of YouTube. This will allow you to choose “Creator Studio.”
Alice Keeler YouTube Creative Studio Video Manager

Video Manager

On the left hand side of the Creator Studio is the “Video Manager.” Choose Videos to see a list of your videos. Videos is the default stop when clicking on Video Manager.
YouTube Video Manager


I check the checkbox next to the “Actions” button to select all of my videos. You can individually checkbox the videos you want to set to Creative Commons if you prefer. After selecting the videos, click on the “Actions” button. Choose “Creative Commons” from the drop down list.
youtube check actions creative commons

Search Creative Commons

To find Creative Commons videos do a search in YouTube. After searching, click on the “Filters” button above the search results and choose the feature “Creative Commons.”
youtube search filter creative commons

Remix Video

You are able to remix YouTube videos with a Creative Commons license. This means you can shorten the clip, mash it together with other Creative Commons YouTube videos, add text, add music and other effects.

Choose your Creative Commons video on YouTube. Underneath the video click on where it says “Show More.”
SHow more youtube


Choose the “Remix this video” option under the License.
youtube remix this video



You can go straight to the YouTube video editor from the Creator Studio. On the left hand side of the Creator Studio underneath “Create” is “Video Editor.”
Youtube video editor creative studio


Search Videos

The first two icons next to the video player shows (1) YOUR videos that are set to Creative Commons (2) YouTube search for Creative Commons videos.
YouTube Video Editor Search Video

Creative Commons

Click on the CC icon to perform a YouTube search. Only Creative Commons videos will be returned.
creative commons search youtube video editor

Drag Videos

Drag videos from your video library or from the Creative Commons search to the timeline below. You can drag multiple videos to mash them together.
Drag YouTube Timeline

Trim Video Length

Click on each video in the timeline and blue bars appear on the edges. Drag the left or right blue bar to trim the beginning or end of the video. If you need multiple segments from the same video you are able to drag the video multiple times into the timeline and trim each one differently.
Google Video Editor Trim Videos youtube video editor

short time

Classroom Use

There is a lot of good information on YouTube to share with students but the videos can be quite lengthy. Using the remix option allows you to direct students to the exact parts of a video that are pertinent.

Even if you want the students to watch the entire video, occasionally the speaker has some dead time, slow parts, or segments that can be taken out. Editing the video helps keep students attention and to have more time in class to discuss the video. Drag the same video clip multiple times to the timeline and edit to be a collection of segments.

Mash student videos together and trim to create a trailer to allow each student to be a star.

Place text on the screen to help direct student attention to what is being spoken.

Posted in Google, YouTube | Tagged , , | 1 Comment

Google Classroom: Numbering Assignments

google classroom folders

Previously I had blogged on assignment naming conventions for Google Classroom. The more I use Google Classroom the more I am liking numbering my assignments. I have numbered my assignments for years. When attempting to enter grades into the gradebook it is easy to find where to input assignment #012, it is next to #011. No more scrolling through and overlooking an assignment. Numbering assignments also makes student conversations about work significantly easier. “Joe, you are missing assignments #013, #027 and #067.” It is easier to jot this on a piece of paper and solves some confusion problems about which assignment we are talking about exactly.

Google Classroom

When you make an assignment in Google Classroom it automatically creates a folder in your Google Drive with the same name. If you have your Drive set to organize by name, they are sorted alphabetically rather than the order you numbered it. Google Classroom does make it easy to locate the folders. Within the assignment status screen there is a button to launch the folder in Google Classroom. folder

However, if you want to look directly in your Google Drive the organization can be really nice.

3 Digit Numbers

I recommend you start each assignment with a 3 digit sequential number. The way a computer “alphabetizes” numbers clusters 1, 11 and 110 together and 2, 22 and 210 together. Always using a 3 digit number will solve this problem.

Assignment Title

Start the assignment with #001, #002, etc… number assignments google classroom

Assignment Resources

To help students clearly identify which resources go along with which assignment, start the document titles with the assignment number also. google classroom number resources

Posted in Classroom, Google | Tagged , | 4 Comments