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

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.