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:
- 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.
- 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.
- Go into the ‘Formulas’ tab on the ribbon
- 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]
- Insert the name of the sheet that holds your data here.
- 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.
- Again, insert the name of the sheet that holds your data here.
- 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.
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.
Comments
11 responses to “How To Create A Dynamic Named Range In Excel”
Using your example, does that mean that the dynamic range is limited to $A200? If so, I agree that the user should strive for a $A$A reference on a dedicated ‘tables data’ sheet
Or, just do ‘$A:$A-1’, since the only reason you define the endpoint is that you want to start the array at A2.
Yes, in my example the dynamic range is limited to $A200. It would be easiest to use an entire column reference, but sometimes it’s easier to change the formula according to the data, and not the other way around.
As @cffndncr suggests, you can use: COUNTA($A:$A)-1 instead
Offset is a garbage formula. Given that it’s volatile, even if you start using mid-sized data sets you are going to slow performance down to a crawl.
Wouldn’t you be better showing people how to do it using INDEX() instead? It’s just as easy to use, and won’t give people bad habits for when they start using more complicated datasets.
=Data!$A$2:INDEX(Data!$A:$A,COUNTA(Data!$A:$A))
The principle is the same, but your performance on large files will be much better.
Very nice. I had to modify your formula though since it would miss the last option.
Thanks for sharing!
Are you sure? I did a test run in a dummy workbook and it worked fine – it’s counting all non-blank cells (including headers) so it should end the array on the last occupied cell of A:A.
Then again, I haven’t used Excel in a while so it’s entirely possible that I’m just rusty =)
Ah there’s the difference – my data didn’t have a header, so that’s why I needed to add 1
Hmm it should work regardless… but without seeing your actual data I wouldn’t be able to tell why it didn’t!
Anyway, glad someone got some use out of it
Good suggestion @cffndncr
I can see why this theoretically should be a better solution, but I haven’t had any real issues with dynamic ranges (the old school way) and big datasets.
Have you done any testing on this? I’d love to hear the results.
I worked out a model for automating a bunch of the analysis we were doing a few years back, before we made the move to a dedicated analysis platform. The idea was that you’d just paste in the raw data, and it would automatically perform all the calculations regardless of the size of the dataset. The datasets weren’t particularly large (maybe 2000r x 1000c), but I was using offset extensively so that all the ranges would work no matter the size of the dataset pasted in. Performance was abysmal – we’re talking 30s of calculation time every time a single cell was modified.
I rebuilt it excluding all the volatile formulas (which was 99% offset), and it didn’t have any of the performance issues after that.
So yeah, I think it’s more to do with the number of Offset formulas used, rather than the actual size of the datasets. Once you start having hundreds/thousands of cells/ranges using volatile formulas, it really starts to affect performance.
Sounds like a good case.
I really appreciate your input on this. Thank you!
Why is a fairly ordinary capability for any reasonable Excel user referred to as ‘geek gibberish’? Or do most users just plod on summing columns?
Has the exercise file with the sample data on creating a Dynamic Range been removed from the site?