GoogleSheets – 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

#EduDuctTape Episode 38!

In the 16th episode of Season 2, I am joined by Joe & Kristin Merrill, 1st & 4th grade educators & authors of The InterACTIVE Class, to talk about making our classrooms and our students’ learning experiences InterACTIVE. We discuss Apple Clips, Buncee, Seesaw, BookCreator & Flipgrid. Also, a game of edtech BFF!

See the Show Notes Here

“Interactive Learning is learning that is responsive and relevant, that engages students.” — Graphic created by Matt Meyer @54Mr_Meyer

Video by David Allan (@_david_allan)

#EduDuctTape Episode 37!

In the 15th episode of Season 2, I am joined by Andreas Johansson to talk about things that we can do to support less tech-savvy staff members, especially non-teaching staff. Tools discussed include Google Forms, Sheets & Sites; the FormMule, AutoCrat & FormRanger Add-Ons; the VLookUp, Concatenate & Substitute Google Formulas; Lean Thinking and more!

See the Show Notes Here

BONUS CONTENT: This audio was not included in the regular podcast episode, but is available here for listening:

Andreas Johansson: "I have the fortunate now to work with a lot of what's called classified staff, or as I think of it, the other 50% of a school district. — GIF created by David Allan.

Video created by David Allan.

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.

Translate in Google Sheets

Living in a new country where a different language is spoken is something that I can barely fathom. Doing it as a child just blows my mind. Our schools are often ill-equipped to support these kiddos, but they often do have one powerful tool that can help: compassionate teachers.

One of these compassionate teachers once came to me looking to create a tool to support an ELL (English Language Learner) student. We both knew that we worked with a great ELL Tutor who was helping this student assimilate to the school . . . but what about the vocabulary that was being learned in the meantime?

His idea was to organize important English vocabulary words and their translations to the student’s native language into a spreadsheet. After he came to me with this idea, I started exploring options. What I discovered was really exciting! There’s a Google Translate formula in Google Sheets!

Enter a word in one language in a cell, and then use the formula =GoogleTranslate(text, source_language, target_language) in another cell to automagically translate it! Translating one word this way doesn’t save my time, but you can drag (or double-click) the fill handle at the bottom of the formula cell to apply this formula to more than one cell.

This formula appears to work for all languages supported by Google Translate, of which there are more than 100! It even outputs the results with the correct letters and alphabet–not just our ABC English letters. You’ll just need to know the 2-letter code for the language, which you can find in this list.

A note: as with using the regular Google Translate site, the results are not 100% accurate, but they’re close. This method is free, fast, and easy. There may be other options that can yield better results.

Check out how to do it in the animation below and then read the steps below the EduGIF!

Continue reading Translate in Google Sheets

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.

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:

Set up your Google Form.
Open up the connected Spreadsheet.
Start with an initial form submission. You’ll need this in the next step.
Create your formulas in Row 2 (the row with your first submission).
Click Add-Ons and follow the steps to add CopyDown.
Click Add-Ons > CopyDown > CopyDown Settings.
Flip the switch to “On.”
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).
Save Settings.
Start gathering form submissions!