Concat, Concatenate and &

Nope.  They have nothing to do with cats.  Sorry.  If you’re here for the cat memes, you’re going to have to look elsewhere.

They are two Google Sheets formulas that are very useful if you’re organizing information in Google Sheets.  Have first names in one column and last names in another, but need them combined?  Concat has your back.  Have a student’s grade number, but need it to be written out (7th grade instead of just 7) for a mail merge?  Concat is here to help.

Need to combine more than 2 things?  i.e., first, middle & last name?  Concatenate can help you out.  It’s the same as Concat but it works for more than 2 items.

The & operator can perform the same tasks.  It’s all personal preference.

Some tips are listed below the GIF.

Concat & Concatenate Animation

  • Basic format: =CONCAT(first thing, second thing)
    • =CONCATENATE(first thing, second thing, third thing, fourth thing….)
    • =first thing & second thing & third thing
  • The items in the formula can be cell references like here: =CONCAT(A1, B1)
  • The items in the formula can be regular text, just use quotation marks: =CONCAT(“me”, “ow)”.
  • When combining text strings, it puts them together with nothing in between them, so if you want a space, you’ll have to add it yourself – use ” ” if needed – the space is held between those quotation marks.

Force Make a Copy

Thou shalt make a copy. – Jake Miller

Ok, so, I never said that.  Well, actually, I guess I just did.  Anyhow, it’s a trick that’s known in most edtech circles, but it’s useful enough to make sure that everyone knows it:

Change the “/edit” or “/view” (or whatever) at the end of a Google Apps file’s URL to “/copy” and it will force the person clicking the link to make a copy of it (as if they had clicked File > Make a Copy).

Important: make sure the doc is shared, at least as “Can View,” prior to using this.  You can’t copy a doc that you can’t view!

With the rise of Google Classroom and other LMS options, it’s not as useful as it used to be, but it has its use cases: sharing a resource on your website, posting forms for use in your school district, sharing optional activities for classes or clubs and much more.  It works in Drawings, Sheets and Slides as well! Here’s how to do it:

Force Make a Copy Animation

Just in case, here are those steps:

  1. Share the doc as “Anyone with the Link Can View.”
  2. Copy the link to the doc.
  3. Change the “/edit” or “/view” or “/edit?usp=sharing” to “/copy

FormRanger Add-On

I love me some Add-Ons. One of my favorites is FormRanger from New Visions Cloud Lab. It can be used to pull in a column of information from a Google Sheet as multiple choice or dropdown options.

This is nice for quickly creating a lot of options for a multiple choice or dropdown question, but what takes it from nice to awesome is  . . . you can set it to automatically update based on changes made to the spreadsheet. Whaaaaat!?  I know, right?

There are two main cases for use: Continue reading FormRanger Add-On

FILTER Formula in Google Sheets

You can filter using menu buttons and create filter buttons in Google Sheets, but sometimes it’s valuable to setup a FILTER formula.  One such instance is shown in the GIF below: when you have a Sheet that contains data about many students across many grades or classes.  Using a filter formula, you can create a tab for each class or grade.  You can also create tabs for certain criteria (like lower scores that require follow-up).

=FILTER(range, condition1, [condition2, …])

Note from the formula above, that you can actually identify multiple criteria (such as Mr. Kotter’s students who scored below a 75%).

Filter Formula (Google Sheets) Animation

Exploring the Number pi with Google Sheets & Forms

What better way to celebrate Pi Day than with a hands-on, tech-on exploration activity that helps students build their own understanding of what pi really is?  Well, probably a good piece of pie, but this is awesome nonetheless.

Here’s what you do:

  1. Get a bunch of fabric tape measures (using string and then measuring lengths on the string works too).
  2. Get a bunch of circular objects.
  3. Have kids measure the circumference and diameter of different circular objects.
  4. Instruct the kids to submit their measurements to a Google Form
    (note: my form doesn’t collect names, but it would be best to collect them so you can help kids who have measurement errors).
  5. Setup a QUERY formula to find the circumference/diameter for each entry.
    =QUERY(B2:C1000, “select B/C”)
  6. Fix that pesky 2 in the Query formula after the first submission – when the first entry inserts a row, it changes B2 to B3.  Change it after the first entry and you’re good to go.
  7. Setup an AVERAGE formula to find the mean of the circumference/diameter calculations.
  8. Project the spreadsheet as entries are recorded.  See what your kiddos notice about the numbers they see on their screen!

