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.
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
Step 2: Click on the New option on the New Manager window
Step 3: Follow the sequences:
- Enter any name in the Name field.
- 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
- Then press the Ok.
Step 4: You will find the New Manager window like this picture
Step 5: Close the window.
Step 6: Now enter the name at any cell of the worksheet
Step 7: All the information will be shown
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.
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.
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)
Step 3: Press the Ok button and the Name Manager window will be like this
Step 4: Close the window
Step 5: Enter the formula at any cell and observe the 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.
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
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.
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)
Step 5: You can use this for different purposes
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
Step 2: Go to the Name box and type any name and press Enter.
Like I have entered: Device_Model_Name
Step 3: Now go to any cell and type:
Step 4: Now see the output after pressing Enter
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)
Step 2: Go to the Formulas tab and then select Create from Selection.
Formulas > Create from Selection (Shortcut Ctrl+Shift+F3)
Step 3: Select Top row, Left column, Bottom row, or Right column check box. Here I am clicking on the Top Row option
Step 4: Now Type =Price on any cell and press enter.
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.