A Google Sheets formula allows you to perform calculations based on data entered into a spreadsheet. You can use such formulas to calculate basic numbers, such as addition or subtraction, and for more complex calculations, such as payroll deductions or averages. An important advantage of using a spreadsheet is that the formulas are dynamic: if you change the data in the spreadsheet, the answer is automatically recalculated wherever it appears, without having to re-enter the formula. This guide covers the steps for creating and using formulas and is intended for those with little or no experience with spreadsheet programs.
- Create a basic formula: start with an equal sign
- Using a pointer to add cell references
- Mathematical Operators in Google Sheets Formula
- Workflow in Google Sheets
Create a basic formula: start with an equal sign
The steps for creating a basic formula are the same as for writing more complex formulas. In our example formula, we will first add the numbers 5 and 3 and then subtract 4. The final formula looks like this:
= A1 + A2 - A3
Enter the following information in the appropriate cells:
When creating a formula in a Google spreadsheet, you always start by typing an equal sign in the cell where you want the answer.
Click on a cell A4 mouse pointer.
Enter an equal sign ( = ) to cell A4.
After the equals sign, add links to the cells that contain the data.
By using data cell references in a formula, the formula automatically updates the answer if the data in cells A1, A2, or A3 changes.
Using a pointer to add cell references
The best way to add cell references is to use the point-to-point function, which lets you click on the cell containing your data to add the cell reference to the formula.
After adding the equal sign:
Click on a cell A1 mouse pointer to enter a cell reference in a formula.
Enter a plus sign ( + ).
Click on a cell A2 mouse pointer to enter a cell reference in a formula.
Enter a minus sign ( – ).
Click on a cell A3 mouse pointer to enter a cell reference in a formula.
Click Enter on keyboard. The answer should appear in the cell A4.
Click on cell A4. Full formula
= A1 + A2 - A3
displayed on formula bars above the worksheet.
Mathematical Operators in Google Sheets Formula
As you can see from the previous steps, writing a formula in a Google Spreadsheet is not difficult. Just combine your data cell references with the correct math operator.
The math operators used in Google Sheets (and Microsoft Excel) formulas are similar to those used in math class:
Subtract – Minus Sign (-)
Complement – plus sign (+)
Separator – slash (/)
Multiplication – asterisk
Exposure – caret (^)
Workflow in Google Sheets
- When more than one operator is used in a formula, Google Sheets follows a specific order of operations, which can be changed by adding parentheses to the equation. An easy way to remember the order of operations is to use the abbreviation BEDMAS: B
- rackets E
- exponents d
- IVISION m
- duplication A
- edition s
All operations in parentheses are performed first, followed by any exponents.
Google Sheets then considers the division or multiplication operations as equally important and performs those operations in the order they are performed from left to right in the equation.
The same goes for the next two operations: addition and subtraction. They are considered equal in the order of operations. Whatever appears first in the equation is executed first.