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
In this first example, I have the following list of names.
This list is used to populate a Data Validation list. But the list may grow and shrink in size so lets make it dynamic.
- 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
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A))
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$2 is used as a start cell for the range. This avoids the header which we do not want in the Data Validation list.
- 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.
Dynamic Named Range for Rows and Columns
The previous named range was only dynamic in height. If you needed a range dynamic in width also for when columns are added and removed you could use the following formula.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
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.
This example also uses Sheet1!$A$1 as the start cell as maybe we want to include the headers.
Although these examples count column A and row 1 for find the edges of the rage, please bear in mind that these may need to be changed to work for your ranges.
Leave a Reply