Lesson 5: Formulas

Total video watch time - 59:17

LESSON OBJECTIVES

Get formula help and suggestions within a spreadsheet
Use basic formulas such as unique, count, sum, average, and sparkline
Use advanced formulas such as if, countif, sumif, sort, filter
Use the QUERY function to extract data

Note: There are hundreds of formulas and functions in Google Sheets. You obviously won't learn them all in this course. My goal is to show the basics as well as some advanced formulas and how they can help you. The Additional Resources section at the bottom of this page includes opportunities for exploration if you are interested in learning about what else is available.

Formula Foundations

Before getting started using formulas in Sheets, it's important to have a foundation. In this video, we will take a look at

where to find formula options within Sheets
how to start typing a formula
making use of suggestions and help embedded within Sheets
how to quickly autofill a formula to other cells
adjusting the formula bar

View a complete list of Google Sheets functions here.

Sum and Average Formulas

This video provides an oveview of some of the most basic, commonly used spreadsheet formulas ... unique, count, sum, and average. As a bonus, you'll also learn about the sparkline function which creates a line graph within a cell providing a really quick way to visualize progress.

The "IF" Formulas

Using IF formulas, you can return a value based on a logical expression or condition. In this video you will learn how to use:

IF to return a value based on whether a condition is true or false.
COUNTIF to count the total number of values in a range that meet a specified condition.
SUMIF to add values in a range if a condition is met.

Sort and Filter Functions

Spreadsheet data can be filtered and sorted manually. Using the filter and sort formulas can automate these functions. Once you know how to use both formulas, you can combine them within a single formula.

Query Function

The QUERY function is one of the most useful functions in Google Sheets. It allows you to manipulate your data in Google Sheets and present it in a meaningful way. The formula is very powerful in that it does the job of many other functions. In this tutorial we will look at the syntax of the QUERY formula and see various ways to use it.

To learn more on the QUERY function and the various commands and keywords you can use within your formula, check out this blog post from Ben Collins.

VLOOKUP

The VLOOKUP function looks for a given value in a vertical list, and once it spots that value, it would use that row and return the value from a column number you specify. Basically, you would use a VLOOKUP formula when you need to find or search for data in a table or a range.

IMPORTRANGE

The IMPORTRANGE formula can be useful to reference a cell or range of cells in another spreadsheet or to pull data from spreadsheet into another. Using the IMPORTRANGE formula can also assist with sharing spreadsheet data with others, allowing you to give access to only the data in a larger data set that collaborators will need to see.

Check out the Additional Resources section below to see how to pair the QUERY and IMPORTRANGE formulas in order to populate a spreadsheet with data from another if cell data matches.

LESSON TASK

IMPORTANT: Before opening the practice task, make sure you are viewing this page with your personal Google Chrome account. This will ensure that a copy of the task is created in your personal Google Account. If you open the task in your school account, some features may be blocked and you will not be able to submit the assignment to Google Classroom.

Click the Practice Task button below, then select the "USE TEMPLATE" button to create an editable copy of the task in your Google Drive.
Follow the steps in the INSTRUCTIONS tab
Click the Solution File button to check your work.
When you have completed the task to the best of your ability, find the "TURN IN" tab in the Practice Task for instructions on how to submit your work.

ADDITIONAL RESOURCES

My Formula Didn’t Work: Deciphering Error Codes

Complete List of Formulas and Functions

Name a Range of Cells (Named Ranges)

IFS Function - Just like the IF function, only evaluates multiple conditions and returns a value that corresponds to the first true condition.

COUNTIFS Function - Just like the COUNTIF function, it returns the count of a range depending on multiple criteria.

EZ Query Add On - The EZ Query Sheets add-on helps you to run a query without having to type the formulas. When the query tab is created, you will still see the formula. This add-on is no longer essential since you can build the same queries with a formula, but if you are just learning queries, it can be helpful to generate the formulas for you.

QUERY and IMPORTRANGE Mashup - Using both of these formulas in conjunction, you can return a value from one spreadsheet into another spreadsheet based on matching data common to both spreadsheets.

Using Arrays in Google Sheets - Enables the display of values returned from an array formula into multiple rows and/or columns.

Blake Burge's 12 Formulas to Increase Productivity - This thread includes a short description of how each formula can help with a short demonstration of how to use the formula.

Page updated

Google Sites

Report abuse