Rifat Hassan
A dynamic named range is a pretty important and helpful tool that we often have to create while working in Excel. Today I’ll show you how you can create a dynamic named range in Excel with proper examples and illustrations.
Download Practice Workbook
3 Methods to Create Dynamic Named Range in Excel
Here we’ve got a data set with the Names of some students and their Marks in English in a school called Sunflower Kindergarten.
Today our objective is to create a dynamic named range of the students.
1. Use OFFSET Function to Create One Dimensional Dynamic Named Range in Excel
You can use the OFFSET function of Excel to create a one-dimensional named range.
Step 1:
➤ Go to the Formulas > Define Name tool in the Excel toolbar.
➤ Then click on the drop-down menu. From the options available, select Define Name.
Step 2:
➤ You will get a dialogue box called New Name.
➤ In the Name box, enter the name of your named range (I have named it as Students), and from the Scope box, select Workbook.
Finally, enter this formula in the Refers to box:
=OFFSET(Sheet1!B4,0,0,COUNTA(Sheet1!B:B)-1,1)Notes:
- Here, Sheet1 is the name of my worksheet.
- B4 is the first cell of my range.
- And B:B is the whole column of my range.
- Also if you don’t have any Column Header with your column, omit the -1 from the COUNTA function. Use
COUNTA(Sheet1!B:B)
Step 3:
➤ Click on OK. A dynamic named range called Students will be created containing all the students’ names.
➤ To check whether it does work properly or not, select any cell in your worksheet and enter:
=StudentsThe names of all the students will be displayed properly.
Step 4:
➤ And finally, to check whether it is a dynamic range or not, enter a new name Rilee Royes at the end of the data set.
➤ You will find the new name added automatically to the list.
Similar Readings:
2. Use FILTER Function to Create One Dimensional Dynamic Named Range in Excel
If you have an Office 365 subscription, you can use the FILTER function of Excel instead of the OFFSET function to create a one-dimensional dynamic named range.
The steps are almost similar to the steps mentioned in method 1.
Just in Step 2, in place of the OFFSET formula, use this formula:
=FILTER(Sheet1!B:B,((Sheet1!B:B<>"")*(Sheet1!B:B<>Sheet1!B3)))Notes:
- Here Sheet1 is my worksheet name.
- B:B is my whole column of the range.
- And B3 is the cell reference containing my Column Header. Omit this part *(Sheet1!B:B<>Sheet1!B3) if you don’t have any Column Header.
This formula also creates a dynamic named range.
Enter a new name Rilee Royes in the data set, and you will find it added automatically to the range.
3. Use INDIRECT Function to Create Two Dimensional Dynamic Named Range in Excel
The two methods described above create one-dimensional dynamic ranges.
To create a two-dimensional dynamic range, use this method instead.
The steps are almost similar to the steps mentioned in method 1.
Just in Step 2, in place of the OFFSET formula, use this formula:
=Sheet1!B4:INDIRECT("C"&COUNTA(Sheet1!C:C)-2+ROW(Sheet1!C4))Notes:
- Here Sheet1 is my worksheet name.
- B4 is the first cell of my data set.
- And C:C is the last column of my data set. And C4 is the first cell of my last column.
This formula creates a dynamic two-dimensional named range called Students. It contains the whole data set.
And this is dynamic too.
Enter a new name Rilee Royes in the data set, and you will find it added automatically to the range.
Conclusion
Using these methods, you can create a dynamic named range in Excel. Do you know any other method? Or do you have any questions? Feel free to ask us.