Locate your Collaborator by Clicking on their Icon

Wait, what page are you on?
I’m confused.  What slide are you referring to?
Ugh, what cell are you in!?

GSuite’s tools make collaboration–both between adults and between students–a piece of cake, but it can still be tough to keep up with one another, especially in lengthy Docs, Sheets or Slide decks.  Did you know that if you click on their icon it will jump you right to their location?  You’re welcome.

Check out the two GIFs below . . .

Click on Collaborator Icon to Locate Them Animation


Searching Google Drive by Owner or Shared To

Sometimes you know who you collaborated on a doc with, but just can’t come up with a search that leads you to that doc.  Why not use their email address to track down the doc?

Searching Drive by Owner or To Animation

I discovered this search term when I was asked to track down all interactions between two specific students.  This gave me the capability to see all docs on which they had communicated, provide them to our administrator and delete the docs from the student accounts.

7 Ways to Make the Most of Your Chrome Bookmarks Bar

1. Folders

It’s nice to have easy access to lots of sites, but that bookmarks bar can get crowded.  Use Bookmark folders on your bar to categorize them while still having convenient access.

2. Shorten those bookmark titles.

Shorter bookmark titles take up less bookmark bar space. Take the title out to just use the sites logo. If the site doesn’t have a logo, or it doesn’t make the destination clear (like a docs logo), use short words or even emojis to save space!

3. Create Bookmarks for Creating New Docs or Slides

Did you know that opens up a fresh new Doc?  Or that does the same with Google Slides?  Create bookmarks for those links and have quick access to that capability.


4. Different Bookmarks for Different sections of your Drive.

Do you go to your Starred files often?  Need quick access to Shared with Me when someone sends you a file in a meeting?  Do you have a folder for all of your students’ assignments that you go to daily?  Make a special bookmark for different locations!

5. Different Bookmarks for Different Parts of Docs, Slides or Sheets

Different tabs in Sheets, Headings in Docs and slides in Slides have different URL’s.  That means you can make your bookmark (or a link you send in an email or message to someone) direct you (or the recipient) to a specific spot.  It’s nice when you want to send someone to today’s meeting agenda in the massive Doc with all meeting agendas in it.  It’s also super convenient if you regularly access a certain spreadsheet tab.

6. Bookmark specific sections of GMail

Have a certain GMail label you access regularly?  Want quick access to your starred or important files?  Want to be able to get to emails from your admin or boss quickly? Create a bookmark for that exact part of your Gmail.

7. Bookmark specific Calendar Views

Want to be able to access Day, Week, Month, Agenda or a Custom View quickly?  Make it a bookmark.


(Ctrl +) Shift (+ V) Happens

Use Ctrl Shift V to make pasted text match the destination!

We’ve all pasted something from a website into a doc, presentation, email or other destination before and experienced that annoyance when it doesn’t match your other font.  Fixing this is simple…  Just add shift to your ctrl+V keyboard shortcut to make your text match the destination font (including size, color, spacing and all style options).

Note: I’ve always been a little apprehensive about sharing this with students, because there’s no easier way to identify a plagiarizing student than a mismatched font with white highlighting . . . 

Exploring Measures of Center with Google Sheets

My obsession with Google Sheets is no secret. I loooove spreadsheets.  And I think that they have a big place in education, especially in math (but elsewhere as well).

Recently, I posted about how you can prove the mean (or average) formula using Google Sheets.  In this post, I’d like to share with you how you can find all 3 measures of center (or measures of central tendency) and explore them in Google Sheets.  I love to change or add numbers in the data set and ask students to make predictions about what will happen.  It really is a great–and relevant–way for students to become more familiar with these statistical measures.