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

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!

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:

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:

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).
You can click Add to Drive to move files from your Shared with Me to your own Drive, where you can then organize it.
You can drag & drop files from the Shared with Me to anywhere in your Drive to organize them.
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.

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.

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.