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

Get FREE Advanced Excel Exercises with Solutions!

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.

This is an overview of this article.

excel dynamic named range


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.

Dataset

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.

Define Name Range

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

Set Name excel dynamic named range

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

Apply Formula

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

Output excel dynamic named range

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

Output

Note

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

Read More: How to Display Named Range Contents in Excel 


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.

Apply Formula

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

excel dynamic named range

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

Final Output

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.

Read More: Excel INDIRECT Function with Named Range 


Download Practice Workbook


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


<< Go Back to Named Range | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo