Formulas and Functions

Welcome to Formulas and Functions. In this section we will cover the following topics:

Formula Basics
Autofill
Sums and Averages
MIN and MAX
COUNT
Insert Functions
Absolute and Relative Cell References
Cell Range Names

Formula Basics

Formulas contain information to perform a numerical calculation such as adding, subtracting, or multiplying. All formulas must start with an equal sign (=), followed by:

Values or cell references joined by an operator. Example: =5+3 or =A1+A2.
A function name followed by parentheses containing function arguments. Functions are the most common way to enter formulas in Sheets. Example: =SUM(A1:A2).

Create a Formula

Select the cell where you want to enter a formula.
Press =.
How to create a formula in Google Sheets.
Enter the formula.
Add cells by clicking them or typing their cell reference.
Operators include:

+ (add)

– (subtract)

* (multiply)

/ (divide)
Press Enter when you’re finished.
How to create a formula in Google Sheets.

If you entered the formula correctly, the results of the formula will appear in the cell.

Operator	Formula Example	Description
=		All formulas must start with an equal sign.
+	=2+2	Adds values.
–	=A1-B1	Subtracts values.
*	=B1*2	Multiplies values.
/	=A1/C2	Divides values.
SUM	=SUM(A1:A3)	Totals numbers in a range.
AVERAGE	=AVERAGE(A2,B1,C3)	Averages numbers in a range.

Update a Formula Result

Every time a cell that a formula references is updated, the formula result updates as well. Let’s see that in action.

Formulas will automatically recalculate when changes are made to referenced cells.
How to update a formula in Google Sheets.

Edit a Formula

Select the cell where you want to edit a formula.
Click in the formula bar.
How to edit a formula in Google Sheets.
Edit the formula in the formula bar.
How to edit a formula in Google Sheets.
Press Enter.

The formula is edited based on the changes you made to it.

Autofill

Autofill is a great way to enter sequential numbers, months, or days quickly. Autofill looks at cells that you have already filled in and makes a guess based on the pattern about how you want to fill in the rest of the series. For example, if you enter January, Autofill will fill in the following months for you. Additionally, you can use Autofill to copy formulas to adjacent cells.
Use Autofill to Duplicate Data

Select the cell that contains the data you want to copy.

Notice that the cell selection border has a box in the bottom-right corner of the cell.
Click and drag the bottom-right corner of the cell across the cells you want to fill.
How to use Autofill to Duplicate Data in Google Sheets

The column is instantly populated with the value in the first cell.
Use Autofill to Enter a Series

Select a cell or cells with the beginning of a series of values.
Double-click the fill handle.
How to use Autofill to Enter a Series in Google Sheets.

The column is filled all the way down, following the pattern that was established in the first few cells.

Selected Cell	Entries in Next Three Autofilled Cells
January	February, March, April
Jan	Feb, Mar, Apr
5:00	6:00, 7:00, 8:00
Qtr 1	Qtr 2, Qtr 3, Qtr 4
1/20/12	1/21/12, 1/22/12, 1/23/12
UPV-3592	UPV-3593, UPV-3594, UPV-3595

Sums and Averages

You can use the SUM function to calculate totals in your spreadsheets.

There are two ways to insert a function. If you know the proper syntax, you can just type it in the cell as part of a formula. You can also select a function from a menu to insert it.
Insert a SUM Function

Creating a sum formula is simple.

Click in the cell where you want to add the SUM function.
Click Insert on the menu bar.
Select Function.

This menu shows a few common functions, as well as submenus for plenty of function categories.
Select SUM.
How to Insert a SUM Function in Google Sheets.

A new formula is started in the selected cell, using the SUM function. Now, we just need to enter the cell range we want to use by typing it in, or selecting the range on the sheet.
Select the cells you want to find the sum for.
How to Insert a SUM Function in Google Sheets.
Press Enter.

The sum of the selected cells is calculated and appears in the cell.
Insert an AVERAGE Function

Next, let’s calculate the average of a cell range. Calculating an average works similar to calculating a sum, just with a different function.

Click in the cell where you want to add the AVERAGE function.
Click Insert on the menu bar.
Select Function.
Select AVERAGE.
How to Insert an AVERAGE function in Google Sheets.
Select the cells you want to find the average of.
How to Insert an AVERAGE function in Google Sheets.
Press Enter.

The average of the cell range is displayed in the cell.

MIN and MAX

The MIN and MAX functions help you find the largest and smallest numeric values in a range of cells.
Insert a MAX Function

Click in the cell where you want to add the MAX function.
Click Insert on the menu bar.Select Function.
Select Function.

Since the MAX function is one of the most commonly-used functions, it’s listed outside of the function categories.
Select MAX.

The MAX function is inserted into the formula, and now we need to select the range of cells we want to analyze.
How to Insert a MAX function in Google Sheets.
Select the range of cells you want to analyze.
How to Insert a MAX function in Google Sheets.
Press Enter.

The selected cell now displays the biggest number from the specified range.
Insert a MIN Function

The MIN function works the same way but shows the smallest numerical value in a range.

