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

YouTube player

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 are self-contained sets of instructions. For example, we use the SUM and COUNT functions in the image below to calculate an average.

Functions in a formula
Functions Inside a Formula

Formulas are flexible and there are many ways to arrive at the same outcome. We could simplify the formula by using the AVERAGE function as such: =AVERAGE(C3:C6).

Functions need a set of parenthesis after the function name, typically filled with inputs for the function to perform its calculation. For the SUM function above, the input is the cell range of C3:C6. 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 unsure 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 forms, which we discuss in more detail here.


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

The third is the ampersand (&), 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