Excel Training: Formula Autofill
The simplest formula that references a cell consists of entering an =
and then clicking on the chosen cell:
The formula =A1
then returns the value of cell A1:
Now copy this cell down:
You can notice that the cell reference has been incremented according to the direction of the copy (when copying down, the row number is increased by 1 at each copy).
Initially, cell B1 contains the formula =A1
and at the end cell B7 therefore contains the formula =A7
:
Copying a function
Although formulas and functions will not be detailed until the next lesson, know that the incremented copy is also usable with functions.
To illustrate this with an example, the formula =SUM(B2:B5)
here returns the sum of cells B2 to B5:
This formula is now copied to the right:
The cell range is then increased by 1 column each time.
Cell B6 returns the sum of cells B2 to B5, cell C6 that of cells C2 to C5, and cell D6 that of cells D2 to D5:
When you copy functions, the references to the relevant cells are incremented according to the direction of the copy (exactly as in the previous example with =A1
).