Home Tags Posts tagged with "Google Sheets Functions"
Tag:

Google Sheets Functions

Title image with 8 Google Sheets Tips listed
Google Tips and Tricks

Eight More Google Sheets Tips to Save You Time 

by Emily Horn
written by Emily Horn

It’s the end of March. That means it’s time for more time-saving tips for Google Sheets. Why? Because any time is a good time for Google Sheets tips! Right? Here are eight more tricks to try. Hopefully, you’ll learn something new that can help you on the daily. Enjoy! 

1. Create a Timeline

Did you know that you (and students!) can create timelines using Google Sheets? These can be used for history, project management, collaborative tasks, and more. Check out this quick three-minute video showing how to take Civil War battle information to create a timeline of the Civil War. 

2. Force a Copy of a Spreadsheet 

Want to let people use your spreadsheet as an editable template without giving them access to your actual spreadsheet? Change the word “edit” in the URL to “copy”, and share this link.

screenshot of a Google spreadsheet edit link
screenshot of a Google spreadsheet copy link

They’ll be prompted to make a copy of your spreadsheet for editing!

Screenshot of Make a copy prompt

3. Easily Create and Open a New Spreadsheet

If you’re looking for a fast and easy way to create a brand new spreadsheet, look no further. Simply go to the search bar and type “sheets.new.”

Screenshot of creating a New Spreadsheet

Your new spreadsheet will magically appear! 

Untilted spreadsheet tab

4. Translate Languages within a Sheet

You can translate the contents of one cell into another language using this formula: 

=GOOGLETRANSLATE(cell with text, “source language”, “target language”) 

Simply place the formula in the cell you want the translated text to appear, indicate the cell you’d like translated, and use the two-letter language code for the source language and the target language. In the example below, I place the formula in B1. I asked it to translate text from A1 from English (en) to French (fr).

Google translate formula in a google sheet cell

Et voila! You have your translation. 

example of English to French translation

5. Use Google Sheets Keyboard Shortcuts 

There are so many shortcuts to be taken advantage of in Google Sheets! Here are just a few of my personal favorites:

13 keyboard shortcuts for Google Sheets

6. Get Notifications of Changes 

Did you know you can get notified anytime a change has been made to your spreadsheet? You can! Click on Tools, Notification settings, and then edit notifications. 

Where to locate notification settings

Select to be notified when “Any changes are made” and then make your choice for a daily digest or an immediate email. 

Sheets notification rule choices

7. Protect Parts of Your Sheet and Make Other Parts Editable

Let’s say you want students to add to your sheet, but you don’t want anyone to edit your formatting! No problem. Right click on the sheet and select Protect sheet.

Sheet menu with the option to protect a sheet

Then, you can enter a description and select which cells and ranges you don’t want protected, which means they can be edited. Pretty cool!

protect sheets and ranges options in Google Sheets

8. Use Macros to Help You Complete Tasks in One Click

This one is truly amazing. You can literally record your screen (“Record a macro”) as you’re completing any task (averaging grades, formatting a sheet, setting up a calendar, etc.) within a spreadsheet. Then, instead of completing the same task step by step the same way over and over and over again to multiple sheets, you can just run the macro, and it will do the task for you. Mind. Blown. Take a look at this video example. 

Have any of your own tricks up your sleeve? Share them with us in the comments below. The more tips, the better! 

2 comments
1 FacebookTwitterPinterestLinkedinEmail
sheets
Google Tips and Tricks

Powerful Add-ons and Functions for Google Sheets

by Miguel Guhlin
written by Miguel Guhlin

Have you ever tried to cut a tree down with a hammer instead of a hatchet? If you know how these two tools work, you won’t even try. But that’s what we do with digital tools every day. Imagine if you knew a simple hack for turning a hammer into a hatchet. It could save a lot of time, effort, and money.

It was only last month that I found myself dealing with tons of data. What I wanted was simple, but I had no idea how to get Google Sheets to reveal the data. The answer? Use a function I had never heard of.  This experience convinced me to try and learn about new functions.

So now, I want to share some of my favorite tools and functions of Google Sheets– tools and functions that can help you avoid the frustration of trying to cut a tree down with a hammer instead of a hatchet. In other words, they can make your work a little easier.

sheets
Skip the Google Sheets frustration!
Sign up for the TCEA Google Sheets online, self-paced course.

Why We Use Google Sheets

I’ve been using spreadsheets since I was 13 years old, but I still know very little about them. That’s because I’m always focused on the problems I have to solve. But if you take a moment to look around, you’ll realize that you don’t always have to use the same tools to solve problems. And Google Sheets is certainly a tool for solving problems. Examples of problems Google Sheets can solve include:

  • Making sense of complexity or representing data in easy-to-understand formats like graphs
  • Sorting and filtering data to see relationships
  • Calculating and summarizing
  • Connecting more efficiently with others

Add-Ons and Extensions for Google Sheets

If you’re like me, you may have reached for some digital helpers like Google add-ons or extensions to Google Sheets. Some of my favorite solution helpers for Google Sheets include these power tools from Hymel Stayte’s Solutions:

  1. Magic Make Copies
  2. Magic Row Reveal
  3. Magic Cell Notifications
  4. Magic Class Tracker

Mail Merge Tools for Sheets

  1. FormMule
  2. Yet Another Mail Merge (YAMM)

Using Sheets as a Database

  1. Google Data Studio
  2. Watch Mike and Megan Video
  3. Google Data Studio Resources: A Wakelet Collection
  4. Awesome Table
  5. Tweet Archiver
  6. TAGS – Twitter Archiving Google Sheet

Generating Documents Using Google Sheets

  1. Autocrat
  2. Certify’em
  3. Document Studio
  4. Mapping Sheets

If you have more add-ons that you like for Google Sheets, please feel free to share those in the comments. But what if these power tools are too much for what you are trying to do? Or what if you need to dig into your data in a way that’s incongruent with these add-ons?

It’s time to explore some of my favorite functions below.

Google Sheets Functions

The following functions are ones that I use often. You can find a sample spreadsheet here (get a copy of it) that shows how each of these works.

power tools

One simple example that a lot of folks don’t know about that I use every day? Concatenate.

=Concatenate(A2,” “,B2,”<”,C3,”>”)

This allows you to put text together. My biggest use involves concatenating text such as firstname, lastname, and email. I use the result to enroll people in Canvas LMS.

Another one that I didn’t know about but is so powerful: IFERROR. It prevents showing a nonsensical error. Instead, it shows you a message you can customize.

Each of these expands your repertoire of what’s possible in Google Sheets. Take the time to learn them and practice them.

=join(“,”,A1:A5)

=IFERROR(value, [value_if_error]) (See more)

=IMPORTRANGE (sheet_url, range_string)

=QUERY(NAMEDRANGE, “SELECT A WHERE B=‘Google Sites’ “)

=COUNTIF (range, criterion)

=COUNTA (range, criterion)

=DetectLanguage(B2) with =GoogleTanslate(B2,C2,”en”)

Need a few more?

Get the “Make Magic with Spreadsheets” Wakelet

Want to get more examples in video format? You may want to check out this Wakelet where I’ve curated a few videos and tutorials.

powertools
4 comments
1 FacebookTwitterPinterestLinkedinEmail