Exceljet

Quick, clean, and to the point

Expanding reference

Example of expanding reference in Excel

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.

Related terminology 
Example of Relative Address in Excel
Relative reference
A relative reference in Excel is a pointer to a cell or range of cells. For example, a relative reference to cell A1 looks like this: = A1 A relative addresses will change when copied to other location in a worksheet because it describes the "offset...
Example of Absolute Reference in Excel
Absolute reference
An absolute reference in Excel refers to a reference that is "locked" so that rows and columns won't change when copied. Unlike a relative reference , an absolute reference refers to an actual fixed location on a worksheet. To create an absolute...
Example of mixed reference in Excel
Mixed reference
An mixed reference in Excel is a reference where part of the reference is absolute and part is relative. For example, the following references have both relative and absolute components: = $A1 // column locked = A$1 // row locked = $A$1:A2 // first...
Example of expanding reference in Excel
Expanding reference
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...
Excel structured reference example
Structured reference
A structured reference is a special syntax for referencing Excel Tables . Structured references work like regular cell references in formulas, but they are easier to read and understand. Structured references are also dynamic, and adjust...
Example of full column references in a SUMIF function
Full column reference
Excel supports both full column, and full row references. A full column reference is a reference that refers to an entire column . For example, to sum all of the values in column A, you can use the SUM function like this: = SUM ( A:A ) Notice a full...
Example of full row references in a SUM formula
Full row reference
Excel supports both full column , and full row references. A full row reference is a reference that refers to an entire row . For example, to sum all of the values in row 1, you can use a formula like this: = SUM ( 1 : 1 ) Notice a full row...
Related videos 
How to create a mixed reference
In this video we look at how to create a mixed reference that is part relative and part absolute; useful when you want only part of a cell reference to change.