Public Relations Today
Jayne Miller By Jayne Miller • June 2, 2016

Google Sheets Features and Formulas for Teachers: More You Didn’t Know Existed


Last year we learned something: educators are using Google Sheets for all sorts of things. Grading, quizzes, progress tracking - even lesson planning.

Our piece on little-known features and formulas for educators went over well enough that our readers have told us they’re ready for round two. And we’re happy to oblige.

Please find new tips and tricks below ideal for the Google Sheet-using educator.

Notification Management

More than one cool thing here teachers should have on their radar. First, you can set a notification for any time someone modifies a sheet you might be sharing. Second, you can use this same tool to ping you when a student completes a form.

To set up sheet notifications, head to Tools > Notification Rules. Here you’ll see options for when and how to get notified, including choices for form submission.

Save your preferences and wait for the info to come to you.

Importing from Excel

In Sheets, click File > Import.

You’ll be given options on what to do with this sheet. You can add it to something you’re working on as a tab, replace a file, or just start using it as a brand new Google Sheet.


Office Editing Extension

Don’t want to import? Here’s a hot tip. You can use this extension to view and edit Microsoft Excel files without installing the Office Suite. Just drag and drop files to Chrome and they’ll open in Sheets for you.

Explore Panel

Here’s a new trick. The Sheets Explore panel. This feature allows quick insights, overview, and analysis on the data contained in your sheet. To access, click the icon with a + sign in the bottom right corner of any sheet.


You can select a range of cells to analyze or leave the document unhighlighted to explore the entire sheet. If the icon is green, Google has recommendations to share. If it is grey, there’s nothing just yet.

Once you click the Explore button, custom charts and trends will appear. Hovering over these charts will give you a little more information on what figures from your dataset created it. More info here on how to start exploring.

Find Exactly What You’re Looking For

You’ve probably used the “Find and Replace” tool elsewhere in Google Docs or Microsoft Word. Tip: when you are looking for something specific, try matching the case to your search.

To do this, head to Edit > Find and Replace. Next, click the “match case” box. This will search for what you’ve entered with the exact capitalization as you’ve written it. It can save you a lot of search time or help you replace words that are improperly capitalized.


For another search upgrade, Google’s "search using regular expressions" is worth checking out. This advanced search option allows users to look for strings of text, including characters, numbers, words, or patterns of characters.

For example, searching for “c.” will yield results in which any character follows a the letter c because of the period placed after the letter. Further, using a question mark tells the search that the character you just entered was optional. So, “cats?” would yield all instances of cat or cats.

There are many more expressions where that came from. Peruse ‘em all here.

New Functions We’re Using

Round Those Numbers: Need to round your final grades? This function will do it for you. ROUNDUP will even your figures to your specified number of decimal places.

Lookup: Searches for specific values using a function. Follow the LOOKUP function with your search key - the items you’re looking for - followed by the range of cells in which you’d like to look.

Import data from an HTML Table: Maybe the info you want to capture is already perfectly sorted in an HTML table somewhere. You can grab and display that information using the IMPORTHTML function followed by the proper url and some quick info to identify which table you’d like to extract. Here’s a step-by-step for using this function.

Import an RSS or ATOM Feed: Here’s an interesting way to keep and track info coming from RSS feeds. Using the IMPORTFEED function followed by the url or query of items from the feed, you can populate your sheet with details such as the title, description, or images from online posts. More on how to do this.

Random Number Generator: The RANDBETWEEN function will provide a random number between the range you specify. Super easy.


The Teacher's Guide to Using Google Drive 2.0