Public Relations Today
Jayne Miller By Jayne Miller • August 20, 2015

Little-Known Google Sheet Features and Formulas for Teachers

Next up in our line of tricks for Google Drive lovers? We’re taking on Google Sheets.


Here are some of our favorite features and functions (that you might not know about yet) that can be leveraged for interesting classroom projects.

We also threw in some tips for formatting, translating, and validating data that are ideal for the organization-loving teacher.


Sheets has a faster way to add images. Use the formula =image(“url”), inserting a link to the image in the quotes. 

Conditional Formatting

Consistency is nice in a spreadsheet. And here’s a trick for fine-tuning the way you’d like to display your data. Select a range of cells, click “format,” and then “conditional formatting.”

Now build a set of rules to format your data. Perhaps you’d like positive figures in black and negative figures in red. Or scores below a 75% to be highlighted. Perhaps bolding every value above a 95%. You’re only limited by your imagination here.

Data Validation

Collaborating is the best. But sometimes there are data points you’d like to keep formatted in a specific way. Use Google Sheets Data Validation in those cases; this tool will allow you to restrict values of certain cells, force collaborators to select values from a list, or warn them when they’ve entered something that doesn’t fit the scheme you have in place.

To use data validation on a set of cells, select “data” and then “ validation.”

You’ll be prompted to include instructions that users will see when they hover over a cell you’ve validated.

The validation menu will also allow you to lock in the formula a collaborator uses in a cell, a date range, or custom text options. A tricky way to keep your data squeaky clean.

Funky Functions

Sheets has some pretty useful functions. Here are some are some little-known gems that are usable for class projects and classroom management alike.

Valid URLs: Dropping a lot of urls into your sheet? You can quickly double check that they’re all valid with the ISURL() function. Sheets will return a “true” or “false” verdict for each url checked.

Valid Email Address: Instead, want to verify that contact information you’ve collected is valid? The ISEMAIL() function will check that the email entered is good and also generates a “true” or “false” once applied.

Import Range: Want to grab something from another one of your sheets? You don’t have to copy and paste. You can apply the IMPORTRANGE() function, followed by the corresponding spreadsheet key (the list of characters in your Google Sheets url) and the range you wish to import.

Translate: This is a nice Google Translate tie in. You can call on the powers of translate with the GOOGLETRANSLATE() function. You’ll select your cells, followed by the origin language and then the language into which you’d like to translate it.

Detect Language: Similarly, if you’ve received info from a Google Form or a collaborator and have no idea what language is being used, you can apply the DETECTLANGUAGE() function.

GoogleClock: Keep a cell updated to the current date and time using the GOOGLECLOCK() function.

Dated: Hey project planners - calculate the amount of time until your deadline through the DATEDIF() function. This one will calculate days, weeks, months, or years between a start and end date.

Mini Charts: The SPARKLINE() function will allow you to create a mini chart within a single cell. (Cute!) Just include the data range followed by any optional chart settings. Learn more here.

Add Estimates to Your Charts

If you’d like to tack on an estimate to one of your charts - perhaps test goals for your class or time spent on certain units -  you can tell Google which figures are estimates by adding the word “FALSE” in the column to the right of the data, known as the "certainty" column.

You should notice these data points will be displayed slightly differently in your chart. For example, projections added to the end of a line charted will display as dashed lines instead.

TOC in Sheets

When we talk about a table of contents, we don’t normally think of a spreadsheet. But if you’re dealing with so much data that you get lost in it, you might need a roadmap.

Well, you’ll notice that each tab of your spreadsheet actually has its own url. Copy each url into a fresh tab and add a descriptive title to each link. When all are entered, you’ll have that roadmap for your doc.

Random Generator

This add-on will help whenever you need a random set of data. It is especially useful for generating bulletproof passwords. IT Coordinators take note!

Pin Location to Map

Another cool add-on here. This one will plot your data on a Google map. Plan a trip, make a map of your friends' houses, or use this tool to map the location of local businesses. Lots of potential for neat student projects with this one.


The Teacher's Guide to Using Google Drive 2.0