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