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.
namedynamic01
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:
=OFFSET(Prices!$B$1,1,0,COUNTA(Prices!$B:$B)-1,2)

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

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

_______________