Excel Training: Formula Autofill

The simplest formula that references a cell consists of entering an = and then clicking on the chosen cell:

excel formula equals autofill

The formula =A1 then returns the value of cell A1:

excel simple formula autofill

Now copy this cell down:

excel formula copy autofill

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:

excel copied formula autofill

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:

excel sum function autofill formula

This formula is now copied to the right:

excel copy sum function autofill formula

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:

excel copied sum autofill formula

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).