Tag: Fun with Google Sheets

Google Sheets

Fun with Google Sheets, Part 4

Mandi

I’m back today with more tips and tricks to make Google Sheets fun and amazing.

Filter by Color

If you have set up your sheet to change colors using conditional formatting (Fun With Sheets 2), then you can filter by color to see entries that are a certain color.

Highlight the cells you want to sort, then select the funnel looking tool on the right of the toolbar. Select Create new filter view.

The view changes and these little upside down triangles show up on the first line.

I’m going to click the triangle on House to filter.

I selected dark red 1 to get my Gryffindor students.

*Note that you can also filter by text color if you prefer colored text instead of colored cells.

A now I have just my Gryffindor students.

Hiding Cells/Sheet

If you’ve been following along with the Google Sheets activities I’ve been creating, then you may already know these tricks. I use them in my activities all the time.

Hiding Cells

Sometimes you don’t want part of your cells to show. Maybe you have some calculations happening, maybe some answers. You can hide those sheets so students can’t see them. Now, a tech savvy student might notice, but we will get to that in the next step.

Highlight the rows you want to hide (works for columns too), right click and select hide rows.

Once the rows (or columns) are hidden, you can see the arrows indicating there are hidden cells.

Hide Sheet

You can also hide an entire sheet from view. If you create an activity and put the images and answers on Sheet 2, you don’t want students to be able to see that.

Once the sheet is ready, right click and hide sheet.

Protect Cells/Sheets

Once you have hidden your cells and sheets, you want to prevent tech savvy students from unhiding them. You can protect these so you are the only person with access.

Highlight the cells you want to protect, right click, and select protect range.

You can do the same thing with the sheet, by right clicking on the sheet and setting protection.

The only caveat with this is once a student makes a copy, like they have to do when they solve a puzzle, the YOU in “only you can make a change” is THEM. So they can now see your Sheet 2 and hidden cells if they know how to unhide. For this reason, I like to make my font color white so it disappears and I place the answers where they have to scroll a lot to find them. Is it perfect? No. But these activities are fun and work well for digital escape rooms or review so I will continue to make them.

I hope this series is helping you embrace the amazingness that is Google Sheets!

See previous Fund with Google Sheets posts:

One

Two

Three

Digital Escape Rooms, Distance Learning, formative assessment, Geometry, Google Sheets

Fun with Google Sheets – Part 3

Mandi

It’s been a minute since we had a fun with Google Sheets so I decided to bring you another installment as school is starting or getting ready start for most of us.

Separate Names

I like to copy the names of my students from my gradebook screen or download an Excel file of students from our grade management system (we use PowerSchool). This copies first and last names but sometimes I just need first names. I DON’T want to go through a delete all of the names, especially since I usually have around 150 students.

Never fear, Google Sheets are here! You can EASILY separate the names into two columns.

Begin by typing the names into two columns, just as you want them to appear. It may take a few names, but once Google Sheets understands what you are doing, Smart Fill will pop up a suggestion. Click the checkmark and Sheets will do the rest.

You will do the same for the last name column. Then you have the entire sheet broken into first and last names. YAY! Such a time saver.

The process also works in reverse. So if you have first and last names in two columns, you can type both in the third column and smart fill will recognize the pattern and fill for you.

Google Translate

You can translate a list of words in Google Sheets. Type the list of words in one column. In the next column, type =googletranslate and and prompt will appear.

Next you want to click on the cell for the word you want to translate. Don’t worry, we don’t have to do this for every word, it will fill all of them in the end. It will place this cell into the formula, then put a comma and “auto” then a comma.

Now you need to find your two letter language code. These are called ISO codes and you can do a google search for them. Here is a link to the site I use. I’m translating to Dutch which is the code nl. So I will add the code in quotations and then close the parentheses.

One word is translated.

Now grab the blue box in the bottom right corner of that cell and drag down. You have translated all of the words.

Hiding Answers

I love to create interactive Google Slides for review games and escape room tasks. Most of the time the game works as it should. But every now and then a student figures out that you can click on the input box to see the answer. And, OF COURSE, they tell the rest of the class and now they are just entering the answer and not working out the problems.

Here is an example of a pixel art activity where this could be used. Many of mine have not been updated to this cell reference method but I will be updating them as I use them this year.

In this example, the student can see that typing 18.1 into D3 will give them the correct answer.

You can make this process more difficult for them. Using sheet 2 (click the + sign next to sheet 1 in the bottom row), you can type the answers into cells and then reference these cells on sheet 1.

I started in T33 to type my answers so it wasn’t on the screen if students click Sheet 2. You can also hide this sheet or password protect it so students can’t see it. If you do that, then just begin in A1 with your answers.

Replace 18.1 with Sheet2! followed by the cell where the answer is located, T33 for this example.

Now when a student clicks on the answers, they would have to unhide (if it’s not password protected) Sheet 2 then find T33 for the answer. I don’t usually password protect and it still deters most students.

Well, I hope you enjoyed this episode of Fun with Google Sheets and I hope you have a great start to your year!

Google Sheets

Fun with Google Sheets, Part 2

Mandi

I am back with another installment of Fun with Google Sheets. Let’s dive in.

You can also visit Part 1 here.

Drop Down Menus

I use this when I’m creating my own data sheets but you could also create this in an activity because conditional formatting will still work with it.

Select the cell to place the drop down menu.

Click Data and then Data Validation.

Under criteria, select list of answers.

Type in your selection with commas between.

Click save

Conditional Formatting

This is my 100% absolute favorite thing to do in Google Sheets. It’s so simple and makes you look like a rock star. I use this in my data tracking sheets with students and the many activities I create from escape room to pixel art.

Insert Images

When Google updated Sheets to include images in cells, I was so excited. Prior to that, the image just floated over the cell and it was somewhat clunky! Now you can do both. I use this with activities, escape room, and the it was very handy for the surface area and volume calculator I created.

Now go have some fun with Google Sheets. I will be sharing more Fun with Google Sheets in the coming weeks.