Dynamic Named in Range Excel (Both One and Two Dimensional)

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.

Data Set to Create Excel Dynamic Named Range

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.

Define Name Tool in Excel

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)

Define Name Dialogue Box in Excel

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:

=Students

The names of all the students will be displayed properly.

One-Dimensional Dynamic Named Range in Excel

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.

Adding New Value to Dynamic Named Range in Excel


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.

Dynamic Named Range in Excel with FILTER Function

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.

Adding New Value to Dynamic Named Range in Excel


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.

Dynamic Named Range in Excel with INDIRET Function

This formula creates a dynamic two-dimensional named range called Students. It contains the whole data set.

Two Dimensional Excel Dynamic Named Range

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.

Adding New Value to Dynamic Named Range in Excel


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.


Related Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo