Overview
Get started on how to create formulas and use built-in functions to perform calculations and solve problems.
Important: Excel worksheet functions may incur errors between different versions of Excel.
Create a formula that refers to values in other cells
1. Select a cell.
2. Type the equal sign “=“.
Note: Formulas in Excel always begin with the equal sign.
3. Select a cell or type its address in the selected cell.
4. Enter an operator.
- “+” Addition
- “–” Subtraction
- “*” Multiplication
- “/” Division
- Functions (Predefined mathematical formulas)
5. Select the next cell, or type its address in the selected cell.
6. Press Enter. The result of the calculation appears in the cell with the formula.
Relative, Absolute, and Mixed References
Excel allows you to use three types of cell references in formulas: relative, absolute, and mixed. Understanding how to switch between these reference types is crucial for creating flexible and accurate formulas.
Relative References
By default, Excel uses relative cell references in formulas. A relative reference adjusts when you copy or move the formula to another cell.
For example:
The formula =B4*C4 in cell D4 and copy it to D5
The formula will automatically adjust to =B5*C5
Absolute References
To prevent a cell reference from changing when you copy or move a formula, you can use an absolute reference by adding a dollar sign ($) before the column and row coordinates.
For example:
=$B$4*$C$4 will remain the same no matter where you copy or move the formula.
Mixed References
You may want to lock either the row or column coordinate while allowing the other to adjust relatively. This is called a mixed reference.
For example:
=B$4*C$4 would keep the row fixed at 4 but allow the column to adjust.
To switch between reference types in Excel:
1. Select the cell containing the formula you want to modify.
2. Click in the formula bar to edit the formula.
3. Position the cursor before the cell reference you want to change.
4. Press the F4 key to cycle through the reference types (relative, absolute, and mixed).
Each time you press F4, the cell reference will switch to the next reference type in the following order:
| For a formula being copied | If the reference is: | It changes to: |
 |
$A$1 (absolute column and absolute row) | $A$1 (the reference is absolute) |
| A$1 (relative column and absolute row) | C$1 (the reference is mixed) | |
| $A1 (absolute column and relative row) | $A3 (the reference is mixed) | |
| A1 (relative column and relative row) | C3 (the reference is relative) |
View the formula
1. When a formula is entered into a cell, it also appears in the Formula bar.
2. To see a formula, select a cell, and it will appear in the formula bar.
Enter a formula that contains a built-in function
1. Select an empty cell.
2. Type an equal sign “=” and then type a function. For example, =SUM for getting the total sales.
3. Type an opening parenthesis “(“.
4. Select the range of cells, and then type a closing parenthesis”)“.
5. Press Enter to get the result.