Click in the cell where you want to add the MIN function.
Click Insert on the menu bar.
Select Function.
Select MIN.
How to Insert a MIN function in Google Sheets.
Select the range of cells you want to analyze.
How to Insert a MIN function in Google Sheets.
Press Enter.

The minimum value from the selected range is displayed.

COUNT

Sheets’ COUNT functions can help you count cells with numbers, text, or blank cells. Sheets actually has a small family of COUNT functions, but the most useful are:

COUNT: Counts cells that contain numbers; blank cells and cells with text aren’t counted. Syntax: = COUNT(D2:D55)
COUNTA: Counts cells that contain any kind of data, including numbers and text. Syntax: = COUNTA(D2:D55)
COUNTBLANK: Counts blank or empty cells. Syntax: = COUNTBLANK(D2:D55)
COUNTUNIQUE: Counts cells with unique values. Syntax: = COUNTUNIQUE(D2:D55)
COUNTIF: Counts cells that meet a single criteria. Syntax: = COUNTIF(D2:D55, “PARIS”)

Insert a COUNT Function

The COUNT function tells you how many cells in a range contain numbers.

Click in the cell where you want to add the COUNT function.
Click Insert on the menu bar.
Select Function.

The COUNT function appears in the common functions group.
Select COUNT.
How to Insert a COUNT Function in Google Sheets.
Select the range of cells you want to analyze.
How to Insert a COUNT Function in Google Sheets.
Press Enter.

The number of cells with numeric values in the cell range appears in the selected cell.

Insert Functions

There are hundreds of functions available for you to perform just about any kind of calculation. Functions are sorted into categories that you can browse.
Insert a Function

Click in the cell where you want to add a function.
Click Insert on the menu bar.
Select Function.
Select a category.
Select a function.
How to Insert a Function in Google Sheets.

Most functions require some kind of input or data to calculate, called arguments.
Enter the function’s arguments.
How to Insert a Function in Google Sheets.
Press Enter.

The function is applied to the selected cell.
Get Help on Functions

If you’re not sure what category a certain function is found in, you can consult the Google Sheets function list page to find out.

Click Insert on the menu bar.
Select Function.
Select Learn more.
How to get help on Functions in Google Sheets.

A new browser tab opens to the function list. You can scroll through this list to see all of the available functions, their syntax, and a description of what they do.
Enter keywords in the search field.
How to get help on Functions in Google Sheets.

Sheets displays information about the topic you entered.

Absolute and Relative Cell References

Formulas can contain numbers, like 5 or 8, but more often they reference the contents of cells. A cell reference tells Sheets where to look for values you want to use in a formula. For example, the formula =A5+A6 adds the values in cells A5 and A6.

Using cell references is useful because if you change the values in the referenced cells, the formula result automatically updates using the new values. There are two different ways to refer to other cells while creating a formula—relative references and absolute references. Let’s take a look at the difference between the two.
Relative References

A relative cell reference is the default type.

Relative References change when a formula is copied to another cell.
B4 is an example of a relative cell reference. Absolute References

When a formula is copied, relative references update the formula based on the cell’s location.
Absolute References

An absolute cell reference ensures that the formula is always refers to the same cell, even when it’s copied and pasted.

Absolute References remain constant no matter where they are copied.
Add $ symbols to make the reference absolute.
$E$1 is an example of an absolute cell reference.
Absolute References

Absolute column and row reference ($A$1): The column and row remain constant no matter where the formula is pasted.
Absolute column reference ($A1): The column remains absolute no matter where the formula is pasted, but the row updates relatively.
Absolute row reference (A$1): The row remains absolute no matter where the formula is pasted, but the column updates relatively.

Cell Range Names

Cell references can be confusing, especially when you’re working with formulas. However, cell and range names can help. You can define a name for a single cell or a range of cells.
Name a Cell Range

Select a cell range you want to name.
Click Data on the menu bar.
Select Named ranges.

The Named Ranges pane opens. If you had any cell ranges named already, they’d be listed here.
Click Add a range.
Cell Range Names
Enter a name for the cell range.
Click Done.
Cell Range Names

The cell range is named. Now, when you create a formula, use the name to reference the cell(s) instead.
Use a Cell Range Name in a Formula

Enter a formula using the cell range name instead of the cell range.
Cell Range Names

Now when we enter a formula, we can use the cell name instead of its column and row coordinates.
Go To a Named Range

You can also use named ranges to make it easier to navigate a workbook.

Open the Named Ranges pane.
Click a named cell range.
Cell Range Names

Sheets takes you to your named cell or cell range.
Edit and Delete Cell Range Names

You can easily edit, rename, and delete cell range names.

Open the Named Ranges pane.
Click a named range’s Edit button.
Cell Range Names

Here, you can change a range’s name or adjust the cell range. You can also delete a range you no longer need.
Edit a named range’s name or range, or click Delete.
Cell Range Names

Just to be sure, you’re asked before it’s deleted.
Click Remove.
Cell Range Names
Close the Named Ranges pane.
Cell Range Names

The pane closes. Remember that once a named range is removed, you’ll need to update any formulas using that range with the correct cell range.