Excel Dynamic Named Range Based on Cell Value (5 Easy Ways)

Show output of name manager

When we need to work with a continuously changing data set, this 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.

Download the Practice WorkBook

5 Ways to Create Dynamic Named Range Based On Cell Value

1. Dynamic Named Range Based On Cell Value Using Excel Name Manager and Formulas

Let’s have a dataset of a mobile shop. In the dataset, we have Brand, Device Type, Model, and Price.

Dynamic Named Range Based On Cell Value Using Excel Name Manager

Now our target is to dynamically name a range based on cell value using the Excel Name Manager option for the above dataset.

Step 1: Go to the Formulas tab then click on Name Manager

Open Name Manager

Step 2: Click on the New option on the New Manager window

Click on New option

Step 3: Follow the sequences:

  1. Enter any name in the Name field.
  2. Enter the below formula in Refers to the field.
    =OFFSET(E4,1,0,COUNTA(E4:E100),1)
    Formula ExplanationHere we have used OFFSET and COUNTA functions. COUNTA(E4:E100) this portion of the formula counts everything from the Model column. Lastly using the OFFSET function, we are making cell references from the selected cells. For more details, we can visit this Link
  3. Then press the Ok.
    Enter rule

Step 4: You will find the New Manager window like this picture

View of Name Manager

Step 5: Close the window.

Step 6: Now enter the name at any cell of the worksheet

Enter device models

Step 7: All the information will be shown

Show output

Uses of the Dynamic Named Range

Let’s see how we can use these dynamic named ranges for calculation purposes. For this, I have created another dynamic named range called Device_Prices.

Device price range

Step 1: Now use any formula (I have used SUM, MAX, MIN) and you will get the dynamic named range to use them in the formula.

Use dynamic ranges for calculation

2. Dynamic Named Range Based On Cell Value Using Excel Using INDEX Formula

Now let’s see how we can use the INDEX function in the formula for the same problem as method 1. Here we will be using the INDEX function instead of the OFFSET. The rest of the procedures will be the same as above.

For this method, we will consider the Brand column.

Step 1: Follow the same steps from Step 1 to Step 3 same as method 1

Step 2: Now enter the below formula

=INDEX(B5:B100, 0, 0)

Enter the formula using Index function

Step 3: Press the Ok button and the Name Manager window will be like this

Name manager

Step 4: Close the window

Step 5: Enter the formula at any cell and observe the output

Index formula output

3. How to make a two-dimensional dynamic range in Excel

Up to now, we have seen a one-dimensional dynamic range in Excel. Now let’s see how we can make a two-dimensional dynamic range in Excel. For this, we will consider a dataset of some salespeople with their monthly sales. Our target is to make a two-dimensional dynamic range of sales.

How to make a two-dimensional dynamic range in Excel

Step 1: Now again do the same thing from Step 1 to Step 3 in method 1

Step 2:  Enter the Name and Formula then press OK

=$C$4:INDEX($1:$1048576,COUNTA($C:$C),COUNTA($4:$4))

Formula Explanation

Here we have used INDEX and COUNTA functions. $1:$1048576 is used for the entire worksheet. And COUNTA function is used for counting all the elements among the range. $C:$C this is the first column and $4:$4 this is the first row.

Enter formula for two di range

Step 3: Now 2D Dynamic Range is created

Step 4: Now you use the Sales range for any calculation. (This Sales range will be work as C2:E10)

use sales for sum function

Step 5: You can use this for different purposes

more examples

4. Dynamic Named Range Based On Cell Value Using Name Cell

In MS-Excel we can easily name a cell. Let’s see how to do this.

Step 1: Select any cell

Select any cell

Step 2: Go to the Name box and type any name and press Enter.

Like I have entered:  Device_Model_Name

Enter device model name

Step 3: Now go to any cell and type:

=Device_Model_Name

Go to any cell and type name

Step 4: Now see the output after pressing Enter

See the output

5. Define names from a selected range

The previous one is for one selected cell now let’s see how we can name any selected range.

Step 1: Select the range of cells (including the row or column labels)

Select the ranges

Step 2: Go to the Formulas tab and then select Create from Selection.

Formulas > Create from Selection (Shortcut Ctrl+Shift+F3)

Open formulas and create from selection

Step 3: Select Top row, Left column, Bottom row, or Right column check box. Here I am clicking on the Top Row option

Select the boxes

Step 4: Now Type =Price on any cell and press enter.

enter price

Conclusion

These are the ways to create a dynamic named range based on cell value in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this then please feel free to share it with us.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo