The advantage of copying when using references
Thanks to references a formula does not have to be written again and again: it is written once and copied. There are three ways to copy: dragging the fill handle (the small square at the lower-right corner of a cell) with the mouse, Ctrl+C and Ctrl+V, and also Ctrl+D (fill the cell above downward) and Ctrl+R (fill the cell on the left to the right). When copying, Excel changes relative references according to how far they move and leaves the parts fixed with $ alone. That is why one formula with mixed references can be spread over a whole table. AutoFill continues a series from two starting numbers such as 1, 2, and also writes the days of the week and the names of months. To centre a table heading across several cells, use “Merge & Center” on the Home tab; to split long text into several lines inside a cell, use the “Wrap Text” button.
| Way | How |
|---|---|
| fill handle | drag the lower-right corner of the cell |
| Ctrl+C, Ctrl+V | copy and paste |
| Ctrl+D | fill down from above |
| Ctrl+R | fill right from the left |
A sports club leader wants a timetable of start times for four teams. The leader filled the first cell, wrote “the previous one plus 30 minutes” in the next, and dragged it down. The table appeared without typing dozens of cells by hand, and without slips.
Planners, accountants and data analysts copy one formula to fill large tables quickly.
On paper, write a plan for 6 days: 10 pages on day one, then apply the rule “2 pages more than the day before”, and notice that the rule alone produces the whole list.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 2 | 3 | 4 | |
| 2 | 2 | 4 | 8 | 16 |
| 3 | 3 | 9 | 27 | 81 |
| 4 | 5 | 25 | 125 | 625 |
“One formula – a whole table”: pairs build a 10 × 10 multiplication table by writing only one formula, and the teacher times it. The winner is the pair that writes the formula correctly with the fewest moves.