Dynamic named ranges in Excel

Commonly in your input form you might have validation on a drop-down field ensuring the user picks their selection from a pre-defined list.


In this example we have named the range ‘ListRegions’ and set it across the cell range B4:B7 to ensure it includes all 4 regions.




We’ve set the field up with Data Validation to lookup the list.




So the drop-down list populates with the 4 available options.




In a lot of cases, this static named range is adequate.


But what if you want it to grow when new entries are added to the region list? What if the organisation expands abroad and there are new regions to add. With a static list you’ll have to manually keep updating what cells the ListRegions range looks up against. This is where a dynamic named range is useful.


A dynamic range will automatically grow when entries are added while also maintaining the validation on the drop-down field. In this example the range begins in cell B4 on Sheet2 so the formula you should use in your Name Manager for ListRegions is:






And the drop-down field will automatically include the new entries.



Leave a Reply

Your email address will not be published. Required fields are marked *