What do You Mean by Excel Dynamic Named Range?

How to Create Excel Dynamic Named Range? (Step by Step)

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.

  • We must first create a list of months from Jan to Jun. Then, 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.

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

  • 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 referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more 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.

This article has been a guide to a dynamic named range in Excel. Here, we discuss creating a dynamic range in Excel and practical examples and downloadable templates. You may learn more about Excel from the following articles: –

  • VBA RangeDynamic Chart in ExcelExcel Dynamic TableUse INDEX Excel FunctionExcel Date Picker