Author: Oscar Cronquist Article last updated on January 05, 2019

This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list is dynamic and it adds new values as you type them. The picture below shows a list containing some random values.

First we need to build a cell reference that expands when we add values to the list, named ranges allow us to do that.

However, if you own Excel 2007 or a later version I highly recommend using an Excel defined table instead.

It has a built-in dynamic cell reference system, named structured referencing.

Create a dynamic named range "List"

The following instructions tells you how to build an automatically expanding cell range.

  1. Press with left mouse button on "Formulas" tab
  2. Press with left mouse button on "Name Manager"
  3. Press with left mouse button on "New..."
  4. Type a name. I named it "List". (See attached file at the end of this post)
  5. Type
    =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A$1:$A$1001))

    in  "Refers to:" field.

  6. Press with left mouse button on "Close" button

Want to learn more about named ranges? Read this:

Create a dynamic named range

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

Create a dynamic named range

Create a dynamic list sorted from A to Z

  1. Select Sheet2
  2. Select cell A1
  3. Type
    =IF(COUNTA(List)>=ROWS($A$2:A2), INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(A1)), COUNTIF(List, "<"&List), 0)), "") + CTRL + SHIFT + ENTER
  4. Copy cell A2 and paste it down as far as needed.

This article explains how to extract a unique distinct list sorted alphabetically:

Extract a unique distinct list sorted from A to Z

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

Extract a unique distinct list sorted from A to Z

You can even use a condition if you prefer, read more here:

Unique distinct list sorted alphabetically based on a condition

This article demonstrates how to extract unique distinct values based on a condition and also sorted from A to z. […]

Unique distinct list sorted alphabetically based on a condition

Create a dynamic named range "SortedValues"

  1. Press with left mouse button on "Formulas" tab
  2. Press with left mouse button on "Name Manager"
  3. Press with left mouse button on "New..."
  4. Type a name. I named it "SortedValues". (See attached file at the end of this post)
  5. Type
    =OFFSET(Sheet2!$A$1, 0, 0, COUNTA(Sheet2!$A$1:$A$1001))

    in  "Refers to:" field.

  6. Press with left mouse button on "Close" button

Learn more about the function OFFSET function:

How to use the OFFSET function

The OFFSET function returns a reference to a range that is a given number of rows and columns from a […]

How to use the OFFSET function

How to create a drop down list containing dynamic values

  1. Press with left mouse button on Data tab
  2. Press with left mouse button on Data validation button
  3. Press with left mouse button on "Data validation..."
  4. Select List in the "Allow:" window. See picture below.
  5. Type
    =SortedValues

    in the "Source:" window

  6. Press with left mouse button on OK!

If you are using an excel defined table, read this:

How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas

This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas