Sheets AcademySheets Academy
Premium
Formulas

Filling a formula down

A monthly orders export has five hundred rows, and each one needs an amount: the price multiplied by the quantity. Writing the formula for the first row doesn't take long. But typing it in another 499 times would take until evening.

You don't need to write it again at all. Excel copies a formula to neighboring cells with one mouse movement, and this is called filling.

The fill handle

You drag the fill handle: the small square in the bottom right corner of the selection border. The table below has five orders, and the formula is already in the first row, in D2.

  1. Select the cell with the formula.
  2. Point to the fill handle: the pointer turns into a thin cross.
  3. Hold down the mouse button and drag down to the last row of the table.
Fill the formula from D2 down to D6
fx
A
B
C
D
E
1
2
3
4
5
6
7
8
Item
Price
Qty
Amount
Chair
250
4
Desk
890
2
Lamp
95
6
Shelf
180
3
Rug
110
5
Sheet1
Select D2. There’s a small square in the bottom right corner of the selection border: that’s the fill handle. Point to it, hold down the mouse button and drag down to D6.

Filled formulas stay formulas. If the price changes in any row, that row's amount recalculates by itself.

Dragging down five hundred rows takes a while. Instead, double-click the fill handle: Excel fills the formula down to the end of the table by itself. It finds the end by looking at the neighboring column with data.

A formula remembers the path, not the address

After filling, D2 has =B2*C2, and D3 already has =B3*C3. This isn't a mistake. Excel doesn't remember the addresses themselves, but where they are relative to the cell with the formula.

For D2, =B2*C2 means: "take the cell two columns to the left and multiply it by the cell one column to the left." From any other row, this path leads to the price and quantity of that row's item:

Click the formulas in column D.
ABCD1ItemPriceQtyAmount2Chair25043Desk89024Lamp9565Shelf1803
The rule in the formula in D2, the same for every row:
2 columns to the leftB2
1 column to the leftC2
From D2, the path "2 columns to the left" leads to B2, and "1 column to the left" leads to C2. That's why in this row the formula looks like =B2*C2.

Addresses like these are called relative references. Thanks to them, one formula works for the whole table, no matter how many rows it has.

You can also fill to the right when the data runs across columns. The path in the formula is kept the same way, only now the letters in the addresses change, and the row numbers stay. For example, the formula =B2+B3 from column B, filled one column to the right, becomes =C2+C3.