Sifting Through the Sheets - Google Sheets Tips & Tricks

Spreadsheets! The bane of one's existence. To others, a life saver. Come "sift" through the sheets with tips on how to use the spreadsheets and what they can be used for.

For this session, I wanted to break down some Google Sheets tips into a variety of parts. The goal is to assist you and provide you with a place where you can quickly and easily grab some information as to how to work with Google Sheets. The topics will include:

Templates
Entering Information
Shortcuts & Formulas
Formatting & Styling
Printing & Sharing
Add-ons

Templates

Like other G Suite for Education products, Google offers a variety of templates that you can use to amplify your use of Google Sheets. There are also third-party companies that offer many templates for Google Sheets as well. To get started with the templates, simply navigate your browser to sheets.google.com. There you will find a number of templates that you can use, including a gradebook option. Eventually, if we develop documents that you will need to fill, the Coloma Community Schools tab will house those templates for you as well. Check out a blog post on the private template gallery here. You can also install an "add-on" to your Google Sheets that will give you access to a variety of templates created by a company called Vertex42. All of the templates are free to use.

Entering Information

Entering information into a Google Sheet is incredibly easy, but one thing to keep in mind is what we want to do with the data. Sheets is an incredible tool for organizing and viewing data, but only if we are careful on how we set it up. Sheets is not made for a lot of text in each cell, but small bits and pieces of information that we can easily sort. For instance, student name or ID number are fantastic student identifiers to allow for easy sorting.

Sheets has a couple ways to enter information and a few ways to fix information when we do. There are some basic tips on how to enter various types of information into a Google Sheet.

Keep information simple and to the point
- Google Sheets may not be the best answer for typing long responses into a cell
It is best to have easily sortable data, or even using numerical codes for different pieces of information
Data can be entered two ways - a form created in Google Forms or manually by hand

Overall, adding data to Google Sheets is something that can be a headache or a breeze. In order to make it more of a breeze, break the data up that you are entering so as to not have too much information in a cell.

Using Formulas

The power of any spreadsheet application is the ability to have it do the calculations for you. While calculations may specify numbers, there are ways to work with words as well. Formulas and functions, which are built into Google Sheets, can serve as a fantastic way to take some of the calculations out of the work that you are doing. There are thousands of functions and opportunities for you calculate, so I have included the official list from G Suite's Help Desk, which also provides some explanations on how we do it. Check out the list here.

Shortcuts

Shortcuts are another way to make the process of using Google Sheets better. While the shortcuts for Chrome OS work for sheets, such as "Ctrl + C" for copy, "Ctrl + V" for paste, there are many more available options for using in Google Sheets, including shortcuts such as "Fill Range" and "Insert Date." To view a full list of shortcuts available within in Google Sheets, check out the official Google help document here.

Formatting and Styling Your GooGle Sheet

Google Sheets has some powerful features that allow us to view the data in ways that make the most sense. For example, you can easily "freeze" the top rows to allow for easy sorting within the sheet. You can use conditional formatting to highlight important parts of the information. You can also use formulas, such as "=countif(x,A1:A10)" to count certain items take place. Google also allows you to easily set up alternating colors for the rows as well.

In order to get to the majority of the formatting, you will use the "Format" tab to set up many of best formatting options. If you want to change the view and "freeze" the top rows or columns, you will use the "View" menu on the top.

Comet Tech Tutorial - Formatting Your Google Sheets

Check out the short video for some how to tips for formatting your Google Sheets.

Printing And Sharing a Google Sheet

Printing a Google Sheet does not have to be a nightmare. There are a few different settings to make printing a breeze with our Google Sheets. The three types of printing are as follows:

Current Sheet
- This will print the top sheet that you are looking at in your Google Sheet. It will print wherever there is text to work on
All Sheets
- This will print all sheets within the Google Sheet Document
Selection
- Prior to selecting print, highlight the information that you wish to print
  - Must be consecutive information

Other options that you have when printing from Google Sheets including how it will display. For instance, on a longer spreadsheet, you may wish to display headers on each page. You can also include various items on the page, including page number, sheet names, and the document title. Google allows you to easily change the size of the paper as well. Plus, you can decide how it will appear on the paper with the layout options at the bottom. Overall, printing is relatively easy to do from within Google Sheets.

Like many of the other Google Products, sharing is something that is very easy to do. Like your Slides and Docs, the blue share button in the upper right hand corner will provide you with the sharing options. There are a number of ways to share, including:

By email address
By Clicking "Advanced," you have options to include who can edit, share, etc. the document.
Or, making the document available by link or publically available

Be mindful when sharing to assure that you are not sharing any confidential student data with people that should not have access to the data.

Comet Tech Tutorial - Printing in Google Sheets

Check out this short tutorial on the different print options in Google Sheets

Comet Tech Tutorial - Sharing Options within Google

Check out this short tutorial on how to share documents within G Suite applications.

Add-ONs

Add-ons are not extensions, but are scripts that are written to help the G Suite applications function more efficiently or do different things. For instance, if you collect form responses as a sign up for different activities, you can use an add-on such as "autoCrat" to automatically send email confirmations to the guests. If you have student data in the document, and you want to split the information up, using an add-on like "Split Names" will assist you in automating the process and saving you some valuable time. Below, you will find a list of some of my favorite Google Sheets add-ons.

autoCrat - Created by New Visions Cloudlab, this add-on allows you to run a mail merge, or automatic email sending, from your Google Sheet. You set a template and it pulls the information from a series of specific cells to fill in a form letter. Really powerful stuff.
Flubaroo - Back in the day, before Google Forms had the quiz feature, Flubaroo was available to help grade Google Forms as quizzes. It will automatically grade and provide feedback for students, allowing them to know instantly how they did. It has come a long way, and continues to improve on grading more of the short answer and text based questions as well.
formMule - formMule is also a New Visions Cloudlab add-on, that does much of what autoCrat does. formMule takes that same idea, but allows you to set smarter templates to email to different stakeholders at different times.
g(Math) for Sheets - g(Math) allows teachers to create and type in custom math formulas, in proper form, within the G Suite applications. There is also one available for Google Docs as well.
Power Tools - Power Tools is a series of tools that allow you to be more efficient with Google Sheets without having to look up the formulas or functions on how to do something. It offers suggestions like "Split," "Clear," "Remove," and "Data" to help you organize and make the most of the options.

All of these add-ons are free to use. In order to install them, simply select "Add-ons" from the top menu and choose get Add-ons. This will bring up a list of all of the available add-ons for the type of G Suite document you are working on.

Report abuse