An expanding reference (or expanding range) in Excel defines a range that expands as a formula is copied down or across cells. This is done by “mixing” absolute and relative references – making the first cell an absolute reference and the last cell a relative reference .
Example
In the example shown the formula in D4 is:
=SUM($C$4:C4)
As this formula is copied down column D, it changes as follows:
=SUM($C$4:C4) // D4
=SUM($C$4:C5) // D5
=SUM($C$4:C6) // D6
At each new row, the range is expanded to include one new row. As a result, the SUM function calculates an accurate running total.
Excel’s fill handle is a small rectangular control that appears whenever a cell or range of cells are selected. When the cursor is moved over the fill handle, it turns into a cross symbol, which can be dragged to fill in more cells.
The behavior of the fill handle varies depending on the content of the cell or range selected:
- When the selected range contains formulas, the formulas are copied to new locations and references are updated.
- When the selected range contains text or numbers with no recognizable pattern, values are copied.
- When the selected range contains text or numbers with a recognizable pattern, the pattern is continued.
When an adjacent column contains values, double-clicking the fill handle will extend values down the worksheet to match, following the behaviors described above.
