# How to Create Dynamic Named Range in Excel (2 Easy Ways)

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.

## Create Dynamic Named Range in Excel: 2 Methods

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 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.

Steps:

• First of all, go to the FormulasÂ tab.
• After that, select Name Manager.

• A Name Manager box will appear.
• Then, select New.

• After that, the New Name box will appear.
• Then, set a Name.
• After that, write down the following formula in Refers to
`=OFFSET(Sheet1!\$B\$5,,,COUNTA(Sheet1!\$B\$5:\$B\$100))`
• Then, click OK.

Formula Breakdown

• COUNTA(Sheet1!\$B\$5:\$B\$100)) â†’ This will count the cells that are not empty in B5:B100.
• Output: 11
• OFFSET(Sheet1!\$B\$5;;;COUNTA(Sheet1!\$B\$5:\$B\$100)) â†’ Returns the range of a cell.
• Output: {“Alfred Moyes”;”Angela Hopkins”;”Frank Orwell”;”Jennifer Marlo”;”Marcus North”;”Natalia Austin”;”Nathan Mills”;”Peter Simpson”;”Steve Smith”;”Shane Hayes”;”Rilee Royes”}
• After that, select E5:E15.
• Then, write down the following formula in the formula bar.
`=Students`

• After that, press CTRL + SHIFT + ENTER. Excel will show the names in the range.

Note

Since this is an array formula, you must press CTRL + SHIFT + ENTER instead of ENTER.

### 2. Use INDIRECT Function to Create Two Dimensional Dynamic Named Range in Excel

Now, I will explain an method that will create a two dimensional dynamic range in Excel.

Steps:

• First of all, bring up the New Name box just like you did in method-1.
• Then, set a name.
• After that, write down the following formula
`=Sheet3!\$B\$5:INDIRECT("C"&COUNTA(Sheet3!\$C:\$C)-2+ROW(Sheet3!\$C\$5))`
• After that, click OK.

• After that, select E5:E15.
• Then, write down the following formula in the formula bar.
`=Multidimensional_List`

• After that, press CTRL + SHIFT + ENTER. Excel will show the names in the range.

Formula Breakdown

• ROW(Sheet3!\$C\$5) â†’ It returns the row number of C5.
• Output: {5}
• COUNTA(Sheet3!\$C:\$C) â†’ Counts the non-empty cells in C column
• Output: 12
• “C”&COUNTA(Sheet3!\$C:\$C)-2+ROW(Sheet3!\$C\$5) â†’ The Ampersand (&) will join the texts.
• Output: {“C15”}
• Sheet3!\$B\$5:INDIRECT(“C”&COUNTA(Sheet3!\$C:\$C)-2+ROW(Sheet3!\$C\$5)) â†’ This will show the final output.
• Output: {“Alfred Moyes”\28;”Angela Hopkins”\55;”Frank Orwell”\76;”Jennifer Marlo”\79;”Marcus North”\20;”Natalia Austin”\75;”Nathan Mills”\67;”Peter Simpson”\87;”Steve Smith”\31;”Shane Hayes”\84;”Rilee Royes”\59}
Note
Sheet3 is the name of the worksheet I was working on.

## 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.

<< Go Back toÂ Named Range

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF