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:

Recommended articles

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 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:

Recommended articles

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. […]

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

Recommended articles

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. […]

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:

Recommended articles

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 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:

Recommended articles

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 […]