Google Sheets - Working With Formulas and Functions
- 1 hour on-demand video
- 1 downloadable resource
- Full lifetime access
- Access on mobile and TV
- Assignments
- Certificate of Completion
Get your team access to 4,000+ top Udemy courses anytime, anywhere.
Try Udemy for Business- Understand what functions are and how to apply them
- Full understanding of what various different functions do, and how to use them
- Basic knowledge of Google Sheets or other spreadsheet program
- Desire to learn how functions are built, and how they can work together
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.
- 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
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.
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.
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)
The IF function is made even more powerful by nesting one function within another. This lesson will show you how that works.
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)
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)
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)
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)
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)
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)
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)