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

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.

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:

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.

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.

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.