Google Sheets - Working With Formulas and Functions
4.0 (85 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
845 students enrolled

Google Sheets - Working With Formulas and Functions

Learn how to use formulas and functions in spreadsheets to do some powerful and amazing things.
4.0 (85 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
845 students enrolled
Created by Joe Buchmann
Last updated 9/2017
English
English [Auto]
Current price: $25.99 Original price: $39.99 Discount: 35% off
2 days left at this price!
30-Day Money-Back Guarantee
This course includes
  • 1 hour on-demand video
  • 1 downloadable resource
  • Full lifetime access
  • Access on mobile and TV
  • Assignments
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Understand what functions are and how to apply them
  • Full understanding of what various different functions do, and how to use them
Requirements
  • Basic knowledge of Google Sheets or other spreadsheet program
  • Desire to learn how functions are built, and how they can work together
Description

If you've ever dreamed of using those really cool functions in your spreadsheets that you've seen other people use, then this course if for you. By the end of the course, you will be on your way to becoming a spreadsheet wizard!

In this course we'll start with the most basic function, learning how to write it and understand how functions work. Then we'll work our way up through more and more advanced functions, so that by the end of the course, you'll be able to use several functions together in powerful ways. 

Don't worry if you have limited, or no experience with formulas and functions. This course will walk you through every step of the way. You should, however, have a basic understanding of how spreadsheets work before taking this course. If you are completely new to spreadsheets, you should check out the course, "Google Sheets and Forms - Beginner to Expert" first. 

This course is done in Google Sheets, but the functions are all relevant to Microsoft Excel as well. 

Who this course is for:
  • Business professionals with a desire to increase spreadsheet efficiency
  • High school and college students
  • People looking to transition from Microsoft to Google products
  • Anyone who uses spreadsheets regularly
Course content
Expand all 16 lectures 01:02:36
+ Introduction
3 lectures 04:33

A short introduction to the course from Joe Buchmann.

Preview 01:42

What's the difference between formulas and functions? We'll find out in this lesson.

Preview 01:44

The formula bar is not only useful, but an important thing to pay attention to in this course. 

Preview 01:07
+ The Functions
12 lectures 57:21

We're going to start this course off by learning the most basic function. We'll see how to write it, how it works and how to do it faster. 

Preview 03:03

Now that we understand the structure of a function and how it works, we'll take a look at some more

 basic functions.

This lesson covers the following functions:

AVERAGE
Returns the the average of numbers in a selected group of cells, ignoring text.

MAX
Returns the maximum numerical value in a selected group of cells.

MIN
Returns the minimum numerical value in a selected group of cells.

COUNT
Returns a count of how many numbers are in a  selected group of cells.

COUNTA
Returns a count of how many in a selected group of cells contain data, including text. This ignores blank cells.

Preview 04:36
Create a new Google Sheet and enter ten random numbers into the first column, in separate cells. Write the SUM, AVERAGE, COUNT, MAX and MIN functions and have each of them look at your column of random numbers. Write these out rather than using the drop down so that you can better understand them.
Writing Basic Functions
5 questions

The IF function works by checking to see if one value is true, then returning a specified result. If the value is not true, it will return a different result.

IF is one of the most powerful functions to know and understand. It's used frequently, and as a bases for several other functions. 

We'll see how this can be used in a few different situations.

Syntax
=IF(logical_expression, value_if_true, value_if_false)

Using the IF Function
05:27

The IF function is made even more powerful by nesting one function within another. This lesson will show you how that works. 

Preview 04:46
You will write a function to determine if an employee is eligible to be entered into a company raffle based on working at least the amount of hours specified in the threshold box.
Using Nested Functions
2 questions

The OR function returns true if ANY of the supplied values are true, and false if none of them are true. 

The AND function returns true if ALL of the supplied values are true, and false if any of them are not true. 

We'll see how both of these functions work in a couple of situations.

Syntax
=AND(logical_expression1, logical_expression2)

=OR(logical_expression1, logical_expression2)

OR and AND
06:08

COUNTIF and SUMIF are variations of the standard IF function.

COUNTIF will count how many cells contain a certain type of data that you specify.
i.e. Count the cells if they are greater than 50. 

Syntax
=COUNTIF(range, criterion)

SUMIF will give you the sum of a range of cells that match the criteria that you specify.
i.e. Give the sum of all cells containing a value greater than 50.

Syntax
=SUMIF(range, criterion, sum_range)

COUNTIF and SUMIF
04:59

Let's test your knowledge to see how much you've learned so far.

Functions Quiz #1
10 questions

In this lesson we'll see how we can round numbers to a specific decimal or whole number place using the ROUND function. We'll also see how we can force the function to round up or down using ROUNDUP and ROUNDDOWN.

Functions covered:

ROUND
Rounds a number to the specified decimal or whole number place.

Syntax
=ROUND(value, places)

ROUNDUP
Forces a number to be rounded up to the specified decimal or whole number place.

Syntax
=ROUNDUP(value, places)

ROUNDDOWN
Forces a number to be rounded down to the specified decimal or whole number place.

Syntax
=ROUNDDOWN(value, places)


Rounding Numbers
02:48

In this lesson we'll use a few functions to format text in different ways. Here's what we'll be covering:

PROPER
Capitalizes each word in a specified string.

Syntax
=PROPER(text_to_capitalize)

UPPER
Converts a specified string to uppercase. 

Syntax
=UPPER(text)

LOWER
Converts a specified string to lowercase.

Syntax
=LOWER(text)

TRIM
=TRIM(text)


Formatting Strings of Text
03:35

Google sheets has several functions for determining dates and times. We'll look at a few of these, and see how we can use them in some practical examples. 

Here's what we'll be covering:

NOW
Returns the current date and time as a date value.

Syntax
=NOW()

TODAY
Returns the current date as a date value only.

Syntax
=TODAY()

DAY
Returns the day of the month that a specific date falls on, in numeric format. 

Syntax
=DAY(date)

MONTH
Returns the month of the year a specific date falls in, in numeric format.

Syntax
MONTH(date)

YEAR
Returns the year specified by a given date.

Syntax
=YEAR(date)

WEEKDAY
Returns a number representing the day of the week of the date provided. 

Syntax
=WEEKDAY(date, type)

Date and Time Functions
05:09

In this lesson we'll find out how to join strings of text together and combine other data using the CONCATENATE function.

CONCATENATE
Joins strings of data together.

Syntax
=CONCATENATE(string1, string2)

Preview 04:16

VLOOKUP (Vertical lookup) is a powerful function for finding data in a spreadsheet and returning surrounding data to another location. The function searches down the first column of a range for a key, or specific value that you define, and returns the value of another cell in the same row from a column that you specify.

There is also another function that works the same way, but horizontally. This is the HLOOKUP function. We won't cover that one, but once you understand VLOOKUP, you can apply the same methods to use HLOOKUP.

VLOOKUP
Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.

Syntax
=VLOOKUP(search_key, range, index, is_sorted)

VLOOKUP
06:12

Let's test your knowledge to see how much more you've learned.

Functions Quiz #2
10 questions
+ What Now
1 lecture 00:42

Just a short closing statement from Joe Buchmann.

Wrapping It All Up
00:42