Welcome to ONLC Training Centers


Google Sheets Functions Course Outline

 (1 day)

Overview
This one-day Google Sheets Functions class will show you some of the amazing things that Google Sheets can do. While Google Sheets is easy to use at an introductory level, it also has significant power to do more complex things. Covered in this class are advanced features and functions for conditional processing (IF statements and conditional functions), dealing with text, database lookups, working with date fields, queries and much more! You will be amazed by all of the advanced features that you never knew were available in Google Sheets.

Special Note to New Hampshire Residents
This course has not yet been approved by the State's Department of Education. Please contact us to get an update as to when the class should be available in New Hampshire.

Who Should Attend
The class is for Google Sheets Power Users.

Prerequisites
Before taking this class, we recommend that you have experience creating worksheets in Google Sheets or have taken ONLC’s Introduction to Google Sheets class.

Course Outline

AVERAGE, MAX, MIN
=AVERAGE()
=MIN()
Automatic formulas

COUNT and COUNTA
=COUNT()
=COUNTA()

IF
Simple IF
Referring to a cell
Dealing with blank spaces and making your data look prettier
Nested IF functions and multiple options
Making decisions based on words not just numbers

CONCATENATE (inc TEXT, CONCAT and &)
Joining two names together
Making website URLS from information in different cells
Combining a range of cell values to make a complete part number
Concatenating formulas
Making sentences and adding dates in the current format
Creating addresses on different lines
& - AND operator

VLOOKUP (including IFERROR and ARRAYFORMULA)
Look up a book number and get the name of who has it
Display a message instead of an error, if something cannot be found
Finding values within ranges
Including formulas in a VLOOKUP function
Using wildcards in VLOOKUP
Looking up multiple values in a table
Retrieving multiple values from a table
Retrieving multiple values from a table (part 2)
Using Named Ranges with VLOOKUP

OR & AND (including WEEKDAY)
How does the OR function work? And how does it work with IF?
Checking to see if at least one race time is below the target time
Looking at more than two race times
Using the AND function
Using AND to check if the values are within a range
Categorizing the values based on certain conditions
Using functions within an OR or AND function

COUNTIF, SUMIF, COUNTIFS, SUMIFS
COUNTIF: Counting the number of instances of a specific number
COUNTIF: Counting how many values are over a certain number
COUNTIF: Counting the number of times a word or phrase appears in a range
COUNTIF: Using a cell reference to find instances of whatever has been written in that cell
COUNTIF: Using wildcards to look for broader values
COUNTIF: Counting dates
SUMIF: Adding up the number of products sold on a particular date
SUMIFS: To find the quantity sold within a date range
COUNTIF: Counting how many students are above average
COUNTIFS: Highlighting duplicate rows

FILTER
Filtering a list by a piece of text
Filtering a list by those who still need to pay
Filtering a table by a specific date
Filtering a table between two dates
Filtering a table by a specific month
Counting the number of values that meet the filter condition
Summing up the total of values that meet the filter condition
Filtering a table using one criterion OR another

IMPORTRANGE
How to use IMPORTRANGE
Open-ended ranges
Multiple IMPORTRANGES on the same sheet
Pre-formatting an imported range

PROPER, UPPER, LOWER, TRIM
PROPER function to capitalize each word
UPPER function to capitalize all letters
LOWER function to put words into lowercase
PROPER and TRIM functions to clean up text
ARRAYFORMULA to copy PROPER function to all rows
Capitalize only the first letter of a sentence and putting the rest in lowercase
Bonus Example - Changing a name to initials

TRANSPOSE
Changing a single column or row of data
Converting 2 vertical columns to 2 horizontal ones
Converting multiple horizontal rows into vertical columns

ISEMAIL, ISNUMBER, ISURL, NOT
Checking email addresses
Checking for numbers
Checking website addresses (URLs)
Displaying different text depending on whether it's TRUE or FALSE
Adding conditional formatting
Using custom formula to add colour to cells with the data in it
Using NOT in a custom formula to highlight what isn't true

UNIQUE, COUNTUNIQUE, SORT
UNIQUE function to list unique occurrences in a list
UNIQUE with SORT to sort the unique list
Making a drop-down menu from a list
Making an alphabetical drop down menu from a list
COUNTUNIQUE to count how many things you have in the list, whilst ignoring duplicates
UNIQUE to look for unique occurrences with 2 or more criteria

NOW, TODAY, DAY, MONTH, YEAR, HOUR, MINUTE, SECOND
Getting the current date and time
Getting today's date and using it in calculations
Extracting the day from a date
Extracting the month or year

WEEKDAY, WORKDAY, NETWORKDAYS, EDATE, EOMONTH, CHOOSE
What day of the week was a particular date?
Returning the day of the week as text not as a number
Find out the date a number of days from a given date
How many working days are there between two dates?
Easily adding start of the month and end of the month dates
Working out the number of days in a month
Working out the number of working days in a month

GOOGLETRANSLATE, DETECTLANGUAGE
Translating from one language to another
Detecting a language
Detecting a language and translating it
Automatically translating board vocabulary
Having a conversation where neither person speaks the other’s language

OFFSET
Creating dynamic ranges to maintain an average formula
Example 2 - Dynamically calculating the sales of the last X months

IMAGE
Inserting an image from Drive
Inserting an image within a cell using the IMAGE function

ROUND, ROUNDUP, ROUNDDOWN

HYPERLINK
Renaming a hyperlink
Linking to a Google Document
Linking to a particular sheet
Linking to a particular cell

INDEX and MATCH
Finding the classroom a specific teacher is in
Finding who is in a particular classroom (looking up to the LEFT in a table)
The effect of inserting a column in a table on an INDEX/MATCH and a VLOOKUP formula
Returning more than one column of information
Matching a range and not an exact figure

QUERY
Analysing questionnaire feedback
Selecting the relevant data from the master data
Filter by name, using cell reference, with sorting
Filter the data between 2 dates, using cell references, and name
Filter against various criteria
Returning the averages of data and ordering them
Pivot information using QUERY not pivot tables Analysing a HR database
Returning average salaries per department
Listing salaries per employee in descending order
Limiting the number of results
Ordering by more than 1 criteria
Relabelling column headers

View outline in Word

XGA1SF

Attend hands-on, instructor-led Google Sheets Functions training classes at ONLC's more than 300 locations. Not near one of our locations? Attend these same live classes from your home/office PC via our Remote Classroom Instruction (RCI) technology.

For additional training options, check out our list of Courses and select the one that's right for you.

Microsoft Gold Partner
Class Dates
(click date for class times)
(click Enroll for locations)

Fee:  $295

Savings options:

 CEA Apps Saver
Need a price quote?

Follow the link to our self-service price quote form to generate an email with a price quote.

Email Alert

Receive an email when this class is available as "Ready to Run" or "Early Notice" status.

Attend from your office or home

If you have high-speed internet and two computers you can likely take this class from your office or home.

Need a class for a group?

We can deliver this class for a private group at your location. Follow the link to request more information.

Attend computer classes from ONLC Training Centers Request a copy via mail

GENERAL INFO

Class Format
Class Policies
Student Reviews

Bookmark and Share


HAVE QUESTIONS?
First Name

Last Name

Company

Phone

Email

Location

Question/Comment



ONLC TRAINING CENTERS
800-288-8221
www.onlc.com