00:00
/
00:00
Audio/video is not supported
Please TryRefresh
Play
Fullscreen
Click and hold to drag
I. Intro
There is a wide variety of formulas in Sheets to help you make sense of your data. For example: the SUM function can be used to add up numbers, the VLOOKUP function to look for data, and the SUMIF function can be used for adding up items that meet specified conditions.
II. Steps
- To insert formulas in a cell, use the following methods:
Method 1: Directly type the formula in the cell. You will get recommended formulas as you type.
250px|700px|reset
Method 2: Click the cell, select Formulas (or the More icon > Formulas) in the toolbar, and select a formula.
250px|700px|reset
Method 3: Click the cell, click Menu > Data > Formulas, and select a formula.
250px|700px|reset
- Follow the prompts to enter the arguments, and press Enter to get the results. Click Reference data from other sheets to reference data from other sheets as needed.
Note:
- Formula prompts are collapsed by default. You can expand it as needed.
- Arguments in square brackets ([]) are optional, while arguments without brackets are required.
- Separate each argument with a comma (,). Text content should be enclosed in double quotation marks (").
250px|700px|reset
250px|700px|reset
- If needed, you can nest multiple formulas in a formula. For example, the table below shows the scores of each subject for students. You can filter out students with total scores above the average score using the IF, SUM, and AVERAGE functions together.
250px|700px|reset
- You can apply the formula to other cells by using autofill.
Note that there are two types of references for formulas. If you referenced data in the formula, the returned values will differ depending on the type of reference that is used:
Relative references
The range of data referenced changes with the cell position (this is the default option).
Example: If E2 referenced B2:C2, then E3 will reference B3:C3 when you autofill the formula downwards.
250px|700px|reset
Absolute references
The range of data referenced does not change with the cell position, but is fixed to a specific range. To do this, add the $ symbol(s) to the formula. Example:
- $D$2 means the cell is fixed (row and column numbers are fixed).
- D$2 means the row number is fixed, but the column number may change.
- $D2 means the column number is fixed, but the row number may change.
250px|700px|reset
III. Common errors
If you encounter errors while using functions, you can refer to the following for troubleshooting:
IV. FAQs