Learning Excel: Fill down
Filling down
Fern wants to know the profit of every day. "So do I have to type the same formula six times?" No: type it once, then fill it down.
Type it once
If E2 holds a formula, you can fill it down: drag the small square at the bottom right of the cell, the fill handle, as far as you want. Excel copies the formula into every cell.
Look at E3: it doesn't say =C2-D2, but =C3-D3. Excel adjusts the references to the new row by itself.
=C3-D3
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Day | Customers | Revenue | Costs | Profit |
| 2 | Tuesday | 84 | £612.40 | £238.00 | £374.40 |
| 3 | Wednesday | 131 | £978.60 | £312.00 | £666.60 |
| 4 | Thursday | 96 | £701.20 | £255.00 | |
| 5 | Friday | 212 | £1,694.80 | £498.00 | |
| 6 | Saturday | 247 | £1,987.50 | £561.00 | |
| 7 | Sunday | 175 | £1,342.90 | £402.00 | |
| 8 | Total | ||||
| 9 |
Relative references
Excel doesn't remember "C2", but "the cell two columns to my left, on my own row". A reference like that is called relative: it moves along when you copy the formula somewhere else.
Fill one row down, and every reference moves down one row with it. Just look at the formula in E5.
=C5-D5
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Day | Customers | Revenue | Costs | Profit |
| 2 | Tuesday | 84 | £612.40 | £238.00 | £374.40 |
| 3 | Wednesday | 131 | £978.60 | £312.00 | £666.60 |
| 4 | Thursday | 96 | £701.20 | £255.00 | £446.20 |
| 5 | Friday | 212 | £1,694.80 | £498.00 | £1,196.80 |
| 6 | Saturday | 247 | £1,987.50 | £561.00 | £1,426.50 |
| 7 | Sunday | 175 | £1,342.90 | £402.00 | £940.90 |
| 8 | Total | ||||
| 9 |
Try it yourself
In E2, work out Tuesday's profit: the revenue minus the costs. Then fill the formula down to E7.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Day | Customers | Revenue | Costs | Profit | |
| 2 | Tuesday | 84 | £612.40 | £238.00 | ||
| 3 | Wednesday | 131 | £978.60 | £312.00 | ||
| 4 | Thursday | 96 | £701.20 | £255.00 | ||
| 5 | Friday | 212 | £1,694.80 | £498.00 | ||
| 6 | Saturday | 247 | £1,987.50 | £561.00 | ||
| 7 | Sunday | 175 | £1,342.90 | £402.00 | ||
| 8 | Total | |||||
| 9 |
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson