Dynamic Named Range Based on Cell Value in Excel

When working with a continuously changing data set, we may need to make our named range dynamic so that it automatically increases to accommodate newly combined entries or records to exclude eliminated data. In this article, we will see how to do a dynamic named range based on cell value in Excel.

Excel Dynamic Named Range Based On Cell Value: 5 Easy Ways to Create

Now we will go through a total of five different methods to create a dynamic named range based on cell value in Excel. The basic idea to name a range is the same- we will use the Name Manager from the Formula tab. In general, we can select a range from a spreadsheet and name it after that through this feature. Or we can select a formula to do that. As the former would result in a fixed range, it would not serve our purpose. So we will resort to using formulas to create dynamic named ranges in Excel. These methods differ only in the formulas we are using.

For each method, we are going to use the same dataset.

1. Create Dynamic Named Range Based On Cell Value with Excel OFFSET Function

In the first method, we are going to be using the OFFSET function only for the formula. This function takes three primary arguments- a reference, and the number of rows and columns it will deviate. It can also take the number of rows and columns it will return as its 4th and 5th arguments, although they are not mandatory. It then returns a value or an array depending on the number of values and their type.

So we can easily use this function to create a dynamic named range based on cell value in Excel. Follow these steps for a detailed guide.

Steps:

• First, go to the Formulas tab on your ribbon.
• Then select Name Manager from the Defined Names group section.

• As a result, the Name Manager box will appear.
• Now click on the New button on the top left corner of the box.

• Next, insert a name in the Name field of the Edit Name We are choosing Device_Models as the name.
• Then in the Refers to field, insert the following formula.

• Finally, click on OK and close the Name Manager The naming of the range is now complete.
• To test the range, go to the spreadsheet and type out the following.

• Then press Enter. You will find that the range will appear.

• If we add a value now, it will automatically update the output.

This is one way we can name a dynamic range in Excel.

Read More: OFFSET Function to Create & Use Dynamic Range in Excel

2. Combine OFFSET and MATCH Functions to Create Dynamic Named Range

Another way, you can achieve a similar result is by a combination of the OFFSET and MATCH functions. The OFFSET function gives us an output depending on the set of variables and their nature. Meanwhile, the MATCH function returns the match of a value in an array. So we can create a formula out of those that can identify a set of numbers that we can use as the range. Keep in mind, that this number identifying and isolation is based on a cell value and so we are creating a dynamic named range in Excel this way.

Follow these steps to see how we use the formula and apply it.

Steps:

• First, go to the Formulas ribbon on your tab.
• Then select Name Manager from the Defined Names group section.

• As the Name Manager box pops up, select New in the top-left corner of the box.

• In the next box, fill the Name field with the name you want.
• Also, insert the following formula in the Refers to field beneath.

• Then click on OK and close the Name Manager The dynamic named range based on cell value is now complete.
• To test the result, let’s go back to the Excel spreadsheet and insert the following formula.

• After that, press Enter.

• Now insert a value under the column and you will see the output change accordingly.

Thus, we have created a dynamic named range based on cell value in Excel.

Read More: Dynamic Range for Multiple Columns with Excel OFFSET

3. Generate Named Range for Text Values by Joining OFFSET and MATCH Functions

We can also use the same OFFSET and MATCH functions to identify text values and thus create a dynamic named range. Usage of this formula in Excel can also be included in the dynamic named range based on cell value criteria.

Follow these steps to see how we can achieve that.

Steps:

• First of all, go to the Formulas tab on your ribbon.
• Now select Name Manager from the Defined Names group section.

• After that, click on the New button on the top-left of the dialog box.

• Then enter a name in the Name field in the following box.
• After that, insert the following formula in the Refers to field to define the range.

• Now click on OK and close the Name Manager The creation of an Excel dynamic named range based on cell value for texts is thus complete.
• Now to test it out go back to the spreadsheet and type the following in a cell.

• And press Enter.

• Finally, let’s enter a value under the column we are filtering from and the output should change accordingly.

This is how we can create a dynamic named range based on text cell value in Excel.

4. Apply FILTER Function to Create One Dimensional Dynamic Named Range in Excel

Another way we can create a dynamic range is to use the FILTER function in the reference box. This function takes a range or an array as the first argument and filters values from them depending on the second argument.

Follow these steps to see how we can use this to our advantage to create the dynamic named range in Excel.

Steps:

• First, go to the Formulas tab on your ribbon.
• Then select Name Manager from the Defined Names group section.

• Now click on the New button on the top-left of the Name Manager box that will pop up next.

• Consequently, insert the name in the Name field of the following box.
• Then insert the formula in the Refers to box as the reference.

• After clicking on OK here, close the Name Manager box also. At this point, the dynamic named range is complete.
• Now to test it out, go back to the spreadsheet and insert the following formula.

• After that, press Enter.

• Now insert something under the column we are picking reference from and it will update the output.

It is another way to create a dynamic named range that can also be based on cell value in Excel.

5. Create Two Dimensional Dynamic Named Range in Excel

All of the ranges we have created up until now are single-dimensional arrays (one-column values). We can create such references using the INDIRECT, COUNTA, and ROW functions.

The INDIRECT function provides us with a reference from a string it takes as an argument. Meanwhile, the COUNTA function returns the non-empty cells from a range. Whereas, the ROW function returns the row number from the reference argument.

Here is how we can create a two-dimensional dynamic named range in Excel with the functions.

Steps:

• First, go to the Formulas tab on your ribbon.
• Second, select Name Manager from the Defined Name group section.

• Third, click on New from the top-left of the Name Manager box that pops up.

• Next, insert a name in the Name field for the range.
• Also, insert the following formula in the Refers to field below.

• Then click on OK and close the Name Manager box after that.
• To test if the formula is working or not, go back to the spreadsheet and write down the following in a cell.

• After pressing Enter you will find something like this.

• Finally, let’s test the value by inserting two values under the two columns we are taking for the two-dimensional arrays.

As you can see, the output has automatically been updated. So we can say we have created a dynamic named range in Excel as a result.

Read More: Create Dynamic Sum Range Based on Cell Value in Excel

Insert INDEX Function in Excel for Creating Named Range Based On Cell Value

We can also use the INDEX function to use as a reference. But unlike the previous ones, we will not get a dynamic output range as the result. Although we will get the output range depending on the formula. This function takes the intersection or rows and col it counts as a specific column (which it takes as the first argument).

To see how we can achieve that, you need to follow the steps below.

Steps:

• First, select the Formulas tab to find the Name Manager which you can find from the Defined Names group.

• Now click on the New button at the top left of the popped-up box.

• Now insert a name in the Name box.
• Then insert the following formula in the Refers to field below that.

• Then click on OK.
• After that, close the Name Manager box also.
• Now go back to the spreadsheet and insert the following formula to test it out.

• Finally, press Enter on your keyboard.

As a result, we can create and use the named range in Excel.

Read More: How to Create Dynamic Range Using Excel INDEX Function

Use Name Cell in Excel to Generate Named Range

The simplest way to create a named range is to use the Name Cell feature of Excel. You can find it on the left side of the formula box on the spreadsheet.

Now, follow the steps below to perform the task.

Steps:

• To use this feature, select the range you want to name and manually edit this.

• Again, if you want to recall the range, just use the name you have given and you can find so.

Define Names from Selected Range

There is yet another feature to define a name for a range. For that purpose, learn the following process.

Steps:

• Firstly, you have to select a range.

• Then, select the Create from Selection feature from the Defined Names group of the Formulas tab.

• In the consequent box, select the option that matches your criteria. In this case, it is the Top row.

• Now, you can find the range through the name of the top row as shown in the following figure.

We can also use this method to define names from a selected range in Excel.

Download Practice Workbook

You can download the workbook used for the demonstration from the link below.

Conclusion

So these were all the methods to create named ranges, both dynamic and static based on the cell value in Excel. Hopefully, you have grasped the idea of using these methods and can use them accordingly. I hope you found this guide helpful and informative. If you have any questions or suggestions let us know in the comments below.

Further Readings

• Data Validation Drop Down List with Excel Table Dynamic Range
• How to Create a Range of Numbers in Excel

<< Go Back to Dynamic Range | Named Range | Excel Formulas | Learn Excel