How To Create A Dynamic Named Range In Excel

Creating a dynamic named range in Excel might sound like geek gibberish, but it’s a super powerful tool and really easy to use. This article explains why you need it and how to roll your own.

Excel image from Shutterstock

What is a dynamic named range?

A dynamic named range is a named range that automatically fits the data you put into it. That means it’ll never be too small or too big.

This has a lot of uses, but one of the most common (and the one this article will revolve around) is the combination of the dynamic named range and a drop-down menu.

If you already know what a “standard” named range is then you’re probably as psyched as I am. If you don’t, I’ll help you out below.

The standard named range

A named range is a group of cells in Excel that you give a name. For example, you can choose to give range A1:C55 the name “Employees” and it will give you the possibility to refer to this range by simple writing “Employees” in the formula instead of “A1:C55”.

This makes it a lot easier to refer to ranges in Excel. Especially if you use the same ranges in multiple formulas. Here’s how create a named range with these 4 (easy) steps:

Step 1

Select the range A2:A56 (collection of cells) that you want to give a name.

[clear]

Step 2

Type in the desired name which in this case is “Employees” right below the ribbon to the left and press ‘Enter’.

[clear]

Step 3

That’s it!

Creating a name for a range in Excel is super easy. Now you can use it with formulas, drop-down menus and other exciting stuff to save time and keep a keen overview of your references.

So why use a dynamic range?

A dynamic named range is a standard named range on steroids. Making a range dynamic, enables it to expand itself automatically and include in new data.

This is extremely useful when you have something that refers to a list in Excel and you know that there’ll be added new data to it eventually. Usually, this means you will have to waste spend time rebuilding that something every time new data goes into the list.

The usual workaround for this, when using named ranges, is: Simply to select more cells than is used by the data in order to keep the named range large enough to hold the future data.

But there are 2 major drawbacks from this workaround:

  1. The first one is that it will make your spreadsheet slow. When working with large spreadsheets, using unnecessarily big named ranges in advanced formulas will eat up all your processing power.
  2. The second and very relevant drawback is that it can mess up your drop-down menus like nothing else – and that’s something you want to avoid!
    Look at this example.

[clear]

In scenario #1 (to the left) we have a range (the green line) that refers to the entire list of employee names. There’s no data outside, and no empty cells within the green line.

In scenario #2 (to the right) there are a lot of empty cells within the green line.

When creating a drop-down menu based on the ‘Full name’ data we get two very different results.

Where in scenario #1 the drop-down menu is neatly fitted to the data, whereas in scenario #2 the drop-down menu is too big and contains blank rows. Not good!

Perhaps you can deal with a few blank rows. But imagine having 15? 20? Besides being annoying to scroll that much, worst of all it looks extremely unprofessional. Annoying, right? Don’t worry, the solution is coming right up.

How to create a dynamic range

In order for you to get the most out of the following guide, please download the exercise file here (if you haven’t already) and follow these steps.

  1. Go into the ‘Formulas’ tab on the ribbon
  2. click on ‘Name manager’ like in the picture below.

[clear]

In the new window that comes up, you’ll see the named range you created earlier “Employees”. We’re going to create a new named range now, that’s also called “Employees”.

Since you can’t have two ranges with the same name, delete the one you created earlier by simply clicking on it and then click ‘Delete’.

In the ‘Name manager’ press the ‘New…’ button in the upper left corner.
In the next window called ‘New Name’ type in “Employees” as the name and in ‘Refers to:’ you type in the following formula:

=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A2:$A200),1)

Just like I’ve done in the picture below:

[clear]

Then click ‘OK’.

Congratulations! Now you have a working dynamic named range that is used by the drop-down menu in cell B3 in the ‘Lookup tool’ sheet. Go ahead – try and click it to see the result!

Using a drop-down menu with a dynamic named range

In the exercise file, I’ve already set up the drop-down menu to work with the named range ‘Employees’ which you’ve just made dynamic.

In your own Excel files, you might not already have a drop-down menu set up to work with a named range.

Here’s how you do it:

Click the ‘Data’ tab in the ribbon. Then click the ‘Data Validation’ button.

In the new window click ‘Allow:’ and then choose and click ‘List’.

[clear]

After this, you’ll have to enter the ‘Source:’ of the drop-down menu. Type an equal sign + the name of your dynamic named range. In this case =Employees.


[clear]

Put it to the test

Try and enter some new names in column A in the ‘Data’ sheet. You will see that they instantly get included in the drop-down menu.


[clear]

The formula explained

Unfortunately, you can’t use the same formula to create the dynamic named range every time. It depends on the placement of your data.

For that reason, let’s break the formula down into each part, making it possible for you to adapt it to your spreadsheet in no time:

Editor note: If above image is too broad, insert below picture and steps 1-4.

[clear]

  1. Insert the name of the sheet that holds your data here.
  2. Change this to the cell that holds the first entry in your data. I’ve set this to $A$2 to avoid including the headline ‘Full name’ in the drop-down menu.
  3. Again, insert the name of the sheet that holds your data here.
  4. If your list doesn’t have headlines you can set this to $A:$A instead. If your data (without headlines) begins anywhere below the first row of the sheet, insert the actual starting row as the first row in this reference. Then set the second reference as high (numerically) as possible. In this case, the reference to $A$200 is just a random row way below the end of the data.
  5. What now?

    Now you’ve learned how to create a dynamic named range and use it with a drop-down menu.

    Try to pick a name from the drop-down menu in the exercise file and see that employee’s salary in cell C3.

    The combination of a lookup formula and a drop-down menu is often used. If you use formulas like VLOOKUP or INDEX + MATCH in your spreadsheets, then try to implement drop-down menus in the cells with the lookup values (the stuff you’re looking for) wherever it makes sense.

    And whenever you use a drop-down menu be sure to use it with a dynamic named range.

    The dynamic named range has many other uses too. A drop-down menu is just one of many things in Excel that refers to ranges. Almost every time you refer to a range you can use a dynamic named range to improve your spreadsheet usability and durability.

    Got any questions about this tutorial? Or dynamic ranges in general? Let me know in the comments below!


    Kasper Langmann is an Excel lover and co-founder of the spreadsheet blog Spreadsheeto.


The Cheapest NBN 50 Plans

Here are the cheapest plans available for Australia’s most popular NBN speed tier.

At Lifehacker, we independently select and write about stuff we love and think you'll like too. We have affiliate and advertising partnerships, which means we may collect a share of sales or other compensation from the links on this page. BTW – prices are accurate and items in stock at the time of posting.

Comments


11 responses to “How To Create A Dynamic Named Range In Excel”