Basics
References that move when filled
Definition
A relative reference such as A1 shifts its row and column by the same distance the formula is moved when it is filled or copied.
Syntax
=A2*18
Arguments
| Argument | Meaning |
|---|---|
A2 |
A relative reference. Filled down one row, it becomes A3, then A4, and so on. |
18 |
A typed constant that stays the same in every row. |
Fill handle |
The small square at a cell's corner. Dragging it down copies the formula into the cells below. |
Worked example
A freelancer earns a flat rate per hour. Hours for three days are in A2:A4, and the pay formula starts in B2.
| A: Hours worked | B: Pay |
|---|---|
| 11 | =A2*18 |
| 13 | =A3*18 |
| 19 | =A4*18 |
Formula: =A2*18
Result: 198. After filling down, B3 shows 234 and B4 shows 342, because each copy points at the hours in its own row.
Practice
Cells D2:D4 hold 30, 45, and 60. Write a formula in E2 that divides D2 by 5, then fill it down through E4.
- In E2, write a formula that divides D2 by 5. Begin with an equals sign.
- Fill that formula down through E4, or write the shifted formula in E3 and in E4.
Step 1 of 2
Enter =D2/5 in E2.
E3 becomes =D3/5 and E4 becomes =D4/5. The results are 6, 9, and 12.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
E2 now holds a formula.
E4 now shows 12.
The next lesson is Locking a cell with dollar signs.
Related functions
After the column is filled down, SUM can total the new results in a single cell below them.
Common mistake
Pointing every row at a single rate cell with a plain reference: =A2*F1 filled down becomes =A3*F2, which reads an empty cell and returns 0.