excel dynamic named range

Creating a dynamic named range in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

When making complex Excel solutions is often the case that you will need to manage many different lists of certain values e.g. for dropdowns, formulas etc. It is a real pain to manage lists that change very often. The clean solution is to define a dynamic named range which will adapt to the list of parameters within a certain column.

Defining a dynamic named range

Create a list of items

Some Excel Range list
Some Excel Range list
Create your list of items. It is often best to keep your lists on a separate Worksheet and each column topped with a header explaining what each list contains. Watch out for BLANK as they will not be supported by the dynamic named range.

Open the Excel Name Manager

Go to the FORMULAS ribbon and open the Name Manager within the Defined Names group.

Create a New Named Range

Hit the New.. button to create a new Named Range. Next provide the name for your Excel dynamic named range.

Create a new Excel Named Range
Create a new Excel Named Range

Remember that Excel range names cannot contain spaces and need to start with a letter character

Provide the dynamic named range formula

Provide the formula for your dynamic named range. Assuming your worksheet name is NameOfWorksheet the formula should look like this:

=OFFSET(NameOfWorksheet!$A$1;1;0;COUNTA(NameOfWorksheet!$A:$A)-1)

See an example below:

Dynamic Named Range
Dynamic Named Range

How does the dynamic named range formula work?

How does it work? Well the OFFSET function takes 5 arguments:

  1. the reference cell
  2. the offset number of rows to move
  3. the offset number of columns to move
  4. how many rows of data to return (optional)
  5. how many columns of data to return (optional)

See now that what our dynamic named range formula does is:

  • move 1 cell down from the “List of names” cell to the first cell of the list
  • return a range of unempty rows – for as many rows as there are un-empty cells in the entire column minus 1 (minus the first cell of the column)

Therefore to sum up, the formula returns a range of all unempty cells within the given column offset by 1 row (fro the header). The definition of the Named Range is a formula hence will recalculate automatically.

The Named Range formula recalculates based on the calculation settings. If you turn of Automatic Calculation be aware that the Named Range will need to be recalculated manual or else it might show an outdated range if you add/remove rows

Making an Excel dropdown with a dynamic populated list

Let’s now make a common use the list of names which we defined to create an Excel dropdown. This way the dropdown will only be populated with the items defined in the dynamic named range list.

Dynamic Excel Dropdown
Dynamic Excel Dropdown

Good practice to using dynamic named ranges

My personal experience is that lists should be in hidden worksheets (sometimes good to make it even “very” hidden) in which each column will represent a certain list of values which can be reference by a certain dynamic named range. This will make it easy for you to manage your lists and not worry about the number of their items increasing or decreasing.

Related Posts

2 thoughts on “Creating a dynamic named range in Excel”

  1. Great article! One question: I believe OFFSET is a volatile function. Is it still volatile when it is used as a name range as suggested here? If so, I could see this being a large performance drag if named ranges, that are volatile, are used frequently in the rest of the workbook – every formula using it would also be volatile.

    1. Very good point Alex! I believe that it still will be volatile as it is a Dynamic Named Range afterall :). It adjusts its reference to your list automatically hence indeed will require a recalculation every time.

      But I wouldn’t worry about this too much in everyday situations – this will only might cause some performance issues if you have LOADs of Dynamic Named Ranges defined. Otherwise you are safe.

Leave a Reply