Named ranges provide many benefits when using spreadsheets. They allow easy access to data on other sheets, make references absolute and provide meaningful names to data.
However, if the list is updated often with new records or columns of data, then the named range needs to frequently be updated also. This is an irritating and time consuming role, so this post aims to solve that problem by making the named range grow automatically when new rows and columns are added.
I was reminded of the brilliance of this feature just yesterday when I was asked by a company how they could get their formulas to include any new rows of data automatically. They had various formulas and PivotTables running on a large list of data to perform sales analysis. This data is frequently updated as more sales are made.
I ended up using the method below and had the formulas and PivotTables use the dynamic named range for their data source.
Create a Dynamic Named Range
- Click the Formulas tab on the Ribbon
- Click the Define Name button from the Defined Names group
- Type a name for the named range. The name cannot include spaces or start with a letter
- Click in the Refers to: field and enter the formula below
Change the references to cells, columns and rows in the formula to those required in your spreadsheet.
- Click Ok
The named range is created. It does not appear in the Name Box list, but it can be used by typing its name into the Name Box.
How Does the Formula Work?
The formula uses the OFFSET function to make the range dynamic. Let’s have a closer look at how this works.
- Sheet1!$A$1 is used as a starting cell for the range. This cell should be the cell in the upper left corner of your range
- The two zeros ensure that the range does not move from its base reference.
- The COUNTA(Sheet1!$A:$A) function makes the height of the range dynamic. It is used to count the number of rows that are not blank. The OFFSET function then uses this for the height of the range. Whenever a new row is added, the COUNTA function recognises it and tells the OFFSET function.
The COUNTA(Sheet1!$1:$1) function is used to find the width of the range. This ensures that new columns are included in the named range.