Create a Dynamic Name Range Table in Excel

We will cover how to create a dynamic name range table in Excel. If you have ever created a list in Excel, it is frustrating if that list is not dynamic.

Overview

To begin, let’s define what it means for a list to be dynamic. A dynamic range automatically incorporates any new additions or deletions from a set list. Say you have a list of 10 students in a class. This class list has a non-dynamic named range “Classlist” from cells A1-A10. Let’s just assume a new student is added to the class. The named range “Classlist” is no longer accurate. It is still only looking at cells A1-10. However, the dynamic name list will automatically account for the new students.

Non-dynamic range
Non-dynamic named range example

Creating a Dynamic Name Range

So how do we create a dynamic range?

In order to create a dynamic name range, we must create a data table.


Create a Table

  1. Create a list in Excel with a Header. Our header is “Students”
  2. On the Ribbon’s Insert tab, click Table
  3. The range should default, but check to make sure it is correct. Do NOT include the header title.
  4. Ensure “My Table Has Headers” is checked
  5. Click OK, to create the table.
Create a Table

Setting up and using a Dynamic Name Range in Excel

Creating and using a Dynamic Name Range in Excel

For a refresher name ranges in Excel, refer back to Named Ranges in Excel – Setup, Rules, and Uses.

Now that we have the table setup, we must name the individual table columns. This is easily done as shown in the video above. Once these columns are named, we are able to use the names in our formulas.

=sum(Ages)
=average(Weight)

Leave a Reply

CUMPRINC Function

The CUMPRINC Function in Excel returns the cumulative principal paid on a loan between the start and end period. This

Read More »

MAX Function

The Excel MAX function returns the largest numeric value in a range of values. The MAX function ignores empty cells,

Read More »
Scroll to Top