Google Sheets
Creating Simple Formulas
Introduction
When working with numerical information, Google Spreadsheets can be used to perform calculations. In this lesson, you'll learn how to create simple formulas that will add, subtract, multiply, and divide values. You will also be introduced to the basics of using cell references in formulas.
Creating simple formulas
A convenient and time-saving feature of Google Spreadsheets is its ability to add, subtract, multiply, and divide numerical information for you. Google Spreadsheets uses mathematical expressions called formulas that make handling these calculations easy. In this lesson, we'll focus on formulas that contain one mathematical operator.
Most of the time, you will be using a cell's address in the formula. This is called using a cell reference. The advantage of using cell references is that you can change a value in a referenced cell and the formula will automatically recalculate. Using cell references in your formulas will make sure the values in your formulas are accurate.
Watch the video to learn how to work with simple formulas in Google Spreadsheets.
Mathematical operators
Google Spreadsheets uses standard operators for formulas, such as a plus sign for addition (+), minus sign for subtraction (-), asterisk for multiplication (*), forward slash for division (/), and caret (^) for exponents.
All formulas must begin with an equals sign (=). This is because the cell contains, or is equal to, the formula and the value it calculates.
Using cell references
When a formula contains a cell address, it is using a cell reference. Creating a formula with cell references is useful because you can update the numerical values in cells without having to rewrite the formula.
By combining a mathematical operator with cell references, you can create a variety of simple formulas in Google Spreadsheets. Formulas can also include a combination of a cell reference and a number.
Creating formulas
In our example, we will use simple formulas and cell references to help calculate a classroom budget.
To create a formula:
- Select the cell that will display the calculated value (B17, for example).
Selecting cell B17 - Type the equals sign (=).
- Type the cell address of the cell you wish to reference first in the formula (B15, for example). A dotted border will appear around the cell being referenced.
Typing a cell address in B17 - Type the operator you wish to use. For example, type the addition sign (+).
- Type the cell address of the cell you wish to reference second in the formula (B16, for example).
Completing the formula - Press the Enter key on your keyboard. The formula calculates, and Google Spreadsheets displays the result.
The result in B17
To see how the formula recalculates, try changing the value in either B15 or B16. The formula in B17 automatically displays the new value.
Google Spreadsheets will not always tell you if your formula contains an error, so it's up to you to check all of your formulas. To learn how to do this, read our article on why you should Double-Check Your Excel Formulas.
To create a formula using the point-and-click method:
Rather than typing cell addresses, you can point and click on the cells you wish to include in your formula.
- Select the cell that will display the calculated value (E18, for example).
Selecting cell E18 - Type the equals sign (=).
- Click on the cell you wish to reference first in the formula (for example, E16). The address of the cell appears in the formula.
Clicking cell E16 - Type the operator you wish to use in the formula. For example, type the multiplication sign (*).
- Click on the cell you wish to reference second in the formula (for example, E17). The address of the cell appears in the formula.
Clicking cell E17 - Press the Enter key on your keyboard. The formula will be calculated, and the value will appear in the cell.
The calculated value in E18
To edit a formula:
Sometimes you may want to modify an existing formula. In our example, we typed an incorrect cell address in our formula, so we need to correct it.
- Double-click on the cell that contains the formula you wish to edit. The formula will be displayed in the cell.
Viewing the formula - Make the desired edits to the formula. In our example, we will replace C21 with B22.
Editing the formula - When finished, press the Enter key on your keyboard. The formula recalculates, and the new value displays in the cell.
Viewing the recalculated value
Challenge!
To work through the challenge, open GCFLearnFree L12: Classroom Budget 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.
- In cell B23, use cell references to calculate the remaining funds by subtracting B22 from B21.
- Try seeing how cell references work by changing the nonreimbursed number in B22 to another number. Notice how the formula in B23 recalculates.
- In cell E18, try using the point-and-click method to write a simple formula that multiplies the price of the easels by the number of students.
- There is a mistake in cell E24. Edit the formula in E24 to change the multiplication sign to a division sign.
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.
- Click the link at the top of this page to open the example file.
- 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 - After you have signed in to your Google account, locate and select File in the toolbar menu and then select Make a copy... from the drop-down menu.
Making a copy of the example file - The Copy Document dialog box will appear. Enter a new title for the file, then click OK.
Naming the file and clicking OK - 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