How to Create a Dynamic Named Range in Excel

This is an overview.

The sample dataset showcases students’ Names and their Marks in English.

Steps:

=OFFSET(Sheet1!$B$5,,,COUNTA(Sheet1!$B$5:$B$100))

Formula Breakdown

COUNTA(Sheet1!$B$5:$B$100)) → counts non-empty cells 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”}

=Students

Excel will show the names in the range.

Note

Steps:

=Sheet3!$B$5:INDIRECT("C"&COUNTA(Sheet3!$C:$C)-2+ROW(Sheet3!$C$5))

=Multidimensional_List

Excel will show the names in the range.

Formula Breakdown

ROW(Sheet3!$C$5) → returns the row number of C5.
- Output: {5}
COUNTA(Sheet3!$C:$C) → Counts non-empty cells in column C
- Output: 12
“C”&COUNTA(Sheet3!$C:$C)-2+ROW(Sheet3!$C$5) → The Ampersand (&) joins the texts.
- Output: {“C15”}
Sheet3!$B$5:INDIRECT(“C”&COUNTA(Sheet3!$C:$C)-2+ROW(Sheet3!$C$5)) → shows 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}