Google Sheets

Introduction

Google Spreadsheets provides a variety of common functions that can be useful for quickly finding the sum, average, count, maximum value, and minimum value of a range of cells. Spreadsheets also provides access to hundreds of functions for financial, statistical, and other complex calculations.

In this lesson, you will learn the basics of how to insert a function into a sheet by typing its name and by using the Functions button. You will also learn how to access the list of available functions.

Basic functions

Video: Working with Functions

Watch the video (5:41).

A function is a predefined formula that performs calculations using specific values in a particular order. One of the key benefits of functions is that they can save you time because you do not have to write the entire formula yourself. Google Spreadsheets has several functions readily available and provides access to hundreds of other functions to assist with your calculations.

In order to use functions correctly, you will need to understand the different parts of a function and how to create arguments in functions to calculate values and cell references.

The parts of a function

Similar to entering a formula, the order in which you enter a function into a cell is important. Each function has a specific order, called syntax, that must be followed in order for the function to calculate properly. The basic syntax to create a formula with a function is to insert an equals sign (=), a function name (AVERAGE, for example, is the function name for finding an average), and an argument. Arguments contain the information you want the formula to calculate, such as a range of cell references.

Syntax of a basic function

Working with arguments

Arguments must be enclosed in parentheses ( ). Individual values or cell references inside the parentheses are separated by either colons or commas.

• Colons create a reference to a range of cells. For example, =SUM(F21:F27) would calculate the sum of the cell range F21 through F27.
  
  
• Commas separate individual values, cell references, and cell ranges. If there is more than one argument, each argument must be separated by a comma. For example, =COUNT(L5:L8,L12:L20,L25) will count all of the cells in the three arguments that are included in parentheses.
  
  

Creating a function

Google Spreadsheets has a variety of functions available. Here are some of the most common functions you'll use:

• SUM: The SUM function adds all of the values of the selected cells in the argument. This function is useful for quickly adding values in a range of cells.
• AVERAGE: The AVERAGE function will find the average of the values included in the argument. It calculates the sum of the cells and then divides the sum by the number of cells in the argument.
• COUNT: Using the COUNT function will display the number of cells that have been included in the argument. This function is useful for quickly counting items on the sheet.
• MAX: The MAX function displays the highest cell value included in the argument.
• MIN: The MIN function displays the lowest cell value included in the argument.

To create a function:

1. Select the cell where the answer will appear (for example, cell C15).
2. Type the equals sign (=), then type the desired function name (for example, AVERAGE).
   
   Typing a function
3. Enter the cells for the argument inside the parentheses (for example, C5:C14).
   
   Entering the argument
4. Press the Enter key on your keyboard, and the answer appears.
   
   The answer

Google Spreadsheets will not always tell you if your function contains an error, so it's up to you to check all of your functions. To learn how to do this, read our article on why you should Double-Check Your Excel Formulas.

To create a function using the Function button:

The Function button allows you to automatically return the results for a range of cells. The answer will display in the cell below the range.

1. Select the range of cells you wish to include in the argument (for example, D21:D27).
   
   Selecting a range of cells
2. Click the Functions button, then select the desired function from the drop-down menu (for example, SUM).
   
   Selecting SUM
3. In the cell directly below the selected cells, the function appears.
   
   The function in the adjacent cell
   
4. Press the Enter key on your keyboard, and the answer appears.
   
   The answer
   

Like formulas, functions can be copied to adjacent cells. Select the cell that contains the function, and the fill handle displays. Click, hold, and drag the fill handle over the cells you wish to fill.

Copying a function into multiple cells

Google Spreadsheets function list

If you have experience using spreadsheets and would like to use Google Spreadsheets to make more advanced calculations, then you may wish to explore the Google Spreadsheets function list. It is a handy reference for hundreds of financial, statistical, and data analysis functions.

The Google Spreadsheets function list

If you are familiar with functions found in Microsoft Excel's Function Library, you will find that the Google Spreadsheets function list has many of the same functions.

To access the function list:

• Click the Functions button and select More functions... from the drop-down menu. The Google spreadsheets function list will appear in a new browser tab.
  
  Selecting More functions...

If you're comfortable with basic functions, you may want to try a more advanced one like VLOOKUP. You can check out our article on How to Use Excel's VLOOKUP Function for more information. Like most functions, VLOOKUP works the same way in Excel and Google Spreadsheets.

Challenge!

To work through the challenge, open GCFLearnFree L15: Exercise Log and copy the file to your Google Drive. View the instructions below the challenge if you are not sure how to make a copy of the file.

1. In cell E28, create a function that adds the range of cells E21 through E27.
2. In cell L15, use the Functions button to insert a function that will find the average number of reps for the range of cells L5 through L14.
3. In cell Q1, create a function that counts the number of exercises performed in the cell ranges A5 through A14, J5 through J14, and cell C19.

To copy the example file to your Google Drive:

In these tutorials, we will provide example files you can use to practice what you've learned in each lesson. Because these files are Google Docs we have chosen to share, you will need to copy the file to your Google Drive before you can edit the file.

1. Click the link at the top of this page to open the example file.
2. The example file will appear in a new browser tab or window. If you are not currently signed in to your Google account, locate and click Sign in on the top-right corner of the page.
   
   Signing in to your Google Account
3. After you have signed in to your Google account, locate and select File in the toolbar menu and select Make a copy... from the drop-down menu.
   
   Making a copy of the example file
4. The Copy Document dialog box will appear. Enter a new title for the file, then click OK.
   
   Naming the file and clicking OK
5. The copy of the file will appear in a new browser tab. Now you're ready to start using the example file.
   
   Viewing the copied example file in a new tab