Using Formulas In Google Sheets

Without formulas, Google Sheets would be the same as a paper ledger. Formulas allow you to write a set of instructions for a calculation.

All formulas begin with an = sign. Once you type the =, Google Sheets expects a formula to follow. There are several ways to proceed with your formula. Listed below are the different types of items that you can add next.

Get your copy of the spreadsheet containing the examples below.

Video Tutorial

Video thumbnail
YouTube Video on Creating Formulas

Parts of a Formula

A formula contains many different parts. Each formula contains at least one of these parts. We will expand on each type below.

Functions

Functions are self-contained sets of instructions. For example, in the image below, we use the SUM and COUNT functions to calculate an average. Further, we could simplify the formula by using the AVERAGE function as such: =AVERAGE(C3:C6).

Functions in a formula
Functions Inside a Formula

Functions always need a set of parenthesis after the function name, typically filled with inputs for the function to perform its calculation. These inputs, also called arguments, can be optional or required, and there can be zero, one, or more than one of them.

A list of suggestions appears as you type. As you continue to type, the list of functions gets smaller. If you see the function you want, click it for automatic completion. If you also want the cell reference that Google Sheets suggests, Google Sheets can finish the formula for you by pressing tab.

List of function suggestions
Function Suggestions

If you continue typing until you enter the first parenthesis, the function suggestion will help with the selected function, including its syntax and an example for reference. If you need more information, click the “Learn more” link near the bottom of the help box to open a complete article.

Suggestions just for SUM
Function Suggestion Just for SUM

Inserting Functions Through the Menus

Every function in Google Sheets is also accessible through the menus. Go to the Insert menu, then choose Function. Using this menu is slower than typing, but it can be helpful if you are not sure which function you need.

Inserting a formula through the menus
Using the Menus

Cell References

Cell references in a formula
Cell References Inside a Formula

In Google Sheets, cell references tell a formula where its data is. They come in several different forms, which we discuss in more detail here.

Operators

Operators tell your formula what type of operation to perform. There are several types of operators.

Operators in a formula
Arithmetic and Reference Operators

Arithmetic Operators

Operators that perform basic math are called arithmetic operators. They are +,-,*,/ along with % to turn a number into a percent and ^ for exponents.

Comparison Operators

Next are the comparison operators. These operators allow you to analyze two values. The result is TRUE or FALSE. The options are =,>,<,>=,<=, and <>. <> represents not equal to.

Concatenation Operators

Thirdly is the ampersand (&) which is the only concatenation operator. Use it to join two strings. The formula ="United"&" States" would produce the output United States. Notice the space before States.

Reference Operators

Last are the reference operators which help you work with cell references. They are : and ,. The : is used in the creation of a range. A1:B2 would contain A1, A2, B1, and B2. The , joins multiple references. An example of this would be A1:B2,C1:D2.

Named Ranges

You can create names to refer to ranges of cells to make them easier to use and remember. Named ranges were not used in the example spreadsheet for this page as they are a more advanced technique. However, this post discusses what they are and how to use them.

Formula using a named range
Named Range (from another spreadsheet)

Sharing Your Creation

Now you can share your creation with the world, or at least with a coworker. Google Sheets offers robust sharing options. Let’s discuss them so you can choose the best option for you.

Leave a Comment