Show All Comments in Google Sheets

The ability to comment on cells in Google Sheets is super useful.  The ability to find those comments, however . . . pretty stinkin’ difficult.  That little yellow triangle in the corner just ain’t cutting it.  In a big spreadsheet, it can be easy to miss some comments.

You can show all of the comments in the currently open spreadsheet tab by either hovering over or clicking on the comments icon on the sheet tab at the bottom.  Clicking keeps them open while you move your mouse around or scroll.  If you hover, the comments are hidden again as soon as you move your mouse.

Show All Comments in Google Sheets Animation

CopyDown Add-On

Years ago, as a STEM teacher, I had my students build basswood bridges.  We’d then test them by hanging weights from them.  I’d submit the results to a Google Form, which would kick it to a Google Spreadsheet, where a formula was all set up to calculate the “Engineering Efficiency” (a measure that leveled the playing field between heavy, strong bridges and light, strong bridges).  Unfortunately, formulas don’t automatically apply to the new rows created by new Form Submissions. I had to have a student manually drag the formula down each time a new result was submitted.

Enter the CopyDown Add-On

I later discovered this wonderful little add-on.  It automatically pulls that formula down to a each new form submission.  No manual dragging necessary.  This is super, super useful when your Form & Sheet are part of a bigger system that triggers other actions in other add-ons (i.e., autoCrat, formMule) that require those formulas.

Here’s a GIF of how it works, followed by a step-by-step guide to using it:

CopyDown Add-On Animation

  1. Set up your Google Form.
  2. Open up the connected Spreadsheet.
  3. Start with an initial form submission.  You’ll need this in the next step.
  4. Create your formulas in Row 2 (the row with your first submission).
  5. Click Add-Ons and follow the steps to add CopyDown.
  6. Click Add-Ons > CopyDown > CopyDown Settings.
  7. Flip the switch to “On.”
  8. Generally, I select to paste the results “as values” (otherwise it puts the formula itself into each cell which, if it’s a lengthy spreadsheet, will ultimately slow it down).
  9. Save Settings.
  10. Start gathering form submissions!

How Many Hot Dog Topping Combinations?

It was 6:12 PM EST.  We were eating dinner on our deck.  My sister messaged me.  She had a very important question.  Her and her colleagues were in a heated debate.  Just how many topping combinations were there at Cleveland’s fun hot dog restaurants Happy Dog?  I know, right?  This is a big deal.  Could I swoop in and save the day?  Yes.  Er, well, with the help of my trusty sidekick Google Sheets I could.  (Excel would have worked, but what if I need to access the calculations on the go?  or share them?  Yup, I made the right choice.  gSuite’s trusted cloud-based spreadsheet is the way to go here.)

So, I got the details.  There are 50 toppings possible.  No limits (you can do all 50, as my oldest son might choose) or minimums (0 toppings, as my youngest son prefers them, counts too).  Variations on the dog (veggie?  black bean!?) or bun (bleck, wheat?) were to be ignored.

I set right to it.  I picked a trusty Google Sheets formula – Combin – and got to work.  That formula deals with a common mathematics formula that finds the number of combinations of something.  You need only know two things – how many possible things and how many are to be chosen (i.e., 50 toppings choose 1, 50 toppings choose 2, etc.).  Now, don’t get this mixed up with permutations where order matters, because no one cares if you go peanut butter, sriracha, alien relish or alien relish, peanut butter, sriracha or … well … you get it.

COMBIN(nk) where n is the size of the pool of objects to choose from and k is the number of objects to choose.

The rest is history.  Check it out in the GIF below.

Oh yeah, I almost forgot to tell you the answer: 1,125,899,906,842,620 – one quadrillion, one hundred twenty-five trillion, eight hundred ninety-nine billion, nine hundred six million, eight hundred forty-two thousand, six hundred twenty combinations.

Side note to math teachers: I love how the numbers are symmetrical (i.e., there are 1,225 different 2-topping dogs and 1,225 different 48-topping dogs).  Could be a great discussion with math students.

Now, here’s how I did it:

Happy Dog Combinations Animations

Moving Stuff Out of your “Shared with Me”

Every household has a junk drawer.  And, for most Google Drive users, they have two: My Drive and Shared with Me.  Everything is in there.  Today, let’s focus on how to clean up your Shared with Me.

Here are 4 tips about cleaning up your Shared with Me, followed by a GIF displaying them:

  1. If there are files you are 100% sure that don’t want, go ahead and delete them.  You’ll still technically have access to them, but you won’t see them in your Shared with Me anymore (so good luck finding them).  The original sharer will have no idea that you removed them and it won’t affect them (because you’re not the owner).
  2. You can click Add to Drive to move files from your Shared with Me to your own Drive, where you can then organize it.
  3. You can drag & drop files from the Shared with Me to anywhere in your Drive to organize them.
  4. Once you’ve moved files into your Drive, you can delete them from your Shared with Me and they will stay in the location that you put them.

Move from Shared With Me Animation

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