Spreadsheet – 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.

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!