Filling and copying formulas in Excel: how it works
Drag the fill handle, the small square at the bottom right of the cell, downwards, and Excel adjusts the formula for each row: =B2*C2 becomes =B3*C3. Double-clicking it is even quicker.
Try the first exercises for freeIn the lesson Absolute and relative references
What happens when you fill a formula?
Filling means copying a formula to the cells below it or next to it. You write it once, in the first row, and Excel makes it for all the other rows. You do that with the fill handle, the small square at the bottom right of the selected cell, or by copying and pasting.
Excel adjusts the references automatically: =B2*C2 in D2 becomes =B3*C3 in D3 and =B4*C4 in D4. That way, each row calculates with its own figures. That's called a relative reference. If a cell has to stay where it is, such as a rate, you lock it with dollar signs: $F$1.
When do you fill a formula?
- You have a Total column and want price times quantity in every row.
- You have a formula for January and want the same for the months next to it: drag the fill handle to the right.
- You add rows at the bottom of your table and want the formula there too.
- Every row has to calculate with the same rate from one cell: fill =A2*$F$1 down, with dollar signs.
Filling a formula, step by step
D3 contains =B3*C3. This is the formula from D2, filled down to D3: Excel turned B2 and C2 into B3 and C3 automatically. Change the numbers or the formula and see what happens.
This is how to go about it:
- Create the formula in the first row: click D2, type the formula and press Enter.
=B2*C2 - Click D2 again. At the bottom right of the cell you see a small square: the fill handle. Point at it until the mouse pointer becomes a black plus sign.
- Drag the fill handle down to D4, or double-click it: then Excel fills the formula down to the last row of your table.
- Click D3 and look in the formula bar: Excel automatically turned B2 and C2 into B3 and C3.
=B3*C3
More examples
Filling to the right
C4 contains =C2-C3. B4 contains =B2-B3. If you fill it to the right, each column letter moves along one place: =C2-C3 in C4, and =D2-D3 in D4.
With a locked cell
B3 contains =A3*$F$1. Filled down from B2: A2 became A3, but $F$1 stayed where it was. That way, every row calculates with the same rate.
Without dollar signs
B3 contains =A3*F2. This is what =A2*F1 looks like after filling down without dollar signs: Excel looks one row lower, at F2. That cell is empty, so the result is 0.
Why do the references shift?
Excel remembers a reference as a position relative to the formula. =B2*C2 in D2 really means: the two cells to my left, on the same row. If you copy the formula to D3, Excel takes the two cells to the left of D3. That's called a relative reference.
Where it often goes wrong
- Double-clicking the fill handle only works if there's data in the column next to it: Excel fills down as far as that data goes.
- After filling, always check one formula at the bottom. If you see 0 or an empty cell where you expect a result, a reference shifted that should have stayed put.
Practise this in the lesson Absolute and relative references. The first exercises are free.
Frequently asked questions
What's the difference between filling and copying?
For the formula there's no difference: in both cases the references shift. The fill handle is quicker for the cells right next to it, while copying and pasting puts the formula anywhere you like.
How do I copy a formula without it changing?
If you want to move it, cut the cell instead of copying it: the formula then stays the same. If you want a copy, copy the text of the formula from the formula bar, or lock the references with dollar signs.
Read next
Creating a formulaOrder of operationsAbsolute references ($)Calculating percentagesWorking with multiple sheets