Sheets – Jake Miller

Filtering by Color in Google Sheets

Sometimes when I’m working on a project in Google Sheets, I shade cells a certain color to visually organize them. I’ve always wished that there was a way to just see the green ones. Or the red ones. Or the yellow ones. Sure, I could add some kind of indicator in a separate cell that I can filter by, but I wished that I could just do it by color.

Well, now I can! Recently, Google added the ability to filter and sort based on the cell color and the font color. Check out how it works with filtering in the EduGIF below! The step-by-step instructions are underneath the EduGIF.

This animated GIF shows the process by which cells in Google Sheets can be filtered by color. Step-by-step instructions are included in the blog post. — See the pausable version of this EduGIF here.

Before I share the step-by-step instructions, one last note. In the EduGIF, I did not share the process of sorting by color. When sorting by color, you’re selecting the color group (red, green, or white, in my GIF) that will come first. The other colors are then grouped after that. Within the color groups, the values will also be sorted in the default format (largest number to smallest number, in my data set). Continue reading Filtering by Color in Google Sheets

Font Formatting in Google Comments

Did you ever really, really, really want a student (or colleague) to understand your feedback on a portion of a Google Doc? Well, my friend, I have got news for you. Surround a word (or group of words) in a Google Docs (or Slides, Sheets, Drawings…) Comment with asterisks (*) and you’ve got bold text. 💥Boom💥 Surround them with underscores ( _ ) and you’ve got italicized text. 💥Boom💥

🤔❓Why does using the underscore lead to italicized text instead of underlined text!? I have no idea. Ask the Googs.❓🤔

Even more puzzling, there’s no option to create underlined text. But hey, 3 minutes ago, you didn’t know about how to do bold or italics, so calm it down, buddy.

Here’s the real head-scratcher: some people seem to want strikethrough text in a comment. 🤷🏻‍♂️ Why? I dunno. But it’s possible. Just surround your text with hyphens (-) and you’ve got strikethrough. Medium-half-excited-don’t-know-why-anyone-wants-this-feature-💥Boom💥. But again, 4 minutes ago, you were clueless that this was even within the realm of possibility, so turn your snark dial down, Francis. Anyhow, here’s a GIF. Please enjoy.

Dropdown List in Google Sheets

Did you know that you could add a dropdown list of options to a cell (or cells, plural) in Google Sheets? This is nice for creating something like a “multiple-choice option,” but is also nice when you want to force your collaborators (or yourself!) to select from a specific list of choices.

It can be especially useful if you have formulas acting on that cell. For example, if you were keeping track of a budget and wanted different things (i.e., adding versus subtracting) to happen if a row was marked as a deposit, purchase or interest.

Here’s an animated GIF about how to do it followed, as usual, by a list of the steps.

Step 1: Click on the cell or cells that you plan to add the dropdown list to
Step 2: Right-click & select Data Validation
Step 3: In the dropdown by Criteria, select List of Items
Step 4: Type your options into the box, separating them with commas (I tend to alphabetize them)
Step 5: Click Save.

Tab-Level Edit Rights in Google Sheets

In Episode 50 of the Google Teacher Tribe Podcast, Bailee Sandsmark, a 6th-grade middle school PE teacher, asked a Google Sheets question that got my gears turning. All that gear turning inspired a new #EduGIF from me. First, let’s look at her question:

I’d like to send out a Sheets template to all 250 of my students for them to individually track their fitness testing data, but then I would like to have an efficient way for them to share that info with me so that I can see all of my students’ data in one sheet. Having to access 250 different sheets makes my head spin…

While Matt & Kasey had a handful of good ideas of their own, I had another one that I wanted to share. It came from a thought that Matt shared: it’d be nice to give each student a tab in one spreadsheet, but then each student could edit the others’ tabs. That’s where “tab-level permissions” comes into play. If you click on the tabs at the bottom of your Google Sheet, there’s a Protect Sheet option. It’s also accessible from under Tools. As you’ll see in the Animated GIF below, you can use this to give tab-level edit rights to specific students.

Before we get the GIF, a few notes:

If each tab will be identical, you can duplicate the tabs.
If you’d like to create a tab for each of your students, you can use Alice Keeler’s Template Tab add-on.
You can also use this to give or limit edit access for just specific cells – I do this sometimes to make sure no one messes up formulas that I have running.
In Bailee’s, situation, she’ll still have the issue that each student can see their classmate’s information (even though they can’t edit it). To prevent this, you could give them code names or numbers.
The tabs that we’re referring to are technically called sheets, but I think that’s super confusing that the individual parts of Google Sheets are Sheets. What!?

And finally, the GIF:

Paint Roller Tool in Google Sheets

I’m a big fan of the Paint Roller (Paint Format) tool in the gSuite platform. I’ve posted before about using it in Google Docs, as well as in Google Slides. I probably use it most often, though, in Google Sheets. I love a nice, organized Google Sheet and this tool helps a lot with that. My favorite part about it is that it even applies to number formatting (i.e., decimal places, date format, currency, etc.). Check it out in the animated GIF below!

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.

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(n, k) 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:

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%).

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:

Get a bunch of fabric tape measures (using string and then measuring lengths on the string works too).
Get a bunch of circular objects.
Have kids measure the circumference and diameter of different circular objects.
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).
Setup a QUERY formula to find the circumference/diameter for each entry.
=QUERY(B2:C1000, “select B/C”)
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.
Setup an AVERAGE formula to find the mean of the circumference/diameter calculations.
Project the spreadsheet as entries are recorded. See what your kiddos notice about the numbers they see on their screen!