Formulas

Google Sheets is a web-based application that allows you to store and organize different types of information. Your data is automatically saved for you. It is very similar to Excel and while lacking a few of the more robust Excel features, it is a powerful spreadsheet application that can do anything you will need for it to do. The bonus is that multiple people can work in the same spreadsheet at once using the sharing features available in Google Sheets. Google Sheets allow you to manipulate data using Functions and Formulas.

• A Function is a built-in operation from the spreadsheet app that can be used to calculate cell, row, column, or range values, manipulate data, and much more!

  • The Syntax refers to the order and layout of the function and its arguments

• A Formula is a combination of functions, cells, rows, columns, and ranges used to obtain a specific result.

  • All formulas begin with an =. This signals Sheets that a formula is being written.

  • There are multiple ways to enter a formula:

    • There is a Formula button at the far right end of the toolbar with a drop-down menu. The most common formulas are included first in the menu but all formulas can be accessed through this menu.

    • Click in a cell and begin typing the Formula Title (ex. Average.) Select the

formula when it pops up in the list

• Manually enter the formula

• You can easily copy formulas down columns or across rows by dragging

BUILT-IN HELP: Google provides lots of help built into their Sheets program. When you begin to enter a formula, you are given options for the formula Sheets believes you want. After selecting the formula, you can click on the i to the left of the formula box and get definitions for the elements of the formula as well as examples as VLOOKUP example below. Understanding how Sheets uses syntax will help you be able to use any Sheets formula.

The articles below can help you learn syntax and how to use any Google Formula:

• Google Sheets Function List

• Google Sheets: Working with Formulas

• Google Sheets (Google Help Center)

• Google Sheets 101: The Beginners Guide to Online Spreadsheets

• Alice Keeler: Alice is the Queen of spreadsheets in instruction. This is a great source for you to use with your teachers or to discover new tricks or scripts that you may not have used before. For instance, here is a script that she wrote to create a tab in Sheets for every student using a script. She always includes step-by=step instructions as well as a video.

  • Template Tab

Google has some formulas built into the interface:

• Split text-to-columns: Use this to separate data in a column.

  • Example: First and Last Name is entered in the same column and you want them in separate columns.

  • Add a blank column to the right of your list of names

  • Select the data in the column by clicking on the column letter names

  • Go to Data>Split text to columns

  • Video

• Remove duplicates: Use this formula to remove all duplicate entries;

  • IMPORTANT: If you use this column, it removes any duplicate entries. To keep your original list and create a list with no duplicates, use the UNIQUE formula.

  • Select the data in the column by clicking on the column letter names

  • Go to Data>Remove Duplicates

  • Select Data has Header row

  • Select the columns you want to search

  • Press Return

  • Video

• Trim Whitespace: Use this formula to remove any extra spaces in your worksheet when leaning up data

  • Select the range of data that you want to clean up.

  • Go to Data> Trim Whitespace

  • Press Return

  • Sheets will tell you how many whitespaces have been removed

  • Video

• Clean-Up Suggestions:

  • Select the range of data that you want to clean up.

  • Go to Data> Clean-Up Suggestions

  • Press Return

  • Sheets will suggest ways to clean up your data.

  • This is a great place to start when cleaning up your data or to use as a double-check after you have cleaned it up yourself.

  • Video