Create a Dynamic Named Range
A dynamic named range automatically adjusts as data is added or removed. In the first example, a dynamic named range is defined for data containing numerical values. In the second example, a dynamic named range is defined for data containing text values.
Note that a dynamic named range will not appear in the Name Box dropdown list. However, you can type the name in the Name Box to select the range on the worksheet.
With Data Containing Numerical Values
To create a dynamic named range for Column B, starting at B2, where the data contains numerical values...
- Select 'Formulas > Defined Names > Defined Name' or 'Insert > Name > Define'
- Define the dynamic range as follows:
- Name: MyRange
- Refers to:
- =$B$2:INDEX($B$2:INDEX($B:$B,ROWS($B:$B)),MATCH(9.99999999999999E+307,
$B$2:INDEX($B:$B,ROWS($B:$B)))) - or
- =OFFSET($B$2,0,0,MATCH(9.99999999999999E+307,$B$2:INDEX($B:$B,ROWS($B:$B))))
- Click OK
Sample Workbook: Download
With Data Containing Text Values
To create a dynamic named range for Column B, starting at B2, where the data contains text values...
- Select 'Formulas > Defined Names > Defined Name' or 'Insert > Name > Define'
- Define the dynamic range as follows:
- Name: MyRange
- Refers to:
- =$B$2:INDEX($B$2:INDEX($B:$B,ROWS($B:$B)),MATCH(REPT("z",255),
$B$2:INDEX($B:$B,ROWS($B:$B)))) - or
- =OFFSET($B$2,0,0,MATCH(REPT("z",255),$B$2:INDEX($B:$B,ROWS($B:$B))))
- Click OK
Sample Workbook: Download