Range names can make referencing a range of cells much easier in Excel. They provide a meaningful name for cells that works across worksheets.
As a table of data grows or shrinks over time when new rows and columns are added or deleted, the range name needs updating.
Using the Offset function in Excel you can create a dynamic range name. One that increases and decreases in size as the data in the range changes. Any charts or PivotTables that use the data from the range can then also be updated.
Create a Dynamic Range Name
- Click the Formulas tab on the Ribbon and then click the Define Name button
- The New Name dialogue box appears. Type a name in the Name: box. This name cannot include spaces, start with a number or be the name of a cell reference e.g. D3
- Enter the formula below in the Refers to: box
- Click Ok
The range name is created. It will not appear in the list from the Name box, but you can find the range by entering the name in the box.
How the Offset Function Works
The Offset function is what makes the named range dynamic. The following is a breakdown of the formula used above.
- Cell A1 on Sheet 1 is used as the starting cell reference. This should be the cell in the upper left hand corner of your range.
- The two zeros ensure that the starting cell does not move anywhere on the sheet. These arguments are very useful to allow the range to move around a sheet, which is not necessary in this case.
- The COUNTA functions are used to find the height and then the width of the range. It counts the number of non-empty cells for the Offset to use.
Both the height and width are dynamic in this example. It may be however that only the height changes and the width should be static. A value such as 5 can be used to specify how many columns wide the range is.
When using Excel VBA you can make use of the Offset property to move around a worksheet and select ranges.