Course / Spreadsheet Basics

Copying formulas with the fill handle

Write a formula once, then copy it down a whole column with relative references. Beginner · 4 min · Fill handle, Relative references

The small square at the bottom-right corner of the selection is the fill handle. Drag it to copy a formula into neighbouring cells, or double-click it to fill down as far as the data goes.

When a formula is copied, its references move with it. =B2-C2 copied one row down becomes =B3-C3. These are called relative references.

Steps

1. In D2, subtract what was spent from the budget.

2. Select D2 and drag its fill handle down to D7. You can also double-click the handle, or select D2:D7 and press Ctrl+D.

3. Click D5 and look at the formula bar. Excel automatically changed the references to row 5.

4. In B8, total the budget column. Then drag the fill handle right to D8 to total the other two columns.

Remember

• Drag the fill handle to copy formulas; double-click it to fill down to the end of the data.
• Ctrl+D fills down and Ctrl+R fills right.
• Relative references shift as you copy, so each row gets its own calculation.

Previous: Counting cells · Next: Absolute references with $