Dynamic Excel Named List Grows Automatically

You can quickly create a named range in Excel, but it doesn’t automatically expand to include new items that are added at the end of the list.
If you plan to add new items to a list, create a dynamic named range, by using an OFFSET formula. In this example, the formula entered in the Refers to box is:

  • Using cell B1 as the reference cell, the list will start 1 row down, and 0 columns to the right.
  • It counts all the items entered in column B, and subtracts 1, because the list won’t include the heading in B1.
  • The list will be 2 columns wide.

Then, as new items are added to the list, the dynamic named range will automatically expand to include them.

Watch the Video

To see the steps for creating a quick named range and a dynamic named ranged, you can watch this video tutorial.