4 Tips To Master Google Sheets

By Jason Schmidt

When I meet with teachers to train them in G-Suite, the tool that seems to intimidate people the most is Google Sheets. This tool is super powerful, and can save you a TON of time if you know some of the secrets of it. Here are just a few tips for ways that I use it on a regular basis.

Formula Basics

Before we get started, it’s helpful to know how Google Sheets operates. A spreadsheet is basically a gigantic table with cells arranged into cells and columns. Any kind of data can be entered into cells (whether through a form or manual entry), and operations can be performed on those data. When you want to perform a function on data in your spreadsheet, you start with an = sign followed by the name of a function or formula. These can be simple math functions, or you can use any of the functions listed here. Google Sheets also lets you nest functions within each other, so you can use multiple formulas within the same cell with pretty powerful results. I have also created a few short videos that show you how to do various tasks within Google Sheets. You can check them out here. I also have created a longer tutorial about formatting that you might find useful.

Importing Data from Other Sources

The importdata function allows you to pull data from any accessible source into your spreadsheet, and the importrange function allows you to pull in data from another spreadsheet. There are ways to import from a variety of data sources (like websites), too. For example, let’s say you want to manipulate data from the US Census published on Wikipedia. Simply locate the page on the site that has your data, and import the data into your spreadsheet. Try this formula to see how it works: =IMPORTHTML(“https://en.wikipedia.org/wiki/United_States_Census”,”table”,3). This is way quicker than figuring out how to download the data or entering it manually. Try this technique with shared spreadsheets, PLC data, or public information sources and see how you can make sense of it.

ARRAYFORMULA

If you’ve used spreadsheets before, chances are you want to try and apply the same formula to a whole column of cells. I used to rely on add-ons to help me with this task, but I have recently learned the power of arrayformula to complete this task more reliably and quickly without having to think too much about how to make it work. Next time you want to copy a formula all the way down a column, try this: =ARRAYFORMULA(IF(ISBLANK(a2:a),””,formula you want to copy. It might take a little research and trial and error to get the result you want, but it’s amazing when it works, and if any data in your sheet changes, your formula is also updated! This was a lifesaver as I was going through nearly 600 rubric scores recently.

Conditional Formatting

Conditional formattting allows you to change the visual appearance of a cell based on the data contained in it. A great example of this is when we establish cut scores of students who may need additional help. Once you have your data (or even before it is entered), set up conditional formatting with the different thresholds that will color code your data for you. Once you have data, click on Format -> Conditional Formatting and set your criteria for how you want your data to be formatted.

Google Sheets has so much power and versatility that I believe every teacher should have at least a little skill with it. Take one or two of these tips with you to your next data team meeting, and wow your colleagues with your mad skills!