Dynamic Named Range In Excel

Publication Date :

08 Dec, 2018

Blog Author :

Edited by :

Reviewed by :

Download FREE Dynamic Named Range Excel Template and Follow Along!

Dynamic Named Range Excel Template.xlsx

Table Of Contents

What Do You Mean By Excel Dynamic Named Range?

Dynamic named range in Excel is the ranges that change as the data in the range changes, and the dashboard or charts or reports associated with them. So that is why it is called dynamic. So we can name the range from the name box, so the name is a dynamic name range. So, for example, to make a table as a dynamic named range, we must select the "Data," insert a table and then name the table.

For example, consider the below image showing table of samples and cost incurred. The sample size is 4 and it is reflecting in cell D2.

Dynamic Named Range in excel Intro

Now, assume that the sample size increases from 4 to 7. Instead of creating new table with formulas, we can readily add the data if we create dynamic named range in Excel as shown in the below image.

Dynamic Named Range in excel Intro Example

Henceforth, dynamic named range in Excel saves time. In this article, let us learn how to create dynamic named range in Excel.

Key Takeaways

Dynamic named range in Excel refers to ranges that change as data in the range changes and the dashboard, charts, or reports associated with them.
To create a dynamic named range, select "Data," insert a table, and name the table using the name box. This allows for easy customization of the range.
Names should start with a letter, underscore, or backslash, without spaces, and cannot be cell references like A50, B10, or C55.
Excel uses letters C, c, R, and r as selection shortcuts and months and months are treated as identical due to the function's non-case-sensitive nature.

How To Create Excel Dynamic Named Range? (Step-By-Step)

Dynamic named range in Excel automatically updates the list whenever the data expands. Let us learn how to create dynamic named range in Excel with apt examples.

The steps are:

Now, go to the "Define Name" tab. Give a name to it.

Create data validation for the month list. Select the "List" from the drop-down.

Fill the required details and give a name to define the list.

Now, we must define a new name now under the "Define Name" section.

Give a name to your list and select the range.

Under the "Refers to" section, we must apply the formula as shown in the below image.

Excel dynamically updates the dropdown list when we add additional data.

Rules For Creating Dynamic Named Range In Excel

It will help if we consider certain things while defining the names. But, first, we must follow the below rules from Microsoft, which are pre-determined.

The first letter of the name should begin with a letter or underscore (_) or backslash ()
We cannot give any space in between two words.
We cannot provide a cell reference as our name, for example, A50, B10, C55, etc.
We cannot use letters like C, c, R, and r because Excel uses them as selection shortcuts.

"Define Name" is not case-sensitive. MONTHS and months both are the same.

Examples

Example #1

Consider the below table showing months and data. Let us learn how to create an Excel dynamic named range:

Dynamic Range in Excel Example 3

Now, we need to go to the "Define Name" tab.
Click on that and give a name to it.
After that, we must create data validation for the month list.
Click on the "Data Validation," and the below box will open.
Select the "List" from the drop-down.
Select the"List," and in "Source, " we must give the name defined for the month list.
Now, the drop-down list has been created.
After that, we need to add the remaining six months to the list.
Now, return and check the drop-down list we have created in the previous step. It shows only the first six months. Whatever we have added later, it will not display.
We must define a new name now under the "Define Name" section.
Give a name to your list and select the range.
Under the "Refers to" section, we must apply the formula as shown in the below image.

Now, return and check the drop-down list we have created in the previous step. Now, it shows all the 12 months in the drop-down list. We can see everything in that column.

"Name Manager" would dynamically update the dropdown list whenever the data expands.

Example #2

Consider the below table showing inventory of various fruits. Let us learn how to create an Excel dynamic named range.

Now, using OFFSET and COUNTA functions in the Define Name feature, we can make this dynamic.

Next, click on Define Name option - Formulas to open the New Name window. And then, enter a valid name for the range. In this example, let us name it as Inventory.

Next, we enter the formula based on the OFFSET() containing the COUNTA() in the fourth field and click OK to make a dynamic named range in Excel, Experiments.

And then, we need to enter the COUNTA() in the target cell D2